Используйте SQLite SELECT, чтобы объединить столбец объединенной таблицы в одну строку для каждой категории, используя соединительную таблицу.

Я использую SQLite и имею три таблицы (в этих таблицах больше данных - сокращенно здесь):

  • Категории – содержит категории элементов (идентификатор, название, описание).
  • Элементы – (идентификатор, название, статус)
  • Ссылка (id, cat_id, item_id)

Категории могут иметь много элементов, связанных с ним, или ни одного. Элементы должны иметь как минимум 1 категорию и могут быть более чем в 1 категории.

Пример:

(Categories)
| id | name   | description                     |
|----|--------|---------------------------------|
| 1. | wet.   | something associated with water |
| 2. | dry.   | something else                  |
| 3. | metal. | steel, copper                   |

(Items)
| id. | name.   | status     |
|-----|---------|------------|
| 11. | river.  | north fork |
| 12. | lake.   | big        |
| 13. | river.  | south fork |
| 14. | desert. | mojave     |
| 15. | car.    | ford       |
| 16. | truck.  | chevy      |

(Reference)
| id | cat_id. | item_id |
|----|---------|---------|
| 21 | 1       | 11      |
| 22 | 1       | 12      |
| 23 | 2       | 14      |
| 24 | 3       | 15      |
| 25 | 3       | 16      |

Используя следующее:

SELECT c.name,(i.name || "-" || i.status) as Related from Items as i
join Categories c where c.id = cat.id

Я получаю что-то вроде этого:

| c.name  | Related            |
|---------|--------------------|
| wet     | river - north fork |
| wet     | lake - big         |
| wet     | river - south fork |
| dry     | desert - mojave    |
| metal   | car - ford         |
| metal   | truck - chevy      |

Мне нужно

| c.name | Related                                            |
|--------|----------------------------------------------------|
| wet    | river - north fork, lake - big, river - south fork |
| dry    | desert - mojave                                    |
| metal  | car - ford, truck - chevy                          |

Одна категория соответствует элементам, на которые есть ссылки в соединительной таблице, и объединяется в столбце «Связанные» (разделенные запятой в примере).

Как мне получить этот результат в SQLite?


sql sqlite select group-concat junction-table
person Vento    schedule 29.12.2020    source источник

Ответы (4)


arrow_upward
1
arrow_downward

Вы должны сначала присоединить Categories к Reference, а затем к Items с помощью LEFT присоединения, на тот случай, если категория не имеет связанных элементов, а затем агрегировать с GROUP_CONCAT():

SELECT c.name,
       GROUP_CONCAT(i.name || ' - ' || i.status, ', ') Related
FROM Categories c
LEFT JOIN Reference r ON r.cat_id = c.id
LEFT JOIN Items i ON i.id = r.item_id
GROUP BY c.id, c.name

См. демонстрацию.
Результаты:

| name  | Related                        |
| ----- | ------------------------------ |
| wet   | river - north fork, lake - big |
| dry   | desert - mojave                |
| metal | car - ford, truck - chevy      |
person forpas    schedule 29.12.2020

arrow_upward
1
arrow_downward

Вы можете написать запрос следующим образом:

select c.name,
       group_concat(i.name || '-' || i.status, ', ') as Related
from Items i join
     Categories c 
     on c.id = i.cat_id
group by c.name;

Примечания:

  • JOIN следует всегда использовать ON для условий JOIN.
  • Используйте одинарные кавычки для строк в SQL.
  • Разделитель по умолчанию — ','. Кажется, вы хотите ', ', поэтому вам нужен второй аргумент.
  • Вам нужно GROUP BY.
person Gordon Linoff    schedule 29.12.2020
comment
Спасибо за ваш пост и быстрый ответ. Я думаю, что все еще отсутствует часть - из элементов, которые я присоединяю к категориям c на c.id = cat.id - cat.id не определен в ответе и должен исходить из справочной таблицы. Другими словами, мне нужна ассоциация со справочной таблицей, чтобы найти все элементы, связанные с категорией. - person Vento; 29.12.2020
comment
@Венто. . . Это было ваше изначальное состояние. Это также не должно работать в вашем запросе. Я исправил ответ. - person Gordon Linoff; 29.12.2020
comment
Да, ты прав, Гордон, это точно было моим недостатком. Я вижу ваш ответ и очень ценю помощь и обратную связь. - person Vento; 04.01.2021

arrow_upward
1
arrow_downward

Вы можете использовать group_concat следующим образом:

SELECT c.name, group_concat (i.name || "-" || i.status) as Related from Items as i
join Categories c on c.id = cat.id
group by c.name
person Popeye    schedule 29.12.2020
comment
Благодарю за ваш ответ. Проблема, с которой я сталкиваюсь, заключается в том, что таблицы категорий и элементов имеют только таблицу ссылок, чтобы связать их (многие со многими). Мне нужно что-то вроде Foreach Category => искать все элементы в справочной таблице и возвращать все элементы в одном столбце. Каждая категория в отдельной строке показывает все элементы, связанные на основе справочной таблицы. - person Vento; 29.12.2020
comment
Да, обновленный ответ принесет желаемый результат. - person Popeye; 29.12.2020
comment
Спасибо Попай. Это помогает, но мне нужна была ссылка из таблицы соединений, чтобы собрать их всех в одну строку (см. ответ Гордона). Я надеюсь, что я все еще не упускаю из виду вашу мысль, потому что я действительно ценю ответ и не хочу сбрасывать со счетов предложение, если я все еще не вижу его. Рад, что есть так много хороших людей, готовых помочь. - person Vento; 30.12.2020

arrow_upward
0
arrow_downward

Ответ от forpas был именно тем, что мне было нужно. Спасибо -

SELECT c.name,
  GROUP_CONCAT(i.name || ' - ' || i.status, ', ') Related
FROM Categories c
LEFT JOIN Reference r ON r.cat_id = c.id
LEFT JOIN Items i ON i.id = r.item_id
GROUP BY c.id, c.name
person Vento    schedule 04.01.2021