珠海公司制作网站,品牌营销策划过程,建筑人才招聘信息网,黑糖 wordpress目录
1.语法 2. 使用场景
select语句
chaining CTEs 链式
union语句
insert into 语句
create table as 语句
前言 Common Table Expressions#xff08;CTE#xff09;#xff1a;公共表达式是一个临时的结果集#xff0c;该结果集是从with子句中指定的查询派生而来…目录
1.语法 2. 使用场景
select语句
chaining CTEs 链式
union语句
insert into 语句
create table as 语句
前言 Common Table ExpressionsCTE公共表达式是一个临时的结果集该结果集是从with子句中指定的查询派生而来的紧跟在select 或 insert关键字之前。CTE可以在 selectinsert create table as select 等语句中使用。
1.语法
[wtih CommonTableExpression]
selectcolumn1,column2, ...
from table
[where 条件]
[group by column]
[order by column]
[cluster by column| [distribute by column] [sort by column]
[limit [offset,] rows]; 2. 使用场景 select语句
with tmp as (selectoid,uid,otime,date_format(otime, yyyy-MM) as dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, yyyy-MM) order by otime) rkfrom t_order
)selectuid,--每个用户一月份的订单数sum(if(dt 2018-01, 1, 0)) as m1_count,--每个用户二月份的订单数sum(if(dt 2018-02, 1, 0)) as m2_count
from tmpgroup by uidhaving m1_count 0 and m2_count0; chaining CTEs 链式 with tmp1 as (selectoid,uid,otime,date_format(otime, yyyy-MM) as dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, yyyy-MM) order by otime) as rkfrom t_order
),tmp2 as(selectuid,--每个用户一月份的订单数sum(if(dt 2018-01, 1, 0)) as m1_count,--每个用户二月份的订单数sum(if(dt 2018-02, 1, 0)) as m2_countfrom tmp1group by uidhaving m1_count 0and m2_count 0)
select * from tmp2 limit 1;union语句
with q1 as (select * from student where num 95002),q2 as (select * from student where num 95004)
select * from q1 union all select * from q2; insert into 语句
with tmp1 as (selectoid,uid,otime,date_format(otime, yyyy-MM) as dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, yyyy-MM) order by otime) as rkfrom t_order
),tmp2 as(selectuid,--每个用户一月份的订单数sum(if(dt 2018-01, 1, 0)) as m1_count,--每个用户二月份的订单数sum(if(dt 2018-02, 1, 0)) as m2_countfrom tmp1group by uidhaving m1_count 0and m2_count 0)insert into tmp3
select * from tmp2 limit 10; create table as 语句
--- 从tmp2 表中取10条数据基于此创建表tmp3
create table tmp3 as
with tmp1 as (selectoid,uid,otime,date_format(otime, yyyy-MM) as dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, yyyy-MM) order by otime) as rkfrom t_order
),tmp2 as(selectuid,--每个用户一月份的订单数sum(if(dt 2018-01, 1, 0)) as m1_count,--每个用户二月份的订单数sum(if(dt 2018-02, 1, 0)) as m2_countfrom tmp1group by uidhaving m1_count 0and m2_count 0)
select * from tmp2 limit 10;
