分组 左连接 合并 SQL

 

SELECT  temp.bz, sum(temp.bzsj) bzsj,sum(temp.llcl) llcl,max(temp.bzep) bzep,sum(temp.bzxscl)bzxscl  FROM (
select t.BZ bz, sum(t.bzsj) bzsj, sum(t.CL) llcl ,max(hye.sep) bzep ,sum(t.CL) / sum(t.bzsj) * 60 bzxscl 
from( SELECT SUM(SJZL) / 1000 CL, PH, GG, BZ, round(((SUM(SJZL) / 1000) / con.yield) * 60, 2) bzsj FROM[dbo].[B3A_JL] b2 
LEFT JOIN zgzx_HourYieldConifg con ON b2.ph = con.brand and b2.GG = con.spec WHERE BZ IS NOT NULL and cjsj >= '2023-11-01' and cjsj <= '2023-12-01' 
group  by PH, GG, BZ, con.yield ) t left join zgzx_hyeprecent hye on t.bz = hye.bz WHERE hye.proline = 'B2' group by t.BZ 
UNION
select t.BZ bz, sum(t.bzsj) bzsj, sum(t.CL) llcl ,max(hye.sep) bzep ,sum(t.CL) / sum(t.bzsj) * 60 bzxscl 
from( SELECT SUM(SJZL) / 1000 CL, PH, GG, BZ, round(((SUM(SJZL) / 1000) / con.yield) * 60, 2) bzsj FROM[dbo].[B3B_JL] b2 
LEFT JOIN zgzx_HourYieldConifg con ON b2.ph = con.brand and b2.GG = con.spec WHERE BZ IS NOT NULL and cjsj >= '2023-11-01' and cjsj <= '2023-12-01' 
group  by PH, GG, BZ, con.yield ) t left join zgzx_hyeprecent hye on t.bz = hye.bz WHERE hye.proline = 'B2' group by t.BZ) temp GROUP BY temp.bz

 写完真的很想报J 烦死了