现有下面一段查询代码,怎样在最后一行 求一下各个 _cnt 的合计 只要求最后一行有一个总的合计
拜托各位高手帮忙 非常急 感激不尽SELECT dept_nm, minor_nm ,
sum(dangri_cnt) as dangri_cnt,
sum(xuesheng_cnt) as xuesheng_cnt,
sum(chuqin_cnt) as chuqin_cnt
FROM
(
select dept_nm,minor_nm,dangri_cnt,0 as xuesheng_cnt,0 as chuqin_cnt from #A
union all
select dept_nm,minor_nm,0 as dangri_cnt,xuesheng_cnt,0 as chuqin_cnt from #B
union all
select dept_nm,minor_nm,0 as dangri_cnt,0 as xuesheng_cnt,chuqin_cnt from #C
) T
GROUP BY dept_nm, minor_nm
拜托各位高手帮忙 非常急 感激不尽SELECT dept_nm, minor_nm ,
sum(dangri_cnt) as dangri_cnt,
sum(xuesheng_cnt) as xuesheng_cnt,
sum(chuqin_cnt) as chuqin_cnt
FROM
(
select dept_nm,minor_nm,dangri_cnt,0 as xuesheng_cnt,0 as chuqin_cnt from #A
union all
select dept_nm,minor_nm,0 as dangri_cnt,xuesheng_cnt,0 as chuqin_cnt from #B
union all
select dept_nm,minor_nm,0 as dangri_cnt,0 as xuesheng_cnt,chuqin_cnt from #C
) T
GROUP BY dept_nm, minor_nm
;with T
as
(SELECT dept_nm, minor_nm ,
sum(dangri_cnt) as dangri_cnt,
sum(xuesheng_cnt) as xuesheng_cnt,
sum(chuqin_cnt) as chuqin_cnt
FROM
(
select dept_nm,minor_nm,dangri_cnt,0 as xuesheng_cnt,0 as chuqin_cnt from #A
union all
select dept_nm,minor_nm,0 as dangri_cnt,xuesheng_cnt,0 as chuqin_cnt from #B
union all
select dept_nm,minor_nm,0 as dangri_cnt,0 as xuesheng_cnt,chuqin_cnt from #C
) T
GROUP BY dept_nm, minor_nm
)
select *ftom T union all
select dept_nm, minor_nm ,
sum( dangri_cnt) as dangri_cnt,
sum(xuesheng_cnt) xuesheng_cnt,
sum(chuqin_cnt)as chuqin_cntfrom T group by dept_nm, minor_nm
order by dept_nm, minor_nm
貌似一条语句很难出来的。
sum(dangri_cnt) as dangri_cnt,
sum(xuesheng_cnt) as xuesheng_cnt,
sum(chuqin_cnt) as chuqin_cnt
FROM
(
select dept_nm,minor_nm,dangri_cnt,0 as xuesheng_cnt,0 as chuqin_cnt from #A
union all
select dept_nm,minor_nm,0 as dangri_cnt,xuesheng_cnt,0 as chuqin_cnt from #B
union all
select dept_nm,minor_nm,0 as dangri_cnt,0 as xuesheng_cnt,chuqin_cnt from #C
) T
GROUP BY dept_nm, minor_nm
WITH ROLLUP
试一下吧 没自己看题目了
union
select .....
就好了、