php - mysql返回相同的行并集

Question

在我的数据库中，我有类别、报价和优惠券。我想计算每个类别中存在的优惠和优惠券。当我使用 union 时，它会两次返回相同的类别。我有以下查询返回具有相同名称的相同类别行。我尝试使用 union distinct 但它不起作用。

(SELECT 
        cat1.id AS cat1id, cat1.title AS title,
        count(offers.id) AS offercounter

    FROM cat1

    INNER JOIN offers 
    ON offers.category=cat1.title

    GROUP BY cat1.id
    order by cat1.order)
     UNION
    (SELECT 
        cat1.id AS cat1id, cat1.title AS title,
        count(coupons.id) AS couponscounter

    FROM cat1

    INNER JOIN coupons 
    ON coupons.category=cat1.title

    GROUP BY cat1.id
    order by cat1.order)

结果

cat1id  title         offercounter
 2       Food              5388
 23      Clothes           6000(this is offers)
 32      Technology         499
 40      Clothes            4(this is coupons)

我希望衣服是 (offercounter + couponscounter)。示例:clothes=6004 而不是两个不同的行

期望的结果是:

cat1id  title         offercounter
 2       Food              5388
 23      Clothes           6004(offers+coupons)
 32      Technology         499

Answer 1

避免联合或子查询的替代方法是使用一对左外连接，并计算每个表中不同的 ID:-

SELECT cat1.id AS cat1id, 
        cat1.title AS title,
        COUNT(DISTINCT offers.id) + COUNT(DISTINCT coupons.id) AS offercounter
FROM cat1
LEFT OUTER JOIN offers ON offers.category = cat1.title
LEFT OUTER JOIN coupons ON coupons.category = cat1.title
GROUP BY cat1.id AS cat1id, 
        cat1.title AS title

编辑

当没有匹配行时，左外连接将返回一行空值。

例如，如果在 cat1 上有一行与优惠匹配的行但在优惠券上没有匹配的行，那么结果行将由来自 cat1 的行、优惠的行和优惠券的字段组成。

此 SQL 将获取匹配行的每个组合。所以如果你有:-

cat1 fields     offers fields       coupons fields
id  title       id      category    id      category
1   fred        99      fred        77      fred
1   fred        99      fred        88      fred
1   fred        100     fred        77      fred
1   fred        100     fred        88      fred
2   burt        120     fred        NULL    NULL
2   burt        121     fred        NULL    NULL

因此，计数使用 DISTINCT 只能对类别中的每个 ID 进行一次。由于 COUNT(field name) 仅计算非空值，对于第二类的此示例数据，优惠券的计数将为 0。