![语句中检索列表列表]( "语句中检索列表列表")
本文介绍了在一个 SQL 语句中检索列表列表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有两个表(比如)Person 和 Parent，父-人是多对一的关系，所以一个人可以有很多父母，直接的和间接的(祖父母等).Parent有外键personId，Person的主键当然是personId.
人表id<PK>父表Id<PK>ParentPersonId <FK into Person >人有值 PK 的行123父级具有带值的行1、21、32、3所以第 1 个人有父母 2、3我希望得到 List[ {1, {2,3}}, {2, {3}}, {3} ]
我正在使用 Spring Boot JDBC 查询 MS SQL 服务器数据库，我可以获得 personId 的所有父级，当然，我可以获得 Person 表中所有人员的列表.但是是否有可能在一个 SQL 语句中检索所有人员的列表，并在类 person 中检索一个人员 ID 列表，这些列表是与父表连接的结果?
或者我必须分两步完成.获取人员列表，然后在数据库中查询每个人的父母列表?
我正在尝试做这样的事情，但它说语法错误".
select ID as personId (select * from Parent where personId = parentPersonId) from Person
解决方案
假设你在 Parent 表中的数据不是递归的(这个表包含每个祖先的一行，直接或间接)，你可以使用一个简单的查询:
>
选择 per.id, par.parentPersonId从每个左的人加入父级在 per.id = par.id
如果您的数据是递归的(显然不是，因为 3 既是 1 的父代也是祖父代)，您需要使用递归 CTE 查询.请参阅我的后续问题中的代码 是否可以对递归 cte 查询进行分支修剪
I have two tables (say) Person and Parent with Parent-Person being many to one relationship, so a person can have many parents, direct and indirect (grandparents etc).Parent has foreign key personId and primary key of Person is of course, personId.
Person table
Id <PK>
Parent table
Id<PK>
ParentPersonId <FK into Person >
Person has rows with values PK
1
2
3
Parent has rows with values
1, 2
1, 3
2, 3
so person 1 has parents 2, 3
I eapect to get List<Person>
[ {1, {2,3}}, {2, {3}}, {3} ]
I am using Spring Boot JDBC to query a MS SQL server database and I can get all parents for personId and of course, I can get a list of all persons in Person table.But is it possible in one SQL statement to retrieve a list of all persons and within class person, a List of person Id which are the result of the join with Parent table?
Or do I have to do it in 2 steps. Get a list of persons, then query the database for the list of parents of each person?
I am trying to do something like this but it says 'syntax error'.
select ID as personId (select * from Parent where personId = parentPersonId) from Person
解决方案
Assuming your data in Parent table is not recursive (this table contains a row for each ancestor, direct or indirect), you can use a simple query:
select per.id, par.parentPersonId
from Person per left join Parent par
on per.id = par.id
If your data is recursive (apparently not, as 3 is both parent and grandparent for 1), you need to use a recursive-CTE query. See code in my follow-up question Is branch pruning possible for recursive cte query
这篇关于在一个 SQL 语句中检索列表列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！