已在 SQL 中求和的列的总和
本文关键字:和的 SQL 求和 已在 | 更新日期: 2023-09-27 18:23:35
SELECT DISTINCT emp_no, sum(CASE WHEN isnull(PresAbs,0)='WO' THEN 1 ELSE 0 END) WO,
sum(CASE WHEN isnull(PresAbs,0)='WP' THEN 1 ELSE 0 END) WP,
sum(CASE WHEN isnull(PresAbs,0)='HL' THEN 1 ELSE 0 END) HL,
sum(CASE WHEN isnull(PresAbs,0)='A' THEN 1 ELSE 0 END) A,**sum(WO+WP+HL+A)**
from TRN_ATTN072013 WHERE CONVERT(varchar,Tdate,112)>'20130712' and CONVERT(varchar,Tdate,112)<'20130717'
group by emp_no
ORDER BY emp_no
我想总结一下这些列 WO,WP,HL,A 我如何对这些列求和
如果这是SQL server
那么您可以使用CTE
with tablesum as
(
SELECT DISTINCT emp_no, sum(CASE WHEN isnull(PresAbs,0)='WO' THEN 1 ELSE 0 END) WO,
sum(CASE WHEN isnull(PresAbs,0)='WP' THEN 1 ELSE 0 END) WP,
sum(CASE WHEN isnull(PresAbs,0)='HL' THEN 1 ELSE 0 END) HL,
sum(CASE WHEN isnull(PresAbs,0)='A' THEN 1 ELSE 0 END) A
from TRN_ATTN072013
WHERE CONVERT(varchar,Tdate,112) > '20130712' and CONVERT(varchar,Tdate,112) < '20130717'
group by emp_no
)
select t.*, t.WO + t.WP + t.HL + t.A
from tablesum t
order by t.emp_no
in mysql ;)
SELECT emp_no, wo+wp+hl+A FROM
(SELECT DISTINCT emp_no, sum(CASE WHEN isnull(PresAbs,0)='WO' THEN 1 ELSE 0 END) WO,
sum(CASE WHEN isnull(PresAbs,0)='WP' THEN 1 ELSE 0 END) WP,
sum(CASE WHEN isnull(PresAbs,0)='HL' THEN 1 ELSE 0 END) HL,
sum(CASE WHEN isnull(PresAbs,0)='A' THEN 1 ELSE 0 END) A,**sum(WO+WP+HL+A)**
from TRN_ATTN072013 WHERE CONVERT(varchar,Tdate,112)>'20130712' and CONVERT(varchar,Tdate,112)<'20130717'
group by emp_no
ORDER BY emp_no) AS SUB_Q;