MySQL 查询 INTERVAL 问题，如果没有提供数据则需要 0

发布于 2024-10-24 05:11:42 字数 433 浏览 1 评论 0原文

我有以下声明：

SELECT
count(rs.rsc_id) as counter
FROM shots as rs
where rsc_rs_id = 345354
AND YEAR(rs.timestamp) = YEAR(DATE_SUB(CURDATE(), INTERVAL 6 MONTH))
GROUP BY DATE_FORMAT(rs.timestamp,'%Y%m')

rs.timestamp 是一个 unix 时间戳

输出就像每行/月的数字，如“28”

它工作正常，但如果我的数据不一致，例如仅过去三个月的数据（不适用于所有数据）六个月），我没有从数据库中得到任何回报。我想每次没有这个月的数据时，都会返回 0...

有什么建议吗？我考虑了一些案例陈述，但这似乎不太好......

谢谢！

原文

i have the following statement:

SELECT
count(rs.rsc_id) as counter
FROM shots as rs
where rsc_rs_id = 345354
AND YEAR(rs.timestamp) = YEAR(DATE_SUB(CURDATE(), INTERVAL 6 MONTH))
GROUP BY DATE_FORMAT(rs.timestamp,'%Y%m')

rs.timestamp is a unix timestamp

Output would be like for each row / month a numeric like '28'

It Works fine, but if i have inconsistent data, like only for the past three month (not for all six month), i get no return from my Database. I would like to have every time there is not data for this month, 0 returned...

any suggestion?
i thought about some case statements, but this seems not so good...

thanks!!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陪你到最终 2024-10-31 05:11:42

仅 6 个月，日期表似乎没有必要，尽管这看起来很复杂（实际上并非如此！）

SELECT DATE_FORMAT(N.PivotDate,'%Y%m'), count(rs.rsc_id) as counter
FROM (
   select ADDDATE(CURDATE(), INTERVAL N MONTH) PivotDate
   FROM (
       select 0 N union all
       select 1 union all
       select 2 union all
       select 3 union all
       select 4 union all
       select 5 union all
       select 6) N) N
LEFT JOIN shots as rs
    ON rsc_rs_id = 345354
    AND DATE_FORMAT(N.PivotDate,'%Y%m')=DATE_FORMAT(FROM_UNIXTIME(rs.timestamp),'%Y%m')
GROUP BY DATE_FORMAT(N.PivotDate,'%Y%m')

For only 6 months, a date table seems unnecessary, although this looks complicated (it really isn't!)

SELECT DATE_FORMAT(N.PivotDate,'%Y%m'), count(rs.rsc_id) as counter
FROM (
   select ADDDATE(CURDATE(), INTERVAL N MONTH) PivotDate
   FROM (
       select 0 N union all
       select 1 union all
       select 2 union all
       select 3 union all
       select 4 union all
       select 5 union all
       select 6) N) N
LEFT JOIN shots as rs
    ON rsc_rs_id = 345354
    AND DATE_FORMAT(N.PivotDate,'%Y%m')=DATE_FORMAT(FROM_UNIXTIME(rs.timestamp),'%Y%m')
GROUP BY DATE_FORMAT(N.PivotDate,'%Y%m')

回复收藏 0 原文

乖乖 2024-10-31 05:11:42

在这种情况下，通常使用包含所有日期（例如从 1970 年 1 月 1 日到 2999 年 12 月 31 日）的日期表，并将您的数据LEFT JOIN添加到该表中。桌子。

请参阅此处答案中的示例： mysql 连接创建缺失日期的表

如果您创建一个您可以使用的日期表：

SELECT
DATE_FORMAT(d.date,'%Y%m') AS `month`, count(rs.rsc_id) AS `counter`
FROM dates d
LEFT JOIN shots as rs
   ON d.date = FROM_UNIXTIME(rs.timestamp)
   AND rs.rsc_rs_id = 345354
WHERE d.date > DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
AND d.date < CURDATE()
GROUP BY DATE_FORMAT(d.date,'%Y%m');

In such cases it's common to use a table of dates with all dates (e.g. from 1/1/1970 to 31/12/2999) and LEFT JOIN your data to that table.

See an example in the answer here: mysql joins tables creating missing dates

If you create a dates table you can use:

SELECT
DATE_FORMAT(d.date,'%Y%m') AS `month`, count(rs.rsc_id) AS `counter`
FROM dates d
LEFT JOIN shots as rs
   ON d.date = FROM_UNIXTIME(rs.timestamp)
   AND rs.rsc_rs_id = 345354
WHERE d.date > DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
AND d.date < CURDATE()
GROUP BY DATE_FORMAT(d.date,'%Y%m');

回复收藏 0 原文

~没有更多了~