SQL Server:一对多选择查询[重复]

时间:2022-04-17 23:29:52

This question already has an answer here:

这个问题在这里已有答案:

I have two tables, logically related one to many.

我有两个表,逻辑上相关的一对多。

First table:

第一张表:

CREATE TABLE Persons 
(
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255), 
    Age int
);

Second table:

第二表:

CREATE TABLE Vehicles 
(
    Brand varchar(50), 
    PersonID int,

    FOREIGN KEY(PersonID) REFERENCES Persons(ID)
);

My approach is to list each of the Persons and the vehicles that this Person own.

我的方法是列出每个人和这个人拥有的车辆。

What I managed to do:

我做了什么:

SELECT LastName, brand 
FROM vehicles
INNER JOIN Persons ON Persons.ID = PersonID
ORDER BY LastName ASC

Unfortunately this is not what I have in mind to do, which is to display every Person once and next to it a list of vehicles for ex.:

不幸的是,这不是我想要做的,这是为了显示每个人一次和旁边的车辆清单:

User1 | kia, ford, jeep
User2 | ferrari, harley

Is it doable and if so what is the appropriate way? Any help is welcome!

它是否可行,如果是,那么适当的方式是什么?欢迎任何帮助!

2 个解决方案

#1


3  

You can use FOR XML with SUFF for this:

您可以将FOR XML与SUFF一起使用:

SELECT FirstName, LastName,
STUFF((
    SELECT ', ' + v.Brand
    FROM vehicles as v
    WHERE v.PersonID = p.ID
    FOR XML PATH (''))
  ,1,2,'') AS Vehicles
FROM Persons AS p;

Results:

结果:

| FirstName | LastName |        Vehicles |
|-----------|----------|-----------------|
|     User1 |    user1 | Ford, kia, jeep |
|     User2 |    User2 |  ferrri, harley |

#2


1  

The most recent version of SQL Server (SQL Server 2017) finally has this functionality built-in. So you can do:

最新版本的SQL Server(SQL Server 2017)最终内置了此功能。所以你可以这样做:

SELECT p.LastName,
       STRING_AGG(v.brand, ', ') WITHIN GROUP (ORDER BY v.brand) as vehicles
FROM Persons p JOIN
     vehicles v
     ON p.ID = v.PersonID
GROUP BY p.ID, p.LastName;

When you have a query with more than one table, I would advise you to always use table aliases and qualified column names.

如果您有一个包含多个表的查询,我建议您始终使用表别名和限定列名。

If you want people without vehicles to be included, then use a LEFT JOIN.

如果您希望包含没有车辆的人,请使用LEFT JOIN。

#1


3  

You can use FOR XML with SUFF for this:

您可以将FOR XML与SUFF一起使用:

SELECT FirstName, LastName,
STUFF((
    SELECT ', ' + v.Brand
    FROM vehicles as v
    WHERE v.PersonID = p.ID
    FOR XML PATH (''))
  ,1,2,'') AS Vehicles
FROM Persons AS p;

Results:

结果:

| FirstName | LastName |        Vehicles |
|-----------|----------|-----------------|
|     User1 |    user1 | Ford, kia, jeep |
|     User2 |    User2 |  ferrri, harley |

#2


1  

The most recent version of SQL Server (SQL Server 2017) finally has this functionality built-in. So you can do:

最新版本的SQL Server(SQL Server 2017)最终内置了此功能。所以你可以这样做:

SELECT p.LastName,
       STRING_AGG(v.brand, ', ') WITHIN GROUP (ORDER BY v.brand) as vehicles
FROM Persons p JOIN
     vehicles v
     ON p.ID = v.PersonID
GROUP BY p.ID, p.LastName;

When you have a query with more than one table, I would advise you to always use table aliases and qualified column names.

如果您有一个包含多个表的查询,我建议您始终使用表别名和限定列名。

If you want people without vehicles to be included, then use a LEFT JOIN.

如果您希望包含没有车辆的人,请使用LEFT JOIN。