求和忽略何地，而不按

Question

我有两张桌子，adverts和advertsitems。这些表得到一个1 (adverts)到多(advertsitems)关系。我正在动态地构建这个查询，这就是为什么我使用WHERE 1=1来更容易地添加新的条件。

我有以下查询

  SELECT a.title AS adtitle, 
         a.id AS adid, 
         a.price, 
         a.image AS image, 
         a.description, 
         SUM(ai.quantity) as quantity, 
         a.shipping, 
         a.customs, 
         a.createdate
    FROM adverts a
           LEFT JOIN advertsitems ai 
                     ON a.id = ai.advertid
   WHERE 1=1 
     AND ai.country LIKE '%AF%'
GROUP BY a.id
ORDER BY a.id DESC

在这种情况下，SUM(ai.quantity)的结果是2。如果删除WHERE条件ai.country LIKE，结果是6。我想在两个独立的列中撤回这两个值。因此，基本上，我想要一个忽略WHERE而不是GROUP BY的GROUP BY。

Answer 1

使用条件聚合：

SELECT a.title AS adtitle, a.id AS adid, a.price, a.image AS image, a.description,
       SUM(ai.quantity) as totalquantity,
       SUM(case when ai.country LIKE '%AF%' then quantity else 0 end) as AFquantity
      a.shipping, a.customs, a.createdate, ai.country
FROM adverts a LEFT JOIN
     advertsitems ai
     ON a.id = ai.advertid
WHERE 1=1
GROUP BY a.id
ORDER BY a.id DESC

如果实际上希望按国家进行筛选，则使用having子句。它将在聚合之后进行过滤：

SELECT a.title AS adtitle, a.id AS adid, a.price, a.image AS image, a.description,
       SUM(ai.quantity) as totalquantity,
       SUM(case when ai.country LIKE '%AF%' then quantity else 0 end) as AFquantity
      a.shipping, a.customs, a.createdate
FROM adverts a LEFT JOIN
     advertsitems ai
     ON a.id = ai.advertid
WHERE 1=1 
GROUP BY a.id
HAVING ai.country LIKE '%AF%'
ORDER BY a.id DESC