同事代码

 

SELECT o.ORGNAME 'orgname',SUM(CASE WHEN z.ZRSLX='01' and z.fqdwid=O.ORGCODE THEN 1 ELSE 0 END) 'dwddwtotal',
SUM(CASE WHEN q.qdzt='1' AND z.ZRSLX='01' AND (z.fqdwid=O.ORGCODE OR Z.BQDDWBMID = O.ORGCODE) THEN 1 ELSE 0 END) 'dwddwyqd',
SUM(CASE WHEN z.ZRSLX='02' and z.fqdwid=O.ORGCODE THEN 1 ELSE 0 END) 'dwdbmtotal',
SUM(CASE WHEN q.qdzt='1'AND z.ZRSLX='02' and z.fqdwid=O.ORGCODE THEN 1 ELSE 0 END) 'dwdbmyqd',
SUM(CASE WHEN z.ZRSLX='03' and z.fqdwid=O.ORGCODE THEN 1 ELSE 0 END) 'bmdgrtotal',
SUM(CASE WHEN q.qdzt='1' AND z.ZRSLX='03' and z.fqdwid=O.ORGCODE THEN 1 ELSE 0 END) 'bmdgryqd',
SUM(CASE WHEN z.ZRSLX='04' and z.fqdwid=O.ORGCODE THEN 1 ELSE 0 END) 'lddldtotal',
SUM(CASE WHEN q.qdzt='1' AND z.ZRSLX='04' and z.fqdwid=O.ORGCODE THEN 1 ELSE 0 END) 'lddldyqd'
FROM org_organization o , c_tb_zrs z LEFT JOIN c_tb_zrs_qdrxx q ON z.id=q.zrsid and q.xt_zxbz='0'
WHERE o.ORGCode IN （‘’，'','',''）
AND o.XT_ZXBZ='0'
AND Z.XT_ZXBZ = '0'
GROUP BY o.ORGname
ORDER BY o.ORDERNO

执行顺序：  from （此时临时表中有orgname数乘以zrs数数量的数据） -----  where （筛选）

------   group by（此时每个orgname组中都有总的zrs数量的数据） ----- select（sum的时候是case函数，满足case条件的返回1，sum+1，不满足返回0 ）

 

SELECT o.ORGNAME 'orgname',SUM(1) 'dwddwtotal'
FROM org_organization o , c_tb_zrs z LEFT JOIN c_tb_zrs_qdrxx q ON z.id=q.zrsid and q.xt_zxbz='0'
WHERE o.ORGCode IN (105203017598, 105203011398, 105203011598, 10520302, 10520303, 10520304, 10520305, 10520306, 10520307,
10520308, 10520309, 10520310, 10520311, 10520312, 10520313, 10520314, 10520315, 10520301)
AND o.XT_ZXBZ='0'
AND Z.XT_ZXBZ = '0'
GROUP BY o.ORGname
ORDER BY o.ORDERNO ;

数据库中总共有133条数据

 

转载于:https://www.cnblogs.com/19960715lww/p/8521268.html