c# - Sql - 合并两个查询以使其成为行

Question

我想从此表(tb_data)中获取疾病总数和上周总数:

P_ID   Disease                Date
1      A                      2016-10-11
2      A                      2016-10-11
3      A                      2016-10-14
4      A                      2016-10-19

虽然我有这样的查询:

SELECT Disease AS DT,
COUNT(P_ID) AS PT
FROM tb_data
GROUP BY Disease
union
SELECT Disease AS DLW,
COUNT(P_ID) AS PLW
FROM tb_data
WHERE Date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND Date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
GROUP BY Disease
ORDER BY 2 DESC, 1; 

我想要的结果是这样的

DT  PT DLW PLW
A   4  A   3

不是这个

DT  PT
A   4
A   3

因为我希望我的程序读取以下内容:

foreach (DataRow kolom in table.Rows)
                {
                    Total = kolom["PT"].ToString();
                    Lastweek = kolom["PLW"].ToString();
                }
labelTotal.Text = Total;
labelLastWeek.Text = Lastweek; 

提前致谢

Answer 1

我认为条件聚合正是您所需要的。

select disease as dt,
         sum(1) as pt,
         disease as dlw,
         sum(case when date between curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY and curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY then 1 else 0 end) as plw
from    tb_data
group   by disease;

结果

+------+------+------+------+
| dt   | pt   | dlw  | plw  |
+------+------+------+------+
| A    |    4 | A    |    3 |
+------+------+------+------+