MySQL按日期分组，如果不存在，则强制返回空值

土库尔

我建立了一个查询，以让我返回某些应用程序的平均排名。

但是对于其中一些人来说，某个月来我们没有数据，因为该应用程序是新应用程序（例如，该应用程序自本月以来就已退出，因此我们自本月以来一直在收集数据）

SELECT 
    DATE_FORMAT(date, '%Y-%m'),
    app_id,
    AVG(rank)
FROM wadstats.applestore_ranking
where app_id IN (100,
        2,
        3,
        4,
        5,
        6)
GROUP BY MONTH(date), app_id
ORDER BY CASE WHEN app_id = 100 THEN 1 ELSE 2 END, date ASC

我需要先显示app_id = 100

但是对于app_id = 8，我没有8月份的数据。

然后结果看起来像

'2015-07', '100', '3.9355'
'2015-04', '100', '49.5000'
'2015-08', '100', '5.2258'
'2015-05', '100', '16.3333'
'2015-09', '100', '6.1333'
'2015-06', '100', '7.5667'
'2015-10', '100', '5.7727'
'2015-04', '2', '6.0000'
'2015-08', '2', '9.8710'
'2015-05', '2', '6.4667'
'2015-09', '2', '8.9667'
'2015-06', '2', '8.5333'
'2015-10', '2', '9.9545'
'2015-07', '2', '10.5806'
'2015-05', '3', '56.3929'
'2015-09', '3', '55.1667'
'2015-06', '3', '35.2500'
'2015-07', '3', '38.7143'
'2015-04', '3', '38.7500'
'2015-08', '3', '52.5500'
'2015-09', '4', '30.2105'
'2015-06', '4', '27.9231'
'2015-10', '4', '30.0000'
'2015-07', '4', '47.0000'
'2015-08', '4', '32.6818'
'2015-06', '5', '46.8667'
'2015-10', '5', '86.6667'
'2015-07', '5', '63.5185'
'2015-04', '5', '24.2500'
'2015-08', '5', '67.3571'
'2015-10', '6', '30.1818'

我希望每个月都为空，即使该特定月份没有可用数据

预期成绩

'2015-07', '100', '3.9355'
'2015-04', '100', '49.5000'
'2015-08', '100', '5.2258'
'2015-05', '100', '16.3333'
'2015-09', '100', '6.1333'
'2015-06', '100', '7.5667'
'2015-10', '100', '5.7727'
'2015-04', '2', '6.0000'
'2015-08', '2', '9.8710'
'2015-05', '2', '6.4667'
'2015-09', '2', '8.9667'
'2015-06', '2', '8.5333'
'2015-10', '2', '9.9545'
'2015-07', '2', '10.5806'
'2015-05', '3', '56.3929'
'2015-09', '3', '55.1667'
'2015-06', '3', '35.2500'
'2015-07', '3', '38.7143'
'2015-04', '3', '38.7500'
'2015-08', '3', '52.5500'
'2015-09', '4', '30.2105'
'2015-06', '4', '27.9231'
'2015-05', '4', NULL
'2015-10', '4', '30.0000'
'2015-07', '4', '47.0000'
'2015-08', '4', '32.6818'
'2015-06', '5', '46.8667'
'2015-10', '5', '86.6667'
'2015-07', '5', '63.5185'
'2015-04', '5', '24.2500'
'2015-08', '5', '67.3571'
'2015-04', '6', NULL
'2015-05', '6', NULL
'2015-06', '6', NULL
'2015-07', '6', NULL
'2015-08', '6', NULL
'2015-09', '6', NULL
'2015-10', '6', '30.1818'

如果我需要使用0而不是NULL，那也可以，但是我需要在数据库中的每个月都为每个app_id赋值

非常感谢

土库尔

多亏了德鲁，

这是在单个查询中执行的一种方式，详细说明了Drew。

非常感谢您使用CROSS JOIN的技巧

   SELECT 
    inr.date AS 'month',
    inr.app_id,
    r.title,
    AVG(r.rank) as 'ranking'    
FROM 
    (
        SELECT
            DISTINCT DATE_FORMAT(mh.date, '%Y-%m') date,
            r.app_id
        FROM 
            applestore_ranking mh
        CROSS JOIN
            applestore_ranking r
        WHERE 
            mh.app_id = 100
        AND
            r.app_id IN (100, 1, 2, 3, 4, 5)
    ) inr
LEFT JOIN
    applestore_ranking r ON r.app_id = inr.app_id
AND inr.date = DATE_FORMAT(r.date, '%Y-%m') 
GROUP BY
    inr.date,
    inr.app_id
ORDER BY
    CASE WHEN inr.app_id = 100 THEN 1 ELSE 2 END

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。