Как я могу выполнить этот групповой запрос mysql

У меня есть две таблицы 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 

До этого нет проблем, но мне также нужно проверить, соответствует ли значение shop_id таблицы B значению, и если это так, мне нужно «количество» этой единственной строки, а не СУММ всех сгруппированных строк. Надеюсь, это понятно.

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=1
doe      9         0         //sum(7,2) because shop is not 1
smith    4         0         //sum(3,1) 

Я думал об операторе if при выборе SUM () чего-то похожего на это, но приведенный ниже оператор возвращает не желаемое значение группированной строки

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  

Любые идеи ? Есть ли способ поместить это условие в ГРУППУ, например: case shop<>1 THEN .... ELSE GROUP BY a.id ??

0
0
41
2

Ответы 2

Вы были на правильном пути, применив то, что называется условным агрегированием. Вот версия, которая должна работать:

SELECT
    a.name,
    CASE WHEN MAX(CASE WHEN b.shop = 1 THEN 1 ELSE 0 END) > 0
         THEN SUM(CASE WHEN b.shop = 1 THEN b.amount ELSE 0 END)
         ELSE SUM(b.amount) END AS amount,
    MAX(CASE WHEN b.shop = 1 THEN 1 ELSE 0 END) AS shop
FROM a
LEFT JOIN b
    ON a.id = b.a_id
GROUP BY
    a.name;

enter image description here

Демо

Я тестирую это, пока выглядит неплохо. Спасибо за быстрый ответ. Как вы думаете, решение Мадхура будет работать медленнее?

Ninet9 31.10.2018 13:53

Хороший вопрос, и вы можете протестировать оба запроса. Его ответ выглядит чище и сложнее, чем мой.

Tim Biegeleisen 31.10.2018 13:53

Вы можете заменить SUM () на Max (), когда shop = 1; в противном случае, если OP имеет повторяющиеся данные для shop = 1; тогда он будет вычислять сумму, а не только одно из значений; +1

Madhur Bhaiya 31.10.2018 13:57
  • Вам потребуется дважды выполнить Left Join с таблицей b.
  • Первое соединение позволит вычислить сумму.
  • Второе соединение будет присоединяться только тогда, когда shop = 1. Итак, если мы получим какое-то ненулевое значение из-за второго соединения, мы будем считать это, иначе 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    |

Посмотреть на DB Fiddle

Это получает одобрение, потому что это настолько сложно, что на самом деле больно смотреть.

Tim Biegeleisen 31.10.2018 13:56

@TimBiegeleisen спасибо; Однако я сомневаюсь в его эффективности из-за двойного соединения.

Madhur Bhaiya 31.10.2018 13:58

@ Ninet9 вы сравнивали два запроса? Что в итоге ?

Madhur Bhaiya 31.10.2018 14:47

Нет, у меня еще не заполнена база данных. Но эффективность очень важна, таблица A будет иметь около 10 000, B - около 50 000 строк, причем в обеих будет намного больше полей. На первый взгляд решение Тима кажется более быстрым, но я протестирую и то, и другое.

Ninet9 03.11.2018 13:19

@ Ninet9 Смотрите: Как принять ответ на закрытие. Спасибо :)

Madhur Bhaiya 15.11.2018 16:53

Другие вопросы по теме