mysql, преобразовывающий несколько строк в столбцы в одной строке
у меня есть таблица деталей со столбцами:
- интервал user_id
- интервал кода
- интервал значения
И я хочу создать сводную таблицу, которая похожа:
- интервал user_id
- интервал valueA
- интервал valueB
В таблице деталей valueA соответствовал бы, чтобы сказать, кодировать 5, и valueB будет соответствовать, чтобы сказать, кодировать 6, таким образом, я буду искать что-то как:
вставьте в сводку (user_id, valueA, valueB) ЗНАЧЕНИЯ (ВЫБОР??? от деталей);
Проблема, конечно, состоит в том, что я смотрю на несколько строк от таблицы "деталей" для заполнения одной строки в "сводной" таблице.
Например, если у меня были следующие строки в деталях:
1 5 100
1 6 200
2 5 1000
2 6 2000
Я хочу закончить со следующим в сводной таблице:
1 100 200
2 1000 2000
Какие-либо идеи?
3 ответа
В MySQL нет синтаксиса PIVOT/UNPIVOT, поэтому вам придется использовать комбинацию выражений GROUP BY и CASE:
INSERT INTO SUMMARY
(user_id,valueA,valueB)
SELECT d.user_id,
MAX(CASE WHEN d.code = 5 THEN d.value ELSE NULL END),
MAX(CASE WHEN d.code = 6 THEN d.value ELSE NULL END),
FROM DETAILS d
GROUP BY d.user_id
Если у вас есть управляемый набор кодов (скажем, только 5 и 6), вы можете сделать что-то вроде этого:
SELECT details.user_id, code5.value, code6.value
FROM details JOIN
(SELECT user_id, value FROM details WHERE code = 5) AS code5 USING(user_id)
JOIN
(SELECT user_id, value FROM details WHERE code = 6) AS code6 USING(user_id);
Вам может понадобиться изменить ваши JOINы в зависимости от того, не требуются ли ваши коды в качестве отношений 1 к 1 (т.е. LEFT JOINы).
Если у вас большой набор кодов, я бы рассмотрел возможность использования курсора, выполняющего аналогичный запрос над набором результатов ваших кодов, или использования другой технологии (например, PHP-скрипта).
insert into summary (user_id,valueA,valueB)
SELECT a.user_id, a.value, b.value
from details a
join details b on a.user_id = b.user_id
WHERE a.code = 5 and b.code = 6;
осторожно: если user_id+code не уникальны, у вас будет несколько итоговых столбцов.
EDIT:
insert into summary (user_id,valueA,valueB)
select u.user_id, ifnull(a.value,0), ifnull(b.value,0)
from (select distinct user_id from details /* where code in (5,6) */) u
left join details a on a.user_id = u.user_id and a.code = 5
left join details b on b.user_id = u.user_id and b.code = 6