22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT DC209,A.*
FROM (
SELECT
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DC201 END DC201,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DC202 END DC202,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DD203 END DD203,
CASE
WHEN GROUPING(DC201)=1 THEN '总计:'
WHEN GROUPING(DC202)=1 THEN '合计:'
WHEN GROUPING(DD203)=1 THEN '合计:'
WHEN GROUPING(DC211)=1 THEN '小计:'
WHEN GROUPING(OH001)=1 THEN '小计:'
WHEN GROUPING(OH002)=1 THEN '小计:'
ELSE DC211 END DC211,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE OH001 END OH001,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE OH002 END OH002,
SUM(OH008) OH008,
SUM(OH035 + OH036) OH035036,
SUM(OH204) OH204,
SUM((OH035+OH036)*(
CASE OH008
WHEN 0 THEN 0
ELSE OH042/OH008 END)*(
CASE OH042
WHEN 0 THEN 0
ELSE OH204/OH042 END)) OH0351,
SUM(OH008-OH042-OH204) OH008042204,
SUM(OH035+OH036-(OH035+OH036)*(
CASE OH008
WHEN 0 THEN 0
ELSE OH042/OH008 END)*(
CASE OH042
WHEN 0 THEN 0
ELSE OH204/OH042 END)) OH0352,
SUM(ISNULL(OA029,0)) OA029,
SUM(ISNULL(OA031,0)) OA031,
SUM(ISNULL(OA029,0)-ISNULL(OA031,0)) OA029031
FROM FGMDC
INNER JOIN FGMDD ON DC201=DD201 AND DC202=DD202
LEFT JOIN BVMOH ON OH001=DD205 AND OH002=DD206 AND OH003=DD207
LEFT JOIN BVMOG ON OH001=OG001 AND OH002=OG002
LEFT JOIN FGMOB ON OB005=OH001 AND OB006=OH002 AND OB007=OH003
LEFT JOIN FGMOA ON OB001=OA001 AND OB002=OA002
LEFT JOIN FGMOD ON OD006=OB001 AND OD007=OB002
LEFT JOIN FGMOC ON OD001=OC001 AND OD002=OC002
GROUP BY DC201,DC202,DD203,DC211,OH001,OH002 WITH ROLLUP
) A
LEFT JOIN FGMDC ON A.DC201=FGMDC.DC201 AND A.DC202=FGMDC.DC202
SELECT DC209,A.*
FROM (
SELECT grouping(dc201) as or1,grouping(dc202) as or2,grouping(dc203) as or3,
grouping(dc211) as or4,grouping(dc001) as or5,grouping(dc002) as or6,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DC201 END DC201,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DC202 END DC202,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DD203 END DD203,
CASE
WHEN GROUPING(DC201)=1 THEN '总计:'
WHEN GROUPING(DC202)=1 THEN '合计:'
WHEN GROUPING(DD203)=1 THEN '合计:'
WHEN GROUPING(DC211)=1 THEN '小计:'
WHEN GROUPING(OH001)=1 THEN '小计:'
WHEN GROUPING(OH002)=1 THEN '小计:'
ELSE DC211 END DC211,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE OH001 END OH001,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE OH002 END OH002,
SUM(OH008) OH008,
SUM(OH035+OH036) OH035036,
SUM(OH204) OH204,
SUM((OH035+OH036)*(
CASE OH008
WHEN 0 THEN 0
ELSE OH042/OH008 END)*(
CASE OH042
WHEN 0 THEN 0
ELSE OH204/OH042 END)) OH0351,
SUM(OH008-OH042-OH204) OH008042204,
SUM(OH035+OH036-(OH035+OH036)*(
CASE OH008
WHEN 0 THEN 0
ELSE OH042/OH008 END)*(
CASE OH042
WHEN 0 THEN 0
ELSE OH204/OH042 END)) OH0352,
SUM(ISNULL(OA029,0)) OA029,
SUM(ISNULL(OA031,0)) OA031,
SUM(ISNULL(OA029,0)-ISNULL(OA031,0)) OA029031
FROM FGMDC
INNER JOIN FGMDD ON DC201=DD201 AND DC202=DD202
LEFT JOIN BVMOH ON OH001=DD205 AND OH002=DD206 AND OH003=DD207
LEFT JOIN BVMOG ON OH001=OG001 AND OH002=OG002
LEFT JOIN FGMOB ON OB005=OH001 AND OB006=OH002 AND OB007=OH003
LEFT JOIN FGMOA ON OB001=OA001 AND OB002=OA002
LEFT JOIN FGMOD ON OD006=OB001 AND OD007=OB002
LEFT JOIN FGMOC ON OD001=OC001 AND OD002=OC002
GROUP BY DC201,DC202,DD203,DC211,OH001,OH002 WITH ROLLUP
) A
LEFT JOIN FGMDC
ON A.DC201=FGMDC.DC201 AND A.DC202=FGMDC.DC202
order by a.or1,a.or2,......