mysql - 如果没有找到结果设置默认值

Question

我有以下查询

SELECT count(*) as count, Month(created_at) as month 
FROM products 
WHERE marketplace_id=21
      and status='counterfeit' 
      and created_at < Now() 
      and created_at > DATE_ADD(Now(), INTERVAL - 5 MONTH)
group by month(created_at)

返回结果为

+-------+-------+
| count | month |
+-------+-------+
|   410 |     1 |
|   174 |     2 |
|   301 |     3 |
|   329 |     4 |
|   141 |    12 |
+-------+-------+

如果一个月没有值，它根本不会返回它，但我希望为该月设置默认值 0。

我试过了this link Return a default value if no rows found和

Returning a value if no result

我不确定是我不能正确实现还是这不是我想要的

Answer 1

试试这个，似乎有点笨，但可能对你有帮助；)

SELECT SUM(count) AS count, month
FROM (
    SELECT count(*) as count, Month(created_at) as month FROM products WHERE marketplace_id=21
            and status='counterfeit' and created_at < Now() and created_at > DATE_ADD(Now(), INTERVAL - 5 MONTH)
            group by month(created_at)
    UNION 
    SELECT * FROM (
        SELECT 0 AS count, 1 AS month
        UNION SELECT 0 AS count, 2 AS month
        UNION SELECT 0 AS count, 3 AS month
        UNION SELECT 0 AS count, 4 AS month
        UNION SELECT 0 AS count, 5 AS month
        UNION SELECT 0 AS count, 6 AS month
        UNION SELECT 0 AS count, 7 AS month
        UNION SELECT 0 AS count, 8 AS month
        UNION SELECT 0 AS count, 9 AS month
        UNION SELECT 0 AS count, 10 AS month
        UNION SELECT 0 AS count, 11 AS month
        UNION SELECT 0 AS count, 12 AS month) M
    WHERE M.month < Month(Now()) AND M.month > Month(DATE_ADD(Now(), INTERVAL - 5 MONTH)))
) tmp
GROUP BY mouth
ORDER BY month