как использовать Sql join слева с 2 таблицами, но table2 имеет отношение многие к одному

ProgramBox

как использовать Sql join слева с 2 таблицами, но table2 имеет отношение многие к одному

Post author:admin
Запись опубликована:16 декабря, 2022
Post category:Вопросы по программированию

#c# #mysql #asp.net

Вопрос:

У меня есть 2 таблицы в моей БД, и я хочу объединить их вместе, но поскольку когда-то у меня было более одной записи в таблице 2, которые подключаются к table1, я хочу показать их в новом столбце. у меня есть приложение в .net C #, которое отправляет запрос и считывает его.

Table1:

ID	Имя
1	Джеймс
2	Джейн
3	Gil

Table2:

ID	table1_id	посещенные страны
1	1	США
2	1	Германия
3	2	Франция

и я хочу, чтобы конечный результат запроса был :

ID	Имя	Страна	Страна	….
1	Джеймс	США	Германия	….
2	Джейн	Франция	здесь нет столбца
3	Gil	больше столбцов нет

мой вопрос: возможно ли сделать так, чтобы строки имели разные столбцы? и если это невозможно, тогда все строки имеют максимальное количество столбцов country (измененных на максимальное количество стран, приведенных к одному ключу) и имеют значение null в своем значении.

пример:

ID	Имя	Страна	Страна	….
1	Джеймс	США	Германия	….
2	Джейн	Франция	null	….
3	Gil	null	null	….

…. представляет, было ли у меня больше стран для одного ключа.

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

1. Сколько столбцов вам нужно, что произойдет, если кто-то побывал в 100 странах?

2. если возможно, только строка со 100 странами будет содержать все 100 столбцов. таким образом, каждая строка имеет разное количество столбцов страны в зависимости от того, сколько стран посещено в таблице 2 с этим идентификатором. если это невозможно, то вся строка будет иметь столбец 100 country, но все дополнительные столбцы для каждой строки будут иметь значение null. спасибо за комментарий

3. Итак, каково ваше максимально возможное количество столбцов? 100?

4. это проблема, если я не хочу ее ограничивать? опять же, если он создает столбцы на основе количества стран, которые имеет идентификатор, я не возражаю, если его 2, 4 или даже 50. причина, по которой размер столбца имеет значение для меня, заключается в том, что я намерен использовать в приложении в программе чтения команд sqlcommand цикл с reader.fieldCount, чтобы узнать, сколько стран, если таковые имеются, есть в этой строке, и оттуда вставить его в объект

5. Это намного проще, если у вас фиксированное количество столбцов. Если для данной строки их не так много, в этих дополнительных столбцах будут просто нули.

Ответ №1:

Вам нужно повернуть на rownumber:

 SELECT
    id,
    name,
    MIN(CASE WHEN rn = 1 THEN countries_visited END) country1,
    MIN(CASE WHEN rn = 2 THEN countries_visited END) country2,
    MIN(CASE WHEN rn = 3 THEN countries_visited END) country3,
    ......... etc copy paste as many as you need
FROM (
    SELECT t1.*, t2.countries_visited,
        ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.countries_visited) AS rn
    FROM Table1 t1
    JOIN Table2 t2 ON t2.table1_id = t1.id
) t
GROUP BY id, name;

Вы также можете сделать это с PIVOT помощью ключевого слова, но это менее гибко.

1. прежде всего, спасибо за быстрый ответ, я допустил ошибку в некоторых строках. я сохраняю t1 и t2 и меняю только Table1 и Table2 на мои настоящие имена таблиц?

2. Хорошо, мне удалось заставить его работать, но результат не тот, который мне нужен, я получаю: (id-name-country1-country2) \ 1-James-Usa-null , 2-Jane-Germany-null, 3-Gil-France-null . но Германия должна быть страной Джеймса 2, Франция должна быть страной Джейн1, а у gil нет страны — оба null. и мой другой вопрос все еще остается в силе — есть ли способ, чтобы, когда значение colmun равно нулю, оно даже не отображалось, поэтому у Джеймса будут оба столбца, у Джейн будет только столбец country1, а у Gil будут только столбцы id и name и вообще никакой страны?

3. Я не понимаю, как вы ожидаете иметь одну таблицу с разными столбцами в каждой строке? Это просто не так, как работает SQL. Если вы не хотите, чтобы слово NULL отображалось, возможно, измените значение pivot на MIN(CASE WHEN rn = 1 THEN countries_visited ELSE '' END) тогда вы получите пустую строку

4. вот в чем я не был уверен, спасибо за разъяснение. итак, у меня есть два вопроса. 1. я использовал код, который вы написали, и он не сработал на 100%. У Джеймса не было обеих стран, только США, и по какой-то причине Джейн получила Германию (вторая страна Джеймса), а Гил получил Францию (у него нет ни одной страны в table2),, 2.is возможно, что количество создаваемых столбцов будет динамичным и будет зависеть от максимального количества стран в любой моментИдентификатор получен? т.е. Джеймс получил 2 страны, поэтому максимальное количество столбцов равно 2, если в будущем у Гила будет 3 строки, потому что он включил 3 страны, так что теперь новый максимум будет 3

5. Извините, я неправильно понял условие соединения, исправил