我有2个具有多个字段的表。
表格1:
+--------+---+---------------+
| month | id| VERDICT_id |
+--------+------+------------+
| 201307 | 1 | 1 |
| 201307 | 2 | 4 |
| 201307 | 3 | 2 |
| 201307 | 4 | 2 |
| 201307 | 5 | NULL |
| 201307 | 6 | 1 |
+--------+------+------------+
像这样,对于每个新的“月”,对于每个唯一的“ id”,都会根据表2中的值设置“ VERDICT_ID”。
表2:
+----+----------------------------------+
| id | title |
+----+----------------------------------+
| 1 | Passed |
| 2 | Failed (Component Fault) |
| 3 | Failed (User Fault) |
| 4 | Failed (Hardware Issue) |
+----+----------------------------------+
我进行查询,给出以下输出
实际输出:
+--------+------------+----------+
| month | VERDICT_id | COUNT(*) |
+--------+------------+----------+
| 201307 | 1 | 2 |
| 201307 | 2 | 2 |
| 201307 | 4 | 1 |
+--------+------------+----------+
我想要的是
+--------+------------+----------+
| month | VERDICT_id | COUNT(*) |
+--------+------------+----------+
| 201307 | 1 | 2 |
| 201307 | 2 | 2 |
| 201307 | 3 | 0 |
| 201307 | 4 | 1 |
+--------+------------+----------+
这两个输出之间的区别是,如果一个月不存在任何VERDICT_id,那么我想将VERDICT_id和COUNT打印为“ 0”。
但是用下面的查询是不可能的。
select month,VERDICT_id, COUNT(*) FROM Table1
where (month = 201307) AND (VERDICT_id BETWEEN 1 AND 4) GROUP BY month, VERDICT_id;
问:是否可以进行查询以将不存在的VERDICT_id和COUNT打印为“ 0”?
尝试这个
SELECT v.*, count(t.verdict_id) as cnt FROM
(
SELECT month, id as verdict_id
from
(SELECT DISTINCT month FROM Table1 where (month = 201307)) M ,
verdictTable
) v
LEFT OUTER JOIN Table1 t ON v.verdict_id = t.verdict_id and v.month = t.month
GROUP BY v.verdict_id, v.month
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句