本文共 4092 字，大约阅读时间需要 13 分钟。

这两天写了一下多表查询的sql语句，然后把14年做天猫推荐竞赛写的sql翻出来看看，还是很有借鉴作用的。

一、sum/case when...then...else...end

表结构如下：

方法一（用count）：

SELECT user_id , brand_id , count(type=0) as type0 ,count(type=1) as type1 , count(type=2) as type2 ,count(type=3) as type3 FROM t_alibaba_data twhere visit_datetime >= 415 && visit_datetime <= 715group by user_id,brand_id;

SELECT user_id , brand_id ,sum(case when type=0 then 1 else 0 end) as type0,sum(case when type=1 then 1 else 0 end) as type1,sum(case when type=2 then 1 else 0 end) as type2,sum(case when type=3 then 1 else 0 end) as type3FROM t_alibaba_data tgroup by user_id , brand_id ;

下面是一段比较复杂的逻辑，大概描述的就是将type=0的记录按照时间进行打分得到和grade，然后按照其降序排列，且输出限制1000：

select user_id, brand_id,(type0+type1+type2+type3) as grade from (SELECT user_id , brand_id ,sum(case        when type=0 && visit_datetime >= 801 && visit_datetime<=815 then 10        when type=0 && visit_datetime >= 716 && visit_datetime<=731 then 8        when type=0 && visit_datetime >= 701 && visit_datetime<=715 then 6        when type=0 && visit_datetime >= 616 && visit_datetime<=630 then 4        when type=0 && visit_datetime >= 601 && visit_datetime<=615 then 2        when type=0 && visit_datetime >= 516 && visit_datetime<=531 then 1    else 0    end) as type0,sum(case when type=1 then 1 else 0 end) as type1,sum(case when type=2 then 2 else 0 end) as type2,sum(case when type=3 then 3 else 0 end) as type3FROM t_alibaba_data tgroup by user_id , brand_idorder by type0 desc)aorder by grade desc limit 1000;

2.1 join..on

select a.user_id,a.brand_id from(SELECT * FROM first_15)ajoin(select * from first_20)bon a.user_id = b.user_id and a.brand_id = b.brand_id and a.type = b.type and a.visit_datetime = b.visit_datetimegroup by user_id,brand_id;

SELECT a.user_id , a.brand_id FROM(select user_id , brand_id from user_behavior_all)ajoin(select user_id , brand_id , type from t_alibaba_data)bwhere a.user_id = b.user_id and a.brand_id = b.brand_id and b.type = 1group by user_id , brand_id;

以左表为基准，右表只会留下左表有的，除去右表没有的，两个表结合join但不是合并merge。

select a.user_id , a.brand_id , b.flagfrom first_20 a left join first_15_flag bon a.user_id = b.user_id and a.brand_id = b.brand_idgroup by user_id , brand_id;

select tmp.a, tmp.b , case when t3.a is null then 0 else 1 end as flag from(  SELECT t1.a,t1.b FROM  (    select * from T1  )t1  left join  (    select * from T2  )t2  on t1.a = t2.a and t1.b = t2.b)tmpleft join(  select * from T3)t3on tmp.a = t3.a and tmp.b = t3.b ;

select * from (        select a.client_id from         (select client_id, role_id from qcs_client_role)a        JOIN        (select role_id from qcs_configure where entry='shdx7' and storage_unit='sh_mams_cold')b        where a.role_id = b.role_id)cJOIN(select * from qcs_client)dwhere c.client_id = d.id;

SELECT count(distinct c1,c2) FROM one inner join two using(c1,c2)；

drop table if exists objects_to_delete;create table objects_to_delete AS(	SELECT * FROM `objects`	where name like "%user/hive/warehouse%");alter table objects_to_deleteadd finished int default 0;

select * from table_namewhere create_time >= DATE_ADD(NOW(),INTERVAL -3 MONTH) ;

select * from table_namewhere create_time >= DATE_ADD(NOW(),INTERVAL -3 MONTH) ;

delete from studentwhere exists(	select a.* from 	(		select * from student	)a 	join	(		select name from grade		where name="Jack"	)b	on a.name = b.name);

Author：忆之独秀

Email：leaguenew@qq.com

注明出处： 

转载地址：http://hpnpf.baihongyu.com/