如何使用 Sql join left 2 个表但 table2 具有多对一关系 [英] how to use Sql join left with 2 tables but table2 has many-to-one relation
问题描述
我的数据库中有 2 个表,我想将它们连接在一起,但因为有时我在表 2 中有多个记录连接到 table1,我想在新列中显示它们.我在 .net C# 中有应用程序,可以发送查询并从中读取.
I got 2 tables in my db and i want to join them together but because sometime i got more than one record in table 2 that connect to table1 i want to show them in a new column . i have app in .net C# that send the query and read from it.
表 1:
id | 姓名 |
---|---|
1 | 詹姆斯 |
2 | 简 |
3 | 吉尔 |
表 2:
id | table1_id | countries_visited |
---|---|---|
1 | 1 | 美国 |
2 | 1 | 德国 |
3 | 2 | 法国 |
我希望查询的最终结果是:
and i want the end result from the query to be :
id | 姓名 | 国家 | 国家 | .... |
---|---|---|---|---|
1 | 詹姆斯 | 美国 | 德国 | .... |
2 | 简 | 法国 | 这里没有列 | |
3 | 吉尔 | 没有更多的列 |
我的问题是可以让行有不同的列吗?如果它不可能,那么所有行都具有最大国家/地区列(通过访问单个键的最大国家/地区数量更改)并且它们的值具有 null.
my question is it possible to make rows have different columns? and if its not possible than have all rows max country colums ( changed by the max amount of country visted to single key ) and have null in their value.
例如:
id | 姓名 | 国家 | 国家 | .... |
---|---|---|---|---|
1 | 詹姆斯 | 美国 | 德国 | .... |
2 | 简 | 法国 | 空 | .... |
3 | 吉尔 | 空 | 空 | .... |
.... 代表我是否有更多的国家/地区到一个键.
the .... represents if i had more countries to a single key.
也许我都弄错了,但我想要的原因是我可以让一些用户没有访问过的国家/地区,而另一些用户则可以不受限制地访问一个或多个国家/地区.
maybe i got it all wrong but the reson i want this is because i can have some users with no countries visited and some with one or more without limiting .
推荐答案
您需要对 rownumber 进行透视:
You need to pivot on 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
关键字执行此操作,但灵活性较差.
You can also do this with the PIVOT
keyword, but that is less flexible.
这篇关于如何使用 Sql join left 2 个表但 table2 具有多对一关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!