无敌SQL

1.查询文章内容连带点赞评论(多表)
SELECT s.id,s.title,s.img,s.context,DATE_FORMAT(s.create_time,'%Y-%m-%d %H:%i:%s') AS createTime,
IFNULL(p.pcount,0) AS pcount,IFNULL(c.ccount,0) AS ccount  FROM strategy s
LEFT JOIN (SELECT pp.content_id,COUNT(1) AS pcount FROM praise pp WHERE pp.type=0 GROUP BY pp.content_id) p ON s.id=p.content_id
LEFT JOIN (SELECT cc.comment_id,COUNT(1) AS ccount FROM commentary cc WHERE cc.type=0 GROUP BY cc.comment_id) c ON s.id=c.comment_id

2.查询收藏内容（包括每条数据的详细内容以及点赞评论，收藏数据）
SELECT c.id,st.create_time AS createTime,st.store_type as storeType,c.title,c.context,c.img,c.pcount,c.ccount,c.scount FROM store st 
Left join (SELECT s.id,s.title,s.context,s.img,s.p_type,p1.pcount,c1.ccount,sst1.scount FROM strategy s 
LEFT JOIN (SELECT pp.content_id,COUNT(1) AS pcount FROM praise pp WHERE pp.type=0 GROUP BY pp.content_id) p1 ON s.id=p1.content_id
LEFT JOIN (SELECT cc.comment_id,COUNT(1) AS ccount FROM commentary cc WHERE cc.type=0 GROUP BY cc.comment_id) c1 ON s.id=c1.comment_id
LEFT JOIN (SELECT ss.store_id,COUNT(1) AS scount FROM store ss WHERE ss.store_type=0 GROUP BY ss.store_id) sst1 ON s.id=sst1.store_id
UNION
SELECT e.id,e.title,e.context,e.img,e.p_type,p2.pcount,c2.ccount,sst2.scount FROM exclusive_news e 
LEFT JOIN (SELECT pp.content_id,COUNT(1) AS pcount FROM praise pp WHERE pp.type=1 GROUP BY pp.content_id) p2 ON e.id=p2.content_id
LEFT JOIN (SELECT cc.comment_id,COUNT(1) AS ccount FROM commentary cc WHERE cc.type=1 GROUP BY cc.comment_id) c2 ON e.id=c2.comment_id
LEFT JOIN (SELECT ss.store_id,COUNT(1) AS scount FROM store ss WHERE ss.store_type=1 GROUP BY ss.store_id) sst2 ON e.id=sst2.store_id
UNION
SELECT f.id,f.title,f.context,f.img,f.p_type,p3.pcount,c3.ccount,sst3.scount FROM fangjing f
LEFT JOIN (SELECT pp.content_id,COUNT(1) AS pcount FROM praise pp WHERE pp.type=2 GROUP BY pp.content_id) p3 ON f.id=p3.content_id
LEFT JOIN (SELECT cc.comment_id,COUNT(1) AS ccount FROM commentary cc WHERE cc.type=2 GROUP BY cc.comment_id) c3 ON f.id=c3.comment_id
LEFT JOIN (SELECT ss.store_id,COUNT(1) AS scount FROM store ss WHERE ss.store_type=2 GROUP BY ss.store_id) sst3 ON f.id=sst3.store_id
) c ON c.id=st.store_id AND c.p_type=st.store_type

3
SELECT s.id,s.title,s.context,DATE_FORMAT(s.create_time,'%Y-%m-%d %H:%i:%s') AS createTime,s.img,
v.dayVisitCount,v.countTime,v.sumVisitCount FROM strategy s
LEFT JOIN (SELECT visit_id,visit_count AS dayVisitCount,SUM(visit_count) AS sumVisitCount,
count_time AS countTime,visit_type FROM tb_visit
<if test="countTime != null and countTime != ''">
where count_time=#{countTime}
  </if>
GROUP BY visit_id)v 
ON v.visit_id=s.id AND v.visit_type=0 
<if test="countTime == null and countTime == ''">
AND TO_DAYS(NOW())-TO_DAYS(v.countTime) <![CDATA[<=]]> 1 
</if>
WHERE 1=1
  <if test="title != null and title != ''">
and s.title like '%${title}%'
  </if>
  <if test="context != null and context != ''">
and s.context like '${context}'
  </if>
  order by id desc

4.
SELECT s.id,s.title,s.context,DATE_FORMAT(s.create_time,'%Y-%m-%d %H:%i:%s') AS createTime,s.img,
v.dayVisitCount,v.countTime,v.sumVisitCount FROM strategy s
LEFT JOIN (SELECT visit_id,visit_count AS dayVisitCount,SUM(visit_count) AS sumVisitCount,
count_time AS countTime,visit_type FROM tb_visit GROUP BY visit_id)v 
ON v.visit_id=s.id AND v.visit_type=0 AND TO_DAYS(NOW())-TO_DAYS(v.countTime) <![CDATA[<=]]> 1 
WHERE 1=1
  <if test="title != null and title != ''">
