Ronetver-Blog
0%

复杂SQL案例积累分析

发表于 阅读次数： Valine：
本文字数： 1.9k 阅读时长 ≈ 2 分钟

(非公司系统表)

#

1
select t1.id t1_id,t2.id t2_id,t1.file_id t1_file_id,t2.file_id t2_file_id,t2.file_name file_name from (select * from attachments where file_id=0) t1 left join    (select * from attachments where id in (                                                    select  max(id) max_id from attachments where file_id!=0 and contract_id in                                                                                                                                    (select contract_id from attachments where file_id=0) group by contract_id )) t2  on t1.contract_id =t2.contract_id ;
1
select concat('update attachments set file_id=',t2_file_id,'where id=',t1_id,';') from (select t1.id t1_id,t2.id t2_id,t1.file_id t1_file_id,t2.file_id t2_file_id,t2.file_name file_name from (select * from attachments where file_id=0) t1 left join                                                (select * from attachments where id in (                                                    select  max(id) max_id from attachments where file_id!=0 and contract_id in                                                                                                                                    (select contract_id from attachments where file_id=0) group by personnel_contract_id )) t2  on t1.contract_id =t2.contract_id ) as t3;

exist 使用

1
2
3
4
5
6
7
8
-- 查询不存在某表的数据exists
select bp.id,bp.code  ,status,date ,contract_id
from people bp
where date > '2022-05-26' and date < '2022-05-31'
and bp.is_delete = 0 and flag =1 and bp.type in (1,2,3) and not exists (select 1 from contract pc
where pc.people_id = bp.id and contract_type in (
1,4,7,10,12,13
) );

行转列

1
2
3
4
5
6
7
8
9
10
11

select customer_code '商户code',
case status when 1 then '在职'
when 2 then '待离职' when 3 then '已离职'
else '其他' end '在职状态',
count(*) '人数'
from people bp where type in (2,3)
and status in (1,2,3)
group by customer_code,status
order by customer_code;

image.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

select t.customer_code ,
max(case t.status when '在职' then t.num end) '在职人数',
max(case status when '待离职' then t.num end) '待离职人数',
max(case status when '已离职' then t.num end) '已离职人数'
from (
select customer_code,case status when 1 then '在职' when 2 then '待离职' when 3 then '已离职'
else '其他' end status,
count(*) num
from people bp where type in (2,3)
and status in (1,2,3)
group by customer_code,status order by customer_code
) t
group by t.customer_code order by t.customer_code
;

image.png