#mysql #select #inner-join
Вопрос:
У меня есть 2 таблицы (MySQL), таблица обмена может содержать 1—n строк в пунктах обмена, когда запись обмена содержит несколько строк, я хотел бы отобразить слово «мульти», но когда есть только 1 строка, я хотел бы отобразить сведения о строке:
Первый стол (обмен):
xid (PK) | cusid | xdate | xref | xtotal
1 | 1 | 2021-10-01 | 345667 | 500
2 | 1 | 2021-10-01 | 345668 | 200
3 | 1 | 2021-10-02 | 345669 | 450
4 | 1 | 2021-10-03 | 345670 | 1200
И вторая таблица (пункты обмена):
chid (PK) | xid | cusid | xcur| xsell| xbuy
1 | 1 | 1 | USD | 300 | 0
2 | 1 | 1 | EUR | 0 | 400
3 | 2 | 1 | USD | 200 | 0
4 | 3 | 1 | EUR | 0 | 500
5 | 4 | 1 | EUR | 0 | 800
6 | 4 | 1 | USD | 300 | 0
Таблица обмена должна содержать по крайней мере 1 строку в таблице exchtiems, и это то, что я хотел бы получить:
xid | cusid | xdate | xref | xcur | xsell | xbuy | xtotal
1 | 1 | 2021-10-01 | 345667 | multi | 0 | 0 | 500
2 | 1 | 2021-10-01 | 345668 | USD | 200 | 0 | 200
3 | 1 | 2021-10-02 | 345669 | EUR | 0 | 500 | 450
4 | 1 | 2021-10-03 | 345670 | multi | 0 | 0 | 1200
Используя следующий запрос, я могу получить все записи, но я хотел бы ограничить таблицу exchitems одной строкой «любая строка», если строк несколько, счетчик используется для отображения слова «несколько», когда оно > 1:
SELECT a.xid,a.xdate,a.xref,a.xtotal,b.xcur,b.xsell,b.xbuy,
(SELECT COUNT(*) FROM exchitems c WHERE c.xid= a.xid) AS tRec
FROM (exchange a
INNER JOIN exchitems b ON a.xid= b.xid AND a.cusid= b.cusid)
WHERE a.cusid = 1
ORDER BY a.xdate DESC,a.xid DESC
Я пробовал много разных запросов, но не смог добиться того, чего хочу.
Любая помощь будет высоко оценена.
Комментарии:
1.
GROUP BY xidиCOUNT(chid)должен сделать свое дело.
Ответ №1:
Непроверено, но это должно сработать.
SELECT
a.xid,
a.cusid,
a.xdate,
a.xref,
-- if distinct currency in the group is > 1 then the word 'multi', else currency.
IF(COUNT(DISTINCT b.xcur) > 1, 'multi', b.xcur) AS `xcur`,
b.xsell,
b.xbuy,
a.xtotal
FROM exchange a
JOIN exchitems b ON a.xid = b.xid AND a.cusid = b.cusid
WHERE a.cusid = 1
GROUP BY xid -- will let you have exchange rows with groups of exchitems 1:n
ORDER BY a.xdate DESC, a.xid DESC
Комментарии:
1. Ценится за быстрый ответ, я думаю, что группа может сделать трюк, я должен проверить его на больших масштабах, я думаю, что «если(количество(уникальных б.xcur) > 1, ‘мульти’, б.xcur), как
xcur» должно быть «если(число(род.чид) > 1, ‘мульти’, б.xcur), какxcur«.2. @Alan Если, например, 2 строки, объединенные из
exchitemstbl, получили одну и ту же валюту, тоCOUNT(DISTINCT b.xcur)следует вернуть 1. Так что не было бы нескольких валют =)3. использование COUNT(b.chid) будет работать во всех случаях, так как бывают ситуации, когда повторяется одна и та же валюта!.
4. Хм, я думал, вы просто хотите показать
multi, существует ли несколько разных валют. Я имею в виду, я знаю, что вы написали, что хотите, если > 1, но в моей книге та же валюта x n по — прежнему остается той же валютой 😀 ПРАВКА: но я вижу, что вам нужны и другие строки. Так что да, вы правы.
Ответ №2:
Вы можете изменить свой текущий запрос следующим образом:
SELECT a.xid, a.cusid, a.xdate,
a.xref,
GROUP_CONCAT(b.xcur),
MIN(b.xsell),
MIN(b.xbuy),
MAX(a.xtotal)
FROM (exchange a
INNER JOIN exchitems b ON a.xid= b.xid AND a.cusid= b.cusid)
WHERE a.cusid = 1
GROUP BY a.xid,a.cusid,a.xdate,a.xref
ORDER BY a.xid;
Результат будет выглядеть так:
| xid | кусид | xdate | ссылка | GROUP_CONCAT(род.xcur) | МИН(б.xsell) | МИН(б.xbuy) | МАКС. (а.итого) |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2021-10-01 | 345667 | ДОЛЛ. США,ЕВРО | 0 | 0 | 500 |
| 2 | 1 | 2021-10-01 | 345668 | Долл. США | 200 | 0 | 200 |
| 3 | 1 | 2021-10-02 | 345669 | Евро | 0 | 500 | 450 |
| 4 | 1 | 2021-10-03 | 345670 | ЕВРО,доллары США | 0 | 0 | 1200 |
Та часть, где я использую MIN и MAX соответствует вашему ожидаемому результату. Возможно, вы захотите уточнить, какое значение, чтобы показать, что у вас есть несколько значений. Если я изменю это на GROUP_CONCAT :
SELECT a.xid, a.cusid, a.xdate,
a.xref,
GROUP_CONCAT(b.xcur),
GROUP_CONCAT(b.xsell),
GROUP_CONCAT(b.xbuy),
GROUP_CONCAT(a.xtotal)
FROM (exchange a
INNER JOIN exchitems b ON a.xid= b.xid AND a.cusid= b.cusid)
WHERE a.cusid = 1
GROUP BY a.xid,a.cusid,a.xdate,a.xref
ORDER BY a.xid;
Тогда вы увидите более сложный результат:
| xid | кусид | xdate | ссылка | GROUP_CONCAT(род.xcur) | GROUP_CONCAT(b.xsell) | GROUP_CONCAT(b.xbuy) | GROUP_CONCAT(a.xtotal) |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2021-10-01 | 345667 | ДОЛЛ. США,ЕВРО | 300,0 | 0,400 | 500,500 |
| 2 | 1 | 2021-10-01 | 345668 | Долл. США | 200 | 0 | 200 |
| 3 | 1 | 2021-10-02 | 345669 | Евро | 0 | 500 | 450 |
| 4 | 1 | 2021-10-03 | 345670 | ЕВРО,доллары США | 0,300 | 800,0 | 1200,1200 |
Чтобы показать xcur ценность multi , вы, вероятно, можете сделать что-то вроде:
SELECT a.xid, a.cusid, a.xdate,
CASE WHEN COUNT(b.xcur) > 1 THEN 'multi' ELSE MAX(b.xcur) END AS xcur,
MIN(b.xsell),
MIN(b.xbuy),
MAX(a.xtotal)
FROM (exchange a
INNER JOIN exchitems b ON a.xid= b.xid AND a.cusid= b.cusid)
WHERE a.cusid = 1
GROUP BY a.xid,a.cusid,a.xdate,a.xref
ORDER BY a.xid;
Комментарии:
1. Спасибо, что нашли время, я хотел бы принять оба ответа, не уверен, что это возможно, пришлось отдать его @cottton, когда он ответил первым. очень признателен
2. Все в порядке, однако на сервере MySQL версии 5.7 и выше запрос @cotton не будет работать, если
sql_modeсохранена настройка по умолчанию; котораяONLY_FULL_GROUP_BYсохраняется. Обратитесь к этому тесту скрипки . И рекомендуется сохранить настройкуON, я цитирую «сервер может свободно выбирать любое значение из каждой группы, поэтому, если они не совпадают, выбранные значения являются недетерминированными, что, вероятно, не то, что вы хотите» из документации