SQL Server 2000 - 如何在查询的最终结果中轮换联接结果？

发布于 2024-10-11 16:15:13 字数 845 浏览 4 评论 0原文

我的数据库非常复杂，因此我将问题简化为下表。

TableA 和 TableB 通过 TableB 中的 NameID 字段关联。我正在尝试创建一个 SQL 语句来产生所需的结果。我了解 JOIN 及其工作原理，但我无法弄清楚这一点。

对于 TableA 中的每个项目，TableB 中的项目永远不会超过 2 个。项目数量可能少于 2 个。

这将在 SQL Server 2000 服务器上使用。

表A

ID | Name
---+-----
 1 | John
 2 | Jane
 3 | Bob
 4 | Doug

表B

ID | NameID | Information
---+--------+------------
 1 |    1   | Apples
 2 |    1   | Apples
 3 |    2   | Pears
 4 |    2   | Grapes
 5 |    3   | Kiwi

期望结果

ID | Name | InformationA | InformationB
---+------+--------------+-------------
 1 | John | Apples       | Apples
 2 | Jane | Pears        | Grapes
 3 | Bob  | Kiwi         | NULL
 4 | Doug | NULL         | NULL

原文

My database is quite complex so I've simplified my problem down to the tables below.

TableA and TableB are related by the NameID field in TableB. I am trying to create a SQL statement to produce the desired results. I'm understand JOINs and how they work but I can't fogure this out.

There will never be more than 2 items in TableB for each item in TableA. There could be less than 2 items.

This will be used on a SQL Server 2000 server.

TableA

ID | Name
---+-----
 1 | John
 2 | Jane
 3 | Bob
 4 | Doug

TableB

ID | NameID | Information
---+--------+------------
 1 |    1   | Apples
 2 |    1   | Apples
 3 |    2   | Pears
 4 |    2   | Grapes
 5 |    3   | Kiwi

Desired Result

ID | Name | InformationA | InformationB
---+------+--------------+-------------
 1 | John | Apples       | Apples
 2 | Jane | Pears        | Grapes
 3 | Bob  | Kiwi         | NULL
 4 | Doug | NULL         | NULL

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

木森分化 2024-10-18 16:15:13

（编辑以给出两列的首选顺序）

SELECT a.Id,
       a.Name,
       STUFF(MIN(STR(b.Id, 10) + b.Information), 1, 10, '') AS InformationA,
       CASE
         WHEN COUNT(b.Id) = 2 THEN STUFF(MAX(STR(b.Id, 10) +
                                   b.Information), 1, 10, '')
       END                                                  AS InformationB
FROM   TableA a
       LEFT JOIN TableB b
         ON a.Id = b.NameId
GROUP  BY a.Id,
          a.Name

(Edited to give the preferred ordering for the two columns)

SELECT a.Id,
       a.Name,
       STUFF(MIN(STR(b.Id, 10) + b.Information), 1, 10, '') AS InformationA,
       CASE
         WHEN COUNT(b.Id) = 2 THEN STUFF(MAX(STR(b.Id, 10) +
                                   b.Information), 1, 10, '')
       END                                                  AS InformationB
FROM   TableA a
       LEFT JOIN TableB b
         ON a.Id = b.NameId
GROUP  BY a.Id,
          a.Name

回复收藏 0 原文