Question

< 坚固>

SELECT DISTINCT  b.b_id 
FROM             b INNER JOIN c 
ON               b.b_id=c.b_id  
WHERE            c.active= yes  AND b.featured= no

返回 0 行时返回结果时, < 强 > 返回返回时, 这将返回一行空数, 计数= 0

SELECT DISTINCT  b.b_id, COUNT(c.c_id) AS count
FROM             b INNER JOIN c 
ON               b.b_id=c.b_id  
WHERE            c.active= yes  AND b.featured= no

我做错什么了吗?

Answer 1

我想你想要一个 left join 而不是一个 inner 加入 ,因为当给定的 b 记录没有匹配 c 记录时, 您想要返回 0 的计数而不是缺失的行。

另外,使用总函数,例如 count ,应该包含 group, by 。

SELECT
    b.b_id,
    COUNT(DISTINCT c.c_id) AS count
FROM 
    b 
    LEFT JOIN c 
        ON b.b_id=c.b_id  
        AND c.active= yes  
WHERE b.featured= no 
GROUP BY b.b_id

Answer 2

尝试在 DISTINCT 旁边 in /em> COUNT () :

SELECT b.b_id, COUNT(DISTINCT c.c_id) AS count FROM b JOIN c ON b.b_id=c.b_id WHERE c.active= yes AND b.featured= no GROUP BY 1

btw, 请考虑将您的 SQL 格式像这样, 更易读 IMHO 格式化。

Answer 3

我宁愿尝试群集而不是远古。

    SELECT b.b_id, COUNT(c.c_id) AS count
    FROM 
    b INNER JOIN c 
    ON 
    b.b_id=c.b_id  
    WHERE 
    c.active= yes  
    AND 
    b.featured= no 
    GROUP BY b.b_id

友情链接