SQL Server 查询返回多行结果，而不是返回 1 行

看起来你真的不熟悉使用 SQL，所以让我们从一些基础知识开始。

DECLARE @Jobs TABLE (JobID INT IDENTITY, CompanyName NVARCHAR(50), CompanyID INT);
DECLARE @Users TABLE (UserID INT IDENTITY, FirstName NVARCHAR(50), LastName NVARCHAR(50), JobID INT);

INSERT INTO @Jobs (CompanyName, CompanyID) VALUES ('Home Depot', 123), ('Home Depot', 123), ('Home Depot', 123);
INSERT INTO @Users (FirstName, LastName, JobID) VALUES ('Michael', 'Mark', 1), ('Jon', 'Smith', 2), ('Louise', 'Manfield', 3);

这些是表变量，定义了两个虚拟对象，我们可以用它来做一些演示。在大多数情况下，我们可以像使用实际的表一样使用它们。

首先，我们只获取所有用户：

SELECT UserID, FirstName, LastName, JobID
  FROM @Users;

我们可以简单地通过语句中的列名来引用表中的列。现在，我们想知道哪些用户属于哪个工作。我们知道这种关系是由 and 表上的列定义的。我们还使用 （u 和 j） 来引用这些表，因为它们可以（并且确实）具有匹配的列名SELECTJobIDJobsUsersALIAS

SELECT u.UserID, u.FirstName, u.LastName, u.JobID, j.CompanyID, j.CompanyName
  FROM @Users u
    INNER JOIN @Jobs j
      ON u.JobID = j.JobID 

现在我们已经掌握了基础知识，我们可以解决您的实际问题。您需要为每家公司工作的人员列表，但要放在一行。

您列出了 SQL-Server 2005，我真的希望这是一个错误。在较新的版本中，有很多方法可以做到这一点。

如果您有已知数量的位置可以执行此操作，您可以只将表格联接三次，强制执行您每次想要如何关联它们：

SELECT DISTINCT CompanyID, u1.FirstName, u1.LastName, u2.FirstName, u2.LastName, u3.FirstName, u3.LastName
  FROM @Jobs J
    INNER JOIN @Users u1
      ON u1.UserID = 1
    INNER JOIN @Users u2
      ON u2.UserID = 2
    INNER JOIN @Users u3
      ON u3.UserID = 3

当你编写一个查询时，你通常会在集合中工作。在本例中，您请求 jobs 表的所有行，这就是您得到的回报。任何查询的开头都可能是这样的列表，然后您分支以获取具有其他信息的其他对象，但每个对象将构成一行，直到您开始执行一些聚合。在本例中，您需要聚合一个字符串。评论中的一些人提到了这个功能，但你离 2005 年还有很长的路要走。STRING_AGG

可以肯定的是，条件聚合就是你在这里所追求的。此外，我还大大简化了所有这些子查询，使其为大小写表达式。这应该会产生你要找的东西。

SELECT
    c.code,
    c.cName,
    ac.cName,
    [User 1] = max(case when a.pos = 1 then u.fname end),
    [Date Assigned 1] = max(case when a.pos = 1 then a.dateOn end),
    [User 2] = max(case when a.pos = 2 then u.fname end),
    [Date Assigned 2] = max(case when a.pos = 2 then a.dateOn end),
    [User 3] = max(case when a.pos = 3 then u.fname end),
    [Date Assigned 3] = max(case when a.pos = 3 then a.dateOn end)
FROM clients c
INNER JOIN assignments a ON a.clientID = c.clientID
INNER JOIN assignmentCarriers ac ON ac.acID = a.acID
join Users u on u.userID = a.userID
WHERE 
    isAssignment = 'True' 
    AND c.active = 'True'
group by c.code,
    c.cName,
    ac.cName,
ORDER BY 
    c.cName

您的编辑确实对问题进行了一些说明，但并没有澄清所有内容。我做了一些非常广泛的猜测和假设，试图把它带到最低阈值以上，所以我希望能帮助你掌握这里的一些基础知识。

首先，快速说明一下提供 DDL/DML - 当您提出此类问题时，这一点非常重要，因为在不知道数据是什么以及它如何存储的情况下，我们从一开始就陷入困境。

为此，我更喜欢使用表变量，因为它们是可移植的，并且几乎不需要事后清理。根据您的描述和您提供的伪装数据，我认为这是一个合理的猜测，因为您的 DDL/DML 实际上是什么。

DECLARE @Assignments TABLE (aID INT IDENTITY, acID INT, clientID INT, userID INT, pos INT, dateOn DATE);
DECLARE @AssignmentCarriers TABLE (acID INT IDENTITY, clientID INT, cName NVARCHAR(50), isAssignment BIT);
DECLARE @Users TABLE (UserID INT IDENTITY, fName NVARCHAR(50));
DECLARE @Clients TABLE (clientID INT IDENTITY, cName NVARCHAR(50), code NVARCHAR(10), active BIT);

