MySQL 查询 w/ COUNT() 和 HAVING 基于日期比较

发布于 2024-11-16 16:17:10 字数 715 浏览 0 评论 0原文

我试图将我的计数结果限制为满足特定标准的结果。我当前正在使用 WHERE 子句，并且我的输出符合预期。

这是我当前的查询，正如您所看到的，只有当我的 WHERE recdate 满足 14 天标记时，才会获取 *ads_list* 和 *ads_cate* 记录：

$queryCats = "
SELECT ads_cate.cateName, COUNT(ads_list.Title)
FROM
ads_cate
JOIN ads_list ON ads_cate.id = ads_list.category 
WHERE to_days(now())<=(to_days(recdate)+14)
GROUP BY ads_cate.cateName";

我希望检索所有类别和广告，但是仅当它们满足我的 WHERE 子句标准时才计数。我一直在使用 HAVING，运气为 0，如下所示。也许有更好的方法？

$queryCats = "
SELECT ads_cate.cateName, ads_list.recdate, COUNT(ads_list.Title)
FROM
ads_cate
JOIN ads_list ON ads_cate.id = ads_list.category 
GROUP BY ads_cate.cateName
HAVING to_days(now())<=(to_days(recdate)+14)";

原文

I am trying to limit my count results to those that meet a specific criteria. I am currently using a WHERE clause and my output is as expected.

Here is my current query, and as you can see *ads_list* and *ads_cate* records will only be fetched if my WHERE recdate meets the 14 day mark:

$queryCats = "
SELECT ads_cate.cateName, COUNT(ads_list.Title)
FROM
ads_cate
JOIN ads_list ON ads_cate.id = ads_list.category 
WHERE to_days(now())<=(to_days(recdate)+14)
GROUP BY ads_cate.cateName";

I would like all categories and ads retrieved, but counted ONLY when they meet my WHERE clause criteria. I have been using the HAVING with 0 luck like below. Maybe there is a better way?

$queryCats = "
SELECT ads_cate.cateName, ads_list.recdate, COUNT(ads_list.Title)
FROM
ads_cate
JOIN ads_list ON ads_cate.id = ads_list.category 
GROUP BY ads_cate.cateName
HAVING to_days(now())<=(to_days(recdate)+14)";

分享到QQ

分享到微博

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

发布评论

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

┾廆蒐ゝ 2024-11-23 16:17:10

尝试使用 LEFT JOIN 并将日期测试作为连接条件的一部分：

SELECT ac.cateName, COUNT(al.Title)
    FROM ads_cate ac
        LEFT JOIN ads_list al
            ON ac.id = al.category 
                AND to_days(now())<=(to_days(al.recdate)+14)
    GROUP BY ac.cateName

Try using a LEFT JOIN and make the date test part of the join condition:

SELECT ac.cateName, COUNT(al.Title)
    FROM ads_cate ac
        LEFT JOIN ads_list al
            ON ac.id = al.category 
                AND to_days(now())<=(to_days(al.recdate)+14)
    GROUP BY ac.cateName

回复收藏 0 原文

蝶…霜飞 2024-11-23 16:17:10

这可能有效：

SELECT ads_cate.cateName, ads_list.recdate, COUNT(CASE WHEN to_days(now())<=to_days((recdate)+14) THEN ads_list.Title ELSE NULL END) AS Title
FROM
ads_cate
JOIN ads_list ON ads_cate.id = ads_list.category 
GROUP BY ads_cate.cateName

This may work:

SELECT ads_cate.cateName, ads_list.recdate, COUNT(CASE WHEN to_days(now())<=to_days((recdate)+14) THEN ads_list.Title ELSE NULL END) AS Title
FROM
ads_cate
JOIN ads_list ON ads_cate.id = ads_list.category 
GROUP BY ads_cate.cateName

回复收藏 0 原文