根据table2上的条目从table1中选择结果 [英] Select results from table1 based on entries on table2

问题描述

我有2个表;

banner_views（id，b_id，b_date） - 此记录横幅视图每次显示

banner_views (id, b_id, b_date)- this record a banner view every time it gets displayed

banners_dynamic（id，status，static_iname，static_keywords，static_url，static_alt，static_type，static_image，b_views，b_clicks） code> - 存储横幅数据

banners_dynamic (id, status, static_iname, static_keywords, static_url, static_alt, static_type, static_image, b_views, b_clicks) - stores the banner data

我想选择3 banners_dynamic 最近7天内看到的最少看法。

I would like to select 3 banners_dynamic results which have had the least views in the last 7 days.

我把某些东西放在一起（见下文），但是我意识到这是抓住所有横幅的总体看法，而不是唯一的id。

I did put somethign together (see below) but I realised it was grabbing the total views for all banner rather than uniquely by 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

任何人都可以指向正确的方向？

Can anyone point me in the correct direction?

推荐答案

您必须同时加入 banners_dynamic 表和您的具有相应横幅ID的子查询：

You must join both banners_dynamic table and your subquery with corresponding banner IDs:

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

更新：即使没有子查询，您也可以执行此操作：

UPDATE: You can do it even without subquery:

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;

如果要包含没有任何视图（count = 0）的横幅，则必须执行 LEFT JOIN ：

If you want to include banners without any views (count=0) then you must do a 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;

这篇关于根据table2上的条目从table1中选择结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！