SQL查询帮助

Question

我正忙得不可开交，所以我想我应该在这里请教一下。我有3张表，如下所示

设备表

EquipmentID | LocationID 
-------------------------
1           | 2
2           | 2
3           | 1
4           | 2
5           | 3
6           | 3

位置表

LocationID | LocationName
--------------------------
1          | Pizza Hut
2          | Giordanos
3          | Lou Malnati's

服务表

LocationID | EquipmentID | Status 
-----------------------------------
2          | 1           | Serviced
2          | 2           | Not Yet Serviced
2          | 4           | Not Yet Serviced
3          | 5           | Serviced

我需要一种方法来列出已经维修了一台或多台设备的所有位置，但并不是该位置的所有设备都已维修。

因此，对于上面的示例，它将返回以下结果

LocationID | ServicedEquipmentID  | NotServicedEquipmentIDS   | LocationStatus
------------------------------------------------------------------------------
2          | 1                    | 2, 4                      | Partially Serviced
3          | 5                    | 6                         | Partially Serviced

谢谢你的帮助！

Answer 1

此查询将为您提供所需的位置状态，但不是单个设备的状态：

SELECT  [LocationId]
       ,[LocationId]
       ,CASE ( [IsServiced] + [IsNotServiced] )
          WHEN 0 THEN 'Not Serviced'
          WHEN 1 THEN 'Partially Serviced'
          WHEN 2 THEN 'Serviced'
        END [LocationStatus]
FROM    ( SELECT    [l].[LocationId]
                   ,[e].[LocationId]
                   ,CASE [s].[Status]
                      WHEN 'Serviced' THEN 1
                      ELSE 0
                    END [IsServiced]
                   ,CASE [s].[Status]
                      WHEN 'Not Yet Serviced' THEN 1
                      ELSE 0
                    END [IsNotServiced]
          FROM      [Location] l
                    INNER JOIN [Equipment] e ON [l].[LocationId] = [e].[LocationId]
                    INNER JOIN [Service] s ON [l].[LocationId] = [s].[LocationId]
                                              AND [e].[EquipmentId] = [s].[EquipmentId]
        ) x

要向结果集添加以逗号分隔的已提供服务/未提供服务的equipmentIds列表，您将需要某种CONCAT函数。可以是UDF、CLR或递归CTE (我现在没有时间编写它-- here's a link)。