INSERT INTO @Assignments (acID, clientID, userID, pos, dateOn) VALUES (1, 1, 1, 1, '2023-04-01'),  (2, 1, 1, 2, '2015-01-22'),  (1, 1, 2, 3, '2000-05-12'),  (2, 1, 3, 4, '2012-12-11'),  (1, 1, 4, 5, '2022-02-12'),  (2, 1, 5, 5, '2022-02-12');
INSERT INTO @AssignmentCarriers (clientID, cName, isAssignment) VALUES (1, 'R+L Domestic', 1), (2, 'R+L INTL', 1);
INSERT INTO @Users (fName) VALUES ('Phil Brown'), ('Mark Twain'), ('Jen Gump'), ('Rob Mills'), ('John Smith');
INSERT INTO @Clients (cName, code, active) VALUES ('Home Depot', 'HDPT', 1), ('Home Depot', 'HDPT', 1);

首先，我定义了表及其列和数据类型，然后在其中插入了代表您提供给我们的输出的行。这只是最好的猜测。

现在，我们有一些东西可以构建查询。

我不确定你为什么如此坚持尝试在你的选择中使用内联子查询。这从一开始就是一个非常糟糕的主意，应该暗示你正在做一些愚蠢的事情。据我所知，甚至没有真正的理由考虑需要这样做。

你很难概念化你想要的结果集的第一个原因可能是因为它是一个反模式。RDMS系统成组运行，并且强烈支持正常形式。每当您开始偏离这一点时，它就会变得具有挑战性，尤其是对于较旧的发动机。

从本质上讲，我们在这里有两个问题需要解决，然后将这些解决方案结合在一起以满足您的需求。第一种是获取分配、运营商和客户端的基本结果集。然后，我们想要一组属于这些用户的三个用户，但作为列，而不是行。

我们将使用 （） 来做到这一点，以保持两个部分周围的卡路里感和秩序感，然后将它们组合在一起。第一个将客户、分配和承运人聚合到两个不同的行。第二个方法为每个用户查找前三个用户（按 userID 顺序），然后用于检索每个用户的 fName 和 dateOn，并将它们作为列返回。COMMON TABLE EXPRESSIONSCTECTEPIVOT

;WITH assignments AS (
SELECT c.clientID, c.cname, code, ac.cName AS acName, ac.acID
  FROM @Clients c
    INNER JOIN @Assignments a
      ON c.clientID = a.clientID
    INNER JOIN @AssignmentCarriers ac
      ON a.acID = ac.acID
 WHERE c.active = 1
 GROUP BY c.clientID, c.cname, code, ac.cName, ac.acID
), ThreeAssignedUsers AS (
SELECT acID, MAX([1]) AS fName1, MAX([11]) AS dateOn1, MAX([2]) AS fName2, MAX([12]) AS dateOn2, MAX([3]) AS fName3, MAX([13]) AS dateOn3
  FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY ac.acID ORDER BY u.UserID) AS rn1, 10+ROW_NUMBER() OVER (PARTITION BY ac.acID ORDER BY u.UserID) AS rn2, fName, dateOn, ac.acID
          FROM @Assignments a
          INNER JOIN @AssignmentCarriers ac
            ON a.acID = ac.acID
          LEFT OUTER JOIN @Users u
            ON a.userID = u.UserID
         WHERE ac.isAssignment = 1
       ) a
    PIVOT (
           MAX(fName) for rn1 IN ([1],[2],[3])
          ) p
    PIVOT (
           MAX(dateOn) for rn2 IN ([11],[12],[13])
          ) p2
 GROUP BY acID
)

SELECT *
  FROM assignments a
    INNER JOIN ThreeAssignedUsers tau
      ON a.acID = tau.acID;

您会注意到，在这里，我们必须使用硬编码的列名进行两个透视。在枢轴中只能使用一列一次，因此 rn1 和 rn2 基本上是相同的，只是每个枢轴重复。我在 rn2 中添加了静态 10 只是为了区分它。

最后，查询这两个 s，并返回要查找的结果集。CTE

尝试使用它时可能会遇到许多限制和很可能的性能影响。其中最不重要的一点是硬编码的列名和列数。您需要手动扩展这两个透视表，以便能够向结果集添加其他用户列，如果要添加任何其他列（如姓氏或其他内容），则需要添加其他透视表和支持列。

当您必须努力让 RDBM 生成结果时，可能是时候重新考虑您的方法并可能考虑使用其他工具来返回数据了 - 在生成报告时有很多选择（这可能是您正在尝试的？）为您提供更好的选择来操作数据到您想要的格式和布局。