我有两个表;
banner_views (id, b_id, b_date)
-每次显示时都会记录横幅视图
banners_dynamic (id, status, static_iname, static_keywords, static_url, static_alt, static_type, static_image, b_views, b_clicks)
-存储横幅数据
我想选择在过去7天内浏览量最少的3个banners_dynamic
结果。
我确实把一些横幅放在一起(见下文),但我意识到它是在获取所有横幅的总浏览量,而不是唯一的id。
SELECT *,
(SELECT COUNT(*) FROM banner_views v WHERE v.b_date >= DATE(NOW()) - INTERVAL 7 DAY) as post_count
FROM banners_dynamic b
WHERE static_keywords LIKE '%test%' AND b.status='1' AND b.static_type='1'
ORDER BY post_count ASC LIMIT 3
有人能给我指正确的方向吗?
您必须将banners_dynamic
表和您的子查询与相应的条幅ID连接起来:
SELECT
b.*, p.b_count
FROM
banners_dynamic b
INNER JOIN (
SELECT
b_id,
COUNT(*) AS b_count
FROM
banner_views v
WHERE
v.b_date >= DATE(NOW() - INTERVAL 7 DAY)
GROUP BY
b_id
) p on p.b_id = b.id
WHERE
b.static_keywords LIKE '%test%'
AND b.`status` = '1'
AND b.static_type = '1'
ORDER BY
p.b_count ASC
LIMIT 3
更新:即使没有子查询也可以执行:
SELECT
b.*, COUNT(v.b_id) AS b_count
FROM
banners_dynamic b
INNER JOIN banner_views v ON v.b_id = b.id
WHERE
v.b_date >= DATE_ADD(NOW(), INTERVAL - 7 DAY)
AND b.static_keywords LIKE '%test%'
AND b.`status` = '1'
AND b.static_type = '1'
GROUP BY
v.b_id
ORDER BY
b_count ASC
LIMIT 3;
如果你想包括没有任何视图的横幅(计数=0),那么你必须执行LEFT JOIN
:
SELECT
b.*, COUNT(v.b_id) AS b_count
FROM
banners_dynamic b
LEFT JOIN banner_views v ON v.b_id = b.id
AND v.b_date >= DATE_ADD(NOW(), INTERVAL - 7 DAY)
WHERE
b.static_keywords LIKE '%test%'
AND b.`status` = '1'
AND b.static_type = '1'
GROUP BY
v.b_id
ORDER BY
b_count ASC
LIMIT 3;