and s.title like '%${title}%'
  </if>
  <if test="context != null and context != ''">
and s.context like '${context}'
  </if>
  order by id desc

5.
SELECT f.id,f.title,f.context,DATE_FORMAT(f.create_time,'%Y-%m-%d %H:%i:%s') AS createTime,f.img,
f.long_video AS longVideo,v.dayVisitCount,v.countTime,v.sumVisitCount FROM fangjing f
LEFT JOIN (SELECT visit_id,visit_count AS dayVisitCount,SUM(visit_count) AS sumVisitCount,
count_time AS countTime,visit_type FROM tb_visit GROUP BY visit_id)v 
ON v.visit_id=f.id AND v.visit_type=2 AND TO_DAYS(NOW())-TO_DAYS(v.countTime) <![CDATA[<=]]> 1 
WHERE 1=1
  <if test="title != null and title != ''">
and title like '%${title}%'
  </if>
  <if test="context != null and context != ''">
and context like '%${context}%'
  </if>
  order by id desc


SELECT e.id,e.title,e.context,DATE_FORMAT(e.create_time,'%Y-%m-%d %H:%i:%s') AS createTime,e.type,e.img,
e.video,v.dayVisitCount,v.countTime,v.sumVisitCount FROM exclusive_news e
LEFT JOIN (SELECT visit_id,visit_count AS dayVisitCount,SUM(visit_count) AS sumVisitCount,
count_time AS countTime,visit_type FROM tb_visit GROUP BY visit_id)v 
ON v.visit_id=e.id AND v.visit_type=1 AND TO_DAYS(NOW())-TO_DAYS(v.countTime) <![CDATA[<=]]> 1 
WHERE 1=1


6.
SELECT COUNT(1),c.cname FROM (SELECT * FROM employee_basic WHERE mobile='199%')eb LEFT JOIN company c ON c.cid=eb.cid
LEFT JOIN employee_info e ON e.eid=eb.eid GROUP BY eb.cid ORDER BY eb.cid DESC LIMIT 0,10


7.统计某个type中的各个类型的数量,（注意事项：查询结果只能是group by后面的字段）
SELECT TRAE.CHK_CUS_ID,
SUM(CASE WHEN TRA.CARGO_TYPE='普货' THEN 1 ELSE 0 END ) AS a,  
SUM(CASE WHEN TRA.CARGO_TYPE='一体化查验货物' THEN 1 ELSE 0 END ) AS b,  
SUM(CASE WHEN TRA.CARGO_TYPE='转关货' THEN 1 ELSE 0 END ) AS c,
SUM(CASE WHEN TRA.CARGO_TYPE='非贸货品' THEN 1 ELSE 0 END ) AS mm,
SUM(CASE WHEN TRA.CARGO_TYPE='葡萄酒类' THEN 1 ELSE 0 END ) AS mmm,
SUM(CASE WHEN TRA.CARGO_TYPE='危险品' THEN 1 ELSE 0 END ) AS mf,
SUM(CASE WHEN TRA.CARGO_TYPE='其它' THEN 1 ELSE 0 END ) AS mmmm  

 

FROM T_REG_APPLY_ENTRY trae,T_REG_APPLY tra where TRAE.PID=TRA."ID" GROUP BY TRAE.CHK_CUS_ID

 

 

8.各种复杂查询和统计

