sql-server - 加入 SQL Bridge 表

Question

我有以下表格

用户

+--------+------+
| UserID | Name |
+--------+------+
| User1  | John |
| User2  | Mike |
+--------+------+

设备

+----------+------------+------------+------+
| DeviceID | DeviceName | DeviceType | Good |
+----------+------------+------------+------+
|        1 | Device1    | A          |    0 |
|        2 | Device2    | A          |    1 |
|        3 | Device7    | B          |    0 |
|        4 | Device8    | B          |    1 |
|        5 | Device11   | C          |    0 |
|        6 | Device12   | C          |    1 |
+----------+------------+------------+------+

用户设备

 +--------------+--------+----------+
| UserDeviceID | UserID | DeviceID |
+--------------+--------+----------+
| z            | User1  |        1 |
| y            | User1  |        3 |
| x            | User1  |        5 |
| w            | User2  |        2 |
| v            | User2  |        4 |
| u            | User2  |        6 |
+--------------+--------+----------+

我想加入这些表格，如下所示

+----------+-------------+-------------+-------------+
| UserName | DeviceTypeA | DeviceTypeB | DeviceTypeC |
+----------+-------------+-------------+-------------+
| User1    | Device1     | Device7     | Device11    |
| User2    | Device2     | Device8     | Device22    |
+----------+-------------+-------------+-------------+

我尝试了所有类型的连接查询，但不幸的是，我无法获取上述格式的数据。有人可以帮忙吗？

标签： sql-serverdatabase-designazure-sql-database

Answer 1

您可以使用PIVOT（文档）执行此操作

SELECT P.[Name], P.A AS DeviceTypeA, P.B AS DeviceTypeB, P.C AS DeviceTypeC
   FROM
      (
         SELECT U.[Name], D.DeviceName, D.DeviceType
            FROM [User] U
            INNER JOIN DeviceUser DU
               ON DU.UserID = U.UserID
            INNER JOIN Device D
               ON D.DeviceID = DU.DeviceID
      ) AS X
      PIVOT
      (
         MAX(X.DeviceName)
         FOR X.DeviceType IN([A], [B], [C])
      ) AS P;

但是请注意，即使该用户有更多设备类型的设备，这也只会为某个用户和设备类型返回一个设备名称。