MySQL - несколько строк по 1 идентификатору и преобразование данных из нескольких столбцов в строку с разделителями-запятыми
У меня есть таблица базы данных:
столбец идентификатора запрашивающего лица, который отделяет каждый запрос (reqid)
столбец идентификатора категории, который разделяет на разные категории каждый элемент в запросе (catid)
столбец идентификатора элемента, который разделяет каждый тип элемента в категории (itemid)
столбец с названием предмета, в котором указано название предмета (предмета)
столбец дескриптора предмета, который дает описание предмета (описание)
и столбец суммы, в котором указана общая стоимость каждого предмета в этой категории (сумма)
поэтому моя таблица выглядит так:
|---------|-------|----------|--------|-------|------------|
| reqid | catid | itemid | item | desc | amount |
|---------|-------|----------|--------|-------|------------|
| 1 | 3 | 16 | food | food | 200 |
|---------|-------|----------|--------|-------|------------|
| 1 | 3 | 17 | water | wtr | 50 |
|---------|-------|----------|--------|-------|------------|
| 1 | 3 | 18 | film | film | 20 |
|---------|-------|----------|--------|-------|------------|
| 1 | 5 | 30 | room | room | 500 |
|---------|-------|----------|--------|-------|------------|
| 1 | 5 | 31 | chair | chair | 150 |
|---------|-------|----------|--------|-------|------------|
| 2 | 3 | 16 | food | food | 200 |
|---------|-------|----------|--------|-------|------------|
| 2 | 3 | 17 | water | wtr | 50 |
|---------|-------|----------|--------|-------|------------|
| 3 | 3 | 18 | film | film | 20 |
|---------|-------|----------|--------|-------|------------|
| 3 | 5 | 30 | room | room | 500 |
|---------|-------|----------|--------|-------|------------|
| 3 | 5 | 31 | chair | chair | 150 |
|---------|-------|----------|--------|-------|------------|
И я хочу, чтобы результат моего запроса выглядел как
|--------|------------------|----------------------|--------------------|--------------------|
| reqid | catid3itemid | catid3item | catid3desc | catid3amount |
|--------|------------------|----------------------|--------------------|--------------------|
| 1 |16, 17, 18 | food, water, film | food, wtr, film | 200, 50, 20 |
|--------|------------------|----------------------|--------------------|--------------------|
| 2 |16, 17 | food, water |food, wtr | 200, 50, |
|--------|------------------|----------------------|--------------------|--------------------|
| 3 |18 | film | film | 20 |
|--------|------------------|----------------------|--------------------|--------------------|
продолжение:
|------------------|----------------------|--------------------|--------------------|
| catid4itemid | catid4item | catid4desc | catid4amount |
|------------------|----------------------|--------------------|--------------------|
| | | | |
|------------------|----------------------|--------------------|--------------------|
| | | | |
|------------------|----------------------|--------------------|--------------------|
| | | | |
|------------------|----------------------|--------------------|--------------------|
продолжение финала:
|------------------|----------------------|--------------------|--------------------|
| catid5itemid | catid5item | catid5desc | catid5amount |
|------------------|----------------------|--------------------|--------------------|
|30,31 |room, chair |room, chair | 500, 150 |
|------------------|----------------------|--------------------|--------------------|
| | | | |
|------------------|----------------------|--------------------|--------------------|
|30,31 |room, chair |room, chair | 500, 150 |
|------------------|----------------------|--------------------|--------------------|
Я видел сообщения об использовании GROUP_CONCAT или CROSS APPLY или SWITCH STATEMENT
Например, GROUP_CONCAT:
select *, GROUP_CONCAT(`table`.`categoryid` ORDER BY `table`.`categoryid` ASC SEPARATOR ', ') AS `categoryid`
from `table`
GROUP BY `table`.`requestid`
но это объединяет все в столбец, не разделенный идентификатором категории, а все идентификаторы категорий вместе в одном столбце.
Любые предложения или помощь будут высоко оценены.
См. stackoverflow.com/questions/12004603/…
Извините, я очень новичок в кодировании, поэтому я пытался найти ответ на форумах, но ничего не подходит, поэтому, пожалуйста, проявите терпение к моему недостатку знаний. Есть ли способ использовать операторы IF в запросе в отношении IF categoryid = 1 THEN group_concat все, что имеет categoryid = 1 в пределах одного и того же requestid, должно быть разделено запятыми для столбцов, дескриптора и суммы?
Да, вы можете использовать if внутри group_concat, но если он должен отображаться в приложении, я предлагаю сделать это в коде приложения, как предложил @Strawberry. В любом случае я опубликую более полный ответ о том, как получить с помощью sql.
Ответы 2
Спасибо всем за помощь! я понял это без использования сводных таблиц
выберите table.requestid КАК request_id,
group_concat((случай когда (table.categoryid = 2) тогда table.item конец) разделитель ',') КАК item2
,group_concat((случай когда (table.categoryid = 2) то table.descriptor конец) разделитель ',') КАК descriptor2,
group_concat((случай когда (table.categoryid = 2) то table.amount конец) разделитель ',') КАК amount2,
group_concat((случай когда (table.categoryid = 3) то table.item конец) разделитель ',') КАК item3,
group_concat((случай когда (table.categoryid = 3) то table.descriptor конец) разделитель ',') КАК descriptor3,
group_concat((случай когда (table.categoryid = 3) тогда table.amount конец) разделитель ',') КАК amount3
group_concat((случай когда (table.categoryid = 4) то table.item конец) разделитель ',') КАК item4,
group_concat((случай когда (table.categoryid = 4) то table.descriptor конец) разделитель ',') КАК descriptor4,
group_concat((случай когда (table.categoryid = 4) то table.amount конец) разделитель ',') КАК amount4
group_concat((случай когда (table.categoryid = 5) то table.item конец) разделитель ',') КАК item5,
group_concat((случай когда (table.categoryid = 5) то table.descriptor конец) разделитель ',') КАК descriptor5,
group_concat((случай когда (table.categoryid = 5) тогда table.amount конец) разделитель ',') КАК amount5
от table
где ((table.categoryid = 2) или
(table.categoryid = 3) или
(table.categoryid = 4) или
(table.categoryid = 5))
группа от table.requestid
Если бы категории были фиксированными (3, 4 и 5), вы могли бы использовать этот запрос:
select
reqid,
group_concat(distinct if (catid = 3, itemid, null)) as catid3itemid,
group_concat(distinct if (catid = 3, item, null)) as catid3item,
group_concat(distinct if (catid = 3, `desc`, null)) as catid3desc,
group_concat(distinct if (catid = 3, amount, null)) as catid3amount ,
group_concat(distinct if (catid = 4, itemid, null)) as catid4itemid,
group_concat(distinct if (catid = 4, item, null)) as catid4item,
group_concat(distinct if (catid = 4, `desc`, null)) as catid4desc,
group_concat(distinct if (catid = 4, amount, null)) as catid4amount ,
group_concat(distinct if (catid = 5, itemid, null)) as catid5itemid,
group_concat(distinct if (catid = 5, item, null)) as catid5item,
group_concat(distinct if (catid = 5, `desc`, null)) as catid5desc,
group_concat(distinct if (catid = 5, amount, null)) as catid5amount
from tab
group by reqid;
Если он должен быть динамическим, можно использовать процедуру. Ниже я использовал повторяющуюся структуру для создания запроса, который возвращает желаемую структуру для каждого catid (от минимума до максимума) и выполняет его с подготовленный отчет:
-- only to avoid problem with only_full_group_by
set global sql_mode = "";
create table tab (reqid int, catid int, itemid int, item varchar(10), `desc` varchar(20), amount int);
insert into tab values
(1, 3, 16, 'food', 'food', 200),
(1, 3, 17, 'water', 'wtr', 50),
(1, 3, 18, 'film', 'film', 20),
(1, 5, 30, 'room', 'room', 500),
(1, 5, 31, 'chair', 'chair', 150),
(2, 3, 16, 'food', 'food', 200),
(2, 3, 17, 'water', 'wtr', 50),
(3, 3, 18, 'film', 'film', 20),
(3, 5, 30, 'room', 'room', 500),
(3, 5, 31, 'chair', 'chair', 150);
delimiter $$
CREATE PROCEDURE result()
BEGIN
DECLARE i INT DEFAULT (select min(catid) from tab);
DECLARE iEnd INT DEFAULT (select max(catid) from tab);
SET @sQuery = 'select reqid';
WHILE i <= iEnd DO
set @sQuery = CONCAT(@sQuery,
', group_concat(distinct if (catid = ',i,', itemid, null)) as catid',i,'itemid,
group_concat(distinct if (catid = ',i,', item, null)) as catid',i,'item,
group_concat(distinct if (catid = ',i,', `desc`, null)) as catid',i,'desc,
group_concat(distinct if (catid = ',i,', amount, null)) as catid',i,'amount'
);
SET i = i + 1;
END WHILE;
SET @sQuery = CONCAT(@sQuery, ' from tab group by reqid');
PREPARE stmt FROM @sQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
call result();
Примечание. Поскольку вы не предоставили слишком много деталей и, как уже было сказано в комментариях, возможно, было бы лучше выполнить логику отображения в приложении.
Вам нужно повернуть таблицу, чтобы получить каждую категорию в свой собственный набор столбцов.