mysql - 即使mysql表中没有记录也返回默认数据

Question

使用 MySql:

查询结果将在 webclient 用于图表

表:tblActivity

+----------------------------------------------------------+
|id |Activityname |state [state is boolean will have 1 or 0] 
+----------------------------------------------------------+
| 1 | activity1   |  1                                     |
| 1 | activity1   |  0                                     |
| 2 | activity2   |  1                                     |        
| 2 | activity2   |  0                                     | 
| 3 | activity3   |  0                                     | 
| 1 | activity1   |  1                                     |
| 2 | activity2   |  0                                     |  
| 4 | activity4   |  1                                     |
+----------------------------------------------------------+

选择查询

 select id, Activityname, state, count(*) as activitycount 
 from tblActivity
 group by id,state

当前输出

 +--------------------------------------+
 |id |Activityname |state |activitycount|
 +--------------------------------------+
 |1  |activity1    | 1    |  2          |
 |1  |activity1    | 0    |  1          | 
 |2  |activity2    | 1    |  1          | 
 |3  |activity2    | 0    |  2          |
 |3  |activity3    | 0    |  1          |
 +--------------------------------------+

预期输出

 +--------------------------------------+
 |id |Activityname |state |activitycount|
 +--------------------------------------+
 |1  |activity1    | 1    |  2          |
 |1  |activity1    | 0    |  1          |
 |2  |activity2    | 1    |  1          | 
 |2  |activity2    | 0    |  2          |
 |3  |activity3    | 1    |  0 [need]   |
 |3  |activity3    | 0    |  1          |
 |4  |activity3    | 1    |  1          |
 |4  |activity3    | 0    |  0 [need]   |
 +--------------------------------------+

因此，即使特定事件

的属性 state 没有数据，我也想返回一行

我尝试了 IFNULL、COLEASE 等，但找不到最佳解决方案。

Answer 1

您需要通过选择所有可用的状态/ID/事件来“创建”一个派生表，并将其左连接到您的查询中:

SELECT t2.id,t1.state,t2.activityname,count(s.activityname) as activitycount
FROM (SELECT distinct t.state FROM tblActivity t) t1
CROSS JOIN (select distinct s.id,s.activityname FROM tblActivity s) t2
LEFT OUTER JOIN tblActivity s
 ON(s.state = t1.state and s.activityname = t2.activityname)
GROUP BY t2.id,t1.state,t2.activityname