php - SQL 遗漏了一些东西..影响了结果

Question

我在 MYSQLI 中使用了这段代码:

SELECT da_brands.name AS brand_name,
    COUNT(da_deals.id) AS total_deals,
    0 AS total_downloaded_coupons,
    0 AS total_validated_coupons,
    COUNT(da_logs.id) AS total_likes
FROM da_brands, da_deals LEFT JOIN da_logs
    ON da_logs.fk_deal_id = da_deals.id
    AND da_logs.type = 'deal_like'
WHERE da_brands.fk_club_id = 6
    AND da_deals.fk_brand_id = da_brands.id
GROUP BY da_brands.name
ORDER BY da_brands.name ASC

结果:

brand_name  total_deals    total_downloaded_coupons   total_validated_coupons   otal_likes
Marca2      2              0                          0                         1
Marca1      12             0                          0                         4

应该是:

brand_name  total_deals    total_downloaded_coupons   total_validated_coupons   total_likes
Marca2      2              0                          0                         1
Marca1      9              0                          0                         4

有什么想法吗？

Answer 1

我相信您需要 COUNT(DISTINCT da_deals.id) AS total_deals,，因为您只想对每个交易 ID 计数一次。

编辑:您的 FROM 语句是隐式内部联接(逗号)和显式外部联接的列表。我错过了。

您的查询可能应该像这样编写，并包含所有显式连接:

SELECT da_brands.name AS brand_name,
    COUNT(DISTINCT da_deals.id) AS total_deals,
    0 AS total_downloaded_coupons,
    0 AS total_validated_coupons,
    COUNT(DISTINCT da_logs.id) AS total_likes
FROM da_brands
INNER JOIN da_deals
    ON da_deals.fk_brand_id = da_brands.id
LEFT JOIN da_logs
    ON da_logs.fk_deal_id = da_deals.id
    AND da_logs.type = 'deal_like'
WHERE da_brands.fk_club_id = 6
GROUP BY da_brands.name
ORDER BY da_brands.name ASC;

要在品牌缺乏交易和点赞的情况下展示品牌，请尝试使用如下所示的 LEFT JOIN:

SELECT da_brands.name AS brand_name,
    COUNT(DISTINCT da_deals.id) AS total_deals,
    0 AS total_downloaded_coupons,
    0 AS total_validated_coupons,
    COUNT(DISTINCT da_logs.id) AS total_likes
FROM da_brands
LEFT JOIN da_deals
    ON da_deals.fk_brand_id = da_brands.id
LEFT JOIN da_logs
    ON da_logs.fk_deal_id = da_deals.id
    AND da_logs.type = 'deal_like'
WHERE da_brands.fk_club_id = 6
GROUP BY da_brands.name
ORDER BY da_brands.name ASC;