SQL中对已经求和的列求和

Question

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，我该如何求它们的总和？

- user2583182

---

它是哪个关系型数据库管理系统？MySQL还是SQL SERVER还是RDBMS不可知？ - Adriaan Stander

Answer 1

如果这是 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

- gzaxx

---

这完全是错误的。在CTE中使用Order by？没有Group by的SUM？ - Adriaan Stander

是的，抱歉有点匆忙。 - gzaxx

你为什么要按照你正在聚合的内容进行分组？为什么不只是将底部部分更改为类似于 select t.*, t.WO + t.WP + t.HL + t.A 的内容？ 从 tablesum t 中排序 t.emp_no。 - Adriaan Stander

好的，看起来更好了。我给你点赞。 - Adriaan Stander

Answer 2

在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;

- jaczes