php - 从一个表中选择计数，并在另一个表上选择“位置”

Question

我有两张 table

• oc2_visits(字段:id_ad)
• oc2_ads(字段:已发布)

我需要通过对 id_ad 进行分组来计算访问次数，这可行，但我还需要前 9 个结果发布时间不超过 25 天。

这是我目前的疑问:

SELECT count(v.id_ad) AS visits,
       v.id_ad,
       a.published
FROM oc2_visits AS v,
     oc2_ads AS a
WHERE DATE(a.published) >= DATE_SUB(NOW(), INTERVAL 25 DAY)
GROUP BY v.id_ad
ORDER BY visits DESC LIMIT 0,9

但是当我尝试在 phpmyadmin 中输入查询时，它崩溃了。

我做错了什么？

Answer 1

我认为您在与 DATE 进行比较时忘记将 DATE_SUB(NOW(), INTERVAL 25 DAY) 解析为 DATE 。希望它能发挥作用。

SELECT count(v.id_ad) AS visits,
       v.id_ad,
       a.published
FROM oc2_visits AS v,
     oc2_ads AS a
WHERE DATE(a.published) >= DATE(DATE_SUB(NOW(), INTERVAL 25 DAY))
GROUP BY v.id_ad
ORDER BY visits DESC LIMIT 0,9