select tt1.vessel_code,tt1.vessel_name_en,tt1.out_voyage,tt1.cma_voyage,tt1.depart_date,tt1.stop_port,tt2.a20,tt2.a40,tt2.a45 from
(select vessel_code,vessel_name_en,out_voyage,cma_voyage,depart_date,stop_port from INFDR_SCHEDULE where (vessel_code,out_voyage,depart_date) in 
(select vessel_code,out_voyage,min(depart_date) from INFDR_SCHEDULE where vessel_type='F' and (vessel_code,out_voyage) in (select pre_vessel,pre_voyage from INFDR_BOOKING 
where bl_no in (select bl_no from INFDR_LOADING_CMA where real_vessel = 'CHONGLUNJI 3017' and real_voyage = '1801E')) group by vessel_code,out_voyage)) tt1
,
(select n3.vessel_code,n3.out_voyage,
sum(case when n3.ctn_size ='20' then 1 else 0 end) as a20,
sum(case when n3.ctn_size ='40' then 1 else 0 end) as a40,
sum(case when n3.ctn_size='45' then 1 else 0 end) as a45
from
(select m1.bl_no,m1.ctn_size,m2.vessel_code,m2.vessel_name_en,m2.out_voyage,m2.cma_voyage,m2.depart_date,m2.stop_port from INFDR_BOOKING_CONTAINER m1,
(select t1.bl_no,t2.vessel_code,t2.vessel_name_en,t2.out_voyage,t2.cma_voyage,t2.depart_date,t2.stop_port from INFDR_BOOKING t1,
(select vessel_code,vessel_name_en,out_voyage,cma_voyage,depart_date,stop_port from INFDR_SCHEDULE where (vessel_code,out_voyage,depart_date) in 
(select vessel_code,out_voyage,min(depart_date) from INFDR_SCHEDULE where vessel_type='F' and status = '1' and (omt is null or omt !='Y') and (vessel_code,out_voyage) 
in (select pre_vessel,pre_voyage from INFDR_BOOKING 
where bl_no in (select bl_no from INFDR_LOADING_CMA where real_vessel = 'CHONGLUNJI 3017' and real_voyage = '1801E')) group by vessel_code,out_voyage)) t2 
where t1.pre_vessel = t2.vessel_code and T1.PRE_VOYAGE = t2.out_voyage) m2 where M1.BL_NO = m2.bl_no) n3 group by n3.vessel_code,n3.out_voyage) tt2

where tt1.vessel_code = tt2.vessel_code and tt1.out_voyage = tt2.out_voyage;

 

9.比较查询结果列是否相同

select BOOK.BL_NO,BOOK.DISCHARGE_PORT,BOOK.PLACE_OF_DELIVERY,load.bl_no,LOAD.LOADING_SIZE,LOAD.CONTAINER_NO,LOAD.POD,LOAD.FPD,
(case when book.bl_no is not null and load.bl_no is not null and book.vessel = load.real_vessel and book.voyage = load.real_voyage then '' else 'MVS' end) as mvs_diff,
(case when book.bl_no is null then 'BKG' else '' end) as bkg_diff,
(case when load.bl_no is null then 'CNT' else '' end) as cnt_diff,
(case when book.DISCHARGE_PORT = load.pod then '' else 'POD' end) as pod_diff,
(case when book.place_of_delivery = load.fpd then '' else 'FPD' end) as fpd_diff
from INFDR_BOOKING book left join INFDR_LOADING_CMA load on BOOK.BL_NO = LOAD.BL_NO
union 
select BOOK2.BL_NO,BOOK2.DISCHARGE_PORT,BOOK2.PLACE_OF_DELIVERY,load2.bl_no,LOAD2.LOADING_SIZE,LOAD2.CONTAINER_NO,LOAD2.POD,LOAD2.FPD,
(case when book2.bl_no is not null and load2.bl_no is not null and book2.vessel = load2.real_vessel and book2.voyage = load2.real_voyage then '' else 'R' end) as mvs_diff,
(case when book2.bl_no is null then 'BKG' else '' end) as bkg_diff,
(case when load2.bl_no is null then 'CNT' else '' end) as cnt_diff,
(case when book2.DISCHARGE_PORT = load2.pod then '' else 'POD' end) as pod_diff,
(case when book2.place_of_delivery = load2.fpd then '' else 'FPD' end) as fpd_diff
from INFDR_BOOKING book2 right join INFDR_LOADING_CMA load2 on BOOK2.BL_NO = LOAD2.BL_NO

10 统计每个类型的其他数据整合（WM_CONCAT使用）

SELECT work_date,mat_prod_code,(SELECT to_char(WM_CONCAT(t1.dst_stock_code)) FROM WORK t1 WHERE t1.work_date=t2.work_date AND t1.mat_prod_code=t2.mat_prod_code) FROM WORK t2 GROUP BY t2.work_date,t2.mat_prod_code

 

11 删除语句中exist，select 1用法

DELETE FROM EBW2_SHIP_SCHEDULE e WHERE EXISTS 
(SELECT 1 FROM (SELECT vessel,voyage,carrier_code,MAX(ID) maxId FROM EBW2_SHIP_SCHEDULE e2 GROUP BY vessel,voyage,carrier_code HAVING COUNT(1) > 1) e3  WHERE e.vessel = e3.vessel AND e.voyage = e3.voyage AND e.carrier_code =  e3.carrier_code AND e.ID != e3.maxId；

 

12 根据字段去重查询最新记录

select 
*
from ( 
select 
t.*, 
row_number() OVER(PARTITION BY t.recv_code ORDER BY T.CREATE_TIME desc) as row_flg 
from EPB.EBW2_BOOKING t ) temp 
where temp.row_flg = 1