mysql - 如果没有结果，如何使查询返回 0 而不是空集

Question

如果每个日期都没有值，我如何让这个查询返回一个值为 0 的行

SELECT COUNT(id) FROM `panel_messages` WHERE `sent_by` = 'root' 
        AND `send_date` IN ("1395-4-25","1395-4-24","1395-4-23","1395-4-22","1395-4-21","1395-4-20","1395-4-19")
        GROUP BY `send_date`
        ORDER BY `send_date` DESC

我的预期结果是这样的 7 行:

| row1 |

| row2 |

| row3 |

| row4 |

| row5 |

| row6 |

| row7 |

如果其中一行没有结果，我希望它为 0，这是默认值:

| 2 |

| 0 |

| 0 |

| 2 |

| 0 |

| 3 |

| 1 |

但现在我只得到 4 行，因为如果没有结果，我的查询将不会返回任何内容:

| 2 |

| 2 |

| 3 |

| 1 |

SQL fiddle :http://sqlfiddle.com/#!9/a07486/3

Answer 1

请试一试:

SELECT 
 COALESCE(YT.total,t.total) AS cnt
FROM 
(SELECT 0 AS total) t
LEFT JOIN
(
    SELECT 
        COUNT(id) AS total 
    FROM `panel_messages` 
    WHERE `sent_by` = 'root' 
    AND `send_date` IN ("1395-4-25","1395-4-24","1395-4-23","1395-4-22","1395-4-21","1395-4-20","1395-4-19")
    GROUP BY `send_date`
    ORDER BY `send_date` DESC
) YT 
ON 1=1;

注意:

已创建值为 0 的虚拟行。

稍后在此虚拟表和您的查询之间执行LEFT JOIN

最后使用 COALESCE 如果您的主查询没有返回任何内容，您可以获得默认计数 0。

编辑:

查询:

SELECT 
COALESCE(YT.count,0) AS count
FROM
(
    SELECT ADDDATE('1395-01-01', INTERVAL @i:=@i+1 DAY) AS DAY
    FROM (
    SELECT a.a
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
) dateTable
LEFT JOIN 

(
    SELECT
        send_date,
        COUNT(id) AS count
    FROM
        `panel_messages`
    WHERE
        `sent_by` = 'root'
    AND `send_date` IN (
        "1395-4-25",
        "1395-4-24",
        "1395-4-23",
        "1395-4-20"
    )
    GROUP BY
        `send_date`
    ORDER BY
        `send_date` DESC
) AS YT 
ON dateTable.DAY = YT.send_date
WHERE dateTable.DAY IN ('1395-04-25','1395-04-24','1395-04-23','1395-04-20');

为了对不存在的日期进行零计数，您需要创建一个临时表，其中存放所有日期(在特定范围内)。

然后在这个临时表的日期字段和您的表的 send_date 字段之间进行左连接就差不多可以完成工作了。

如果 count 为 NULL，最后您需要使用 COALESCE 得到 0。

WORKING DEMO