SQL 复合语句解析

我会带着你远行 2021-09-14 06:34 442阅读 0赞

## —————————————————————————————— ##

1、建表wepon\_merchant3  
2、从表wepon\_feature3选取数据  
3、将选取的数据复制到wepon\_merchant3中

create table wepon_merchant3 as select merchant_id,user_id,coupon_id,distance,date_received,date_pay from wepon_feature3;

## —————————————————————————————— ##

1、从表wepon\_merchant3中选取数据（注意在选取过程中新建一列cnt赋值为1），并将其命名为t  
2、对表t按merchant\_id分组后选取数据，并对分组后数据列cnt求和  
3、将选取的数据赋值给新建表wepon\_d3\_f1\_t1

create table wepon_d3_f1_t1 as select merchant_id,sum(cnt) as total_sales from ( select merchant_id,1 as cnt from wepon_merchant3 where date_pay!="null" )t group by merchant_id;

## —————————————————————————————— ##

1、将tb按姓名分组后选取数据，其中max(case 课程 when ‘数学’ then 分数 else 0 end) \[数学\]表示对某个姓名如”张三”组中的课程类别为数学列的求解分数最大值并将其作为新建列数学的值。  
2、注意max group by联合使用时姓名等其他信息为该组第一行数据，而数学列为该组最大值。可能会出现不匹配现象。见[max group by一起使用][max group by]

select 姓名, max(case 课程 when '数学' then 分数 else 0 end) [数学] , max(case 课程 when '物理' then 分数 else 0 end) [物理] , max(case 课程 when '语文' then 分数 else 0 end) [语文] from tb group by 姓名

## —————————————————————————————— ##

1、将\#student 分组后选取数据。  
2、isnull(sum( case stdsubject when ’ 化学 ’ then Result end), 0 ) \[化学\]与上同。

select stdname, isnull(sum( case stdsubject when ' 化学 ' then Result end), 0 ) [化学], isnull(sum( case stdsubject when ' 数学 ' then Result end), 0 ) [数学], isnull(sum( case stdsubject when ' 物理 ' then Result end), 0 ) [物理], isnull(sum( case stdsubject when ' 语文 ' then Result end), 0 ) [语文] from #student group by stdname

拼接版：@sql为申明的变量。select和set可以用于赋值。

declare @sql varchar( 4000 ) 
    set @sql = ' select stdname ' select @sql = @sql + ' ,isnull(sum(case stdsubject when ''' + stdsubject + ''' then Result end),0) [ ' + stdsubject + ' ] ' from (select distinct stdsubject from #student) as a select @sql = @sql + ' from #student group by stdname ' print @sql exec(@sql)

## —————————————————————————————— ##

合并多个表

create table wepon_d1_f1 as select merchant_id,cast(merchant_max_distance as bigint) as merchant_max_distance, from ( select tt.*,tt.sales_use_coupon/tt.total_coupon as transform_rate from ( select merchant_id,case when sales_use_coupon is null then 0.0 else sales_use_coupon end as sales_use_coupon from ( select k.*,l.merchant_user_buy_count from ( select i.*,j.merchant_avg_distance from ( select g.*,h.distinct_coupon_count from ( select e.*,f.total_coupon from ( select c.*,d.sales_use_coupon from ( select a.*,b.total_sales from (select distinct merchant_id from wepon_merchant1) a left outer join wepon_d1_f1_t1 b on a.merchant_id=b.merchant_id )c left outer join wepon_d1_f1_t2 d on c.merchant_id=d.merchant_id )e left outer join wepon_d1_f1_t3 f on e.merchant_id=f.merchant_id )g left outer join wepon_d1_f1_t4 h on g.merchant_id=h.merchant_id )i left outer join wepon_d1_f1_t5 j on i.merchant_id=j.merchant_id )k left outer join wepon_d1_f1_t6 l on k.merchant_id=l.merchant_id )t )tt )ttt;

[max group by]: http://blog.csdn.net/john_hongming/article/details/42742965