mysql - 我怎样才能实现这个mysql组查询

Question

我有两个表 A 和 B。我的目标是列出 A 的每一行，同时附上 B 的“金额”的总和。

像这样:

SELECT a.name, 
       SUM(b.amount) as amount 
FROM a 
LEFT JOIN b ON a.id = b.a_id 
GROUP BY a.id 

在此之前没有问题，但我还需要检查 B 表的“shop_id”是否与某个值匹配，如果匹配，我想要该单行的“金额”，而不是所有分组行的总和。我希望这是可以理解的。

Table A  
id     name
----------------
1      john
2      doe
3      smith

Table B
a_id    amount    shop
-----------------------
1         4        1
1         3        2
2         2        2
2         7        3
3         3        3
3         1        2

'shop'=1 的期望结果:

name   amount    shop---------------------john     4         1         //no SUM, only the value of amount where shop=1doe      9         0         //sum(7,2) because shop is not 1smith    4         0         //sum(3,1) 

I was thinking of an if statement at the SUM() selection something similar to this, but the below statement returns not the desired groupped row value

SELECT a.name,
       ( CASE
           WHEN b.shop <> 1 THEN Sum(b.amount)
           ELSE b.amount
         end ) AS amount,
       ( CASE
           WHEN b.shop <> 1 THEN 0
           ELSE b.shop
         end ) AS shop
FROM   a
       LEFT JOIN b
              ON a.id = b.a_id
GROUP  BY a.id  

有什么想法吗？有没有办法将此条件添加到 GROUP 中，例如:case shop<>1 THEN .... ELSE GROUP BY a.id ??

Answer 1

• 您需要对表 b Left Join 两次。
• First Join 将启用 Sum 的计算。
• 只有当 shop = 1 时，Second Join 才会加入。因此，如果我们由于 second join 而获得一些非空值，我们将考虑它，否则 Sum

尝试以下操作:

SELECT 
  a.id, 
  a.name, 
  COALESCE(MAX(b2.amount), SUM(b1.amount)) AS amount, 
  COALESCE(b2.shop, 0) AS shop 
FROM a 
LEFT JOIN b AS b1 ON a.id = b1.a_id 
LEFT JOIN b AS b2 ON a.id = b2.a_id AND b2.shop = 1
GROUP BY a.id, a.name 

结果

| id  | name  | amount | shop |
| --- | ----- | ------ | ---- |
| 1   | john  | 4      | 1    |
| 2   | doe   | 9      | 0    |
| 3   | smith | 4      | 0    |

---

View on DB Fiddle