T:使用case语句进行分区

Question

我有下表：

| RoomID | OrderID | Occupancy | Status        |
+--------+---------+-----------+---------------+
| 01     | 101     | Vacant    | inspection    |
| 01     | 102     | Occupied  | Preservation  |
| 01     | 103     | Occupied  | inspection    |
| 01     | 104     | Vacant    | inspection    |
| 02     | 201     | Vacant    | inspection    |
| 02     | 202     | Occupied  | inspection    |
| 02     | 203     | Vacant    | inspection    |
| 03     | 301     | Vacant    | inspection    |
| 03     | 302     | Occupied  | inspection    |
| 03     | 303     | Occupied  | Preservation  |
| 03     | 304     | Occupied  | Preservation  |
| 04     | 401     | Occupied  | inspection    |
| 04     | 402     | Occupied  | inspection    |
| 04     | 403     | Vacant    | Preservation  |
| 04     | 404     | Occupied  | inspection    |

我需要在RoomID级别上提取数据，在给定RoomID的任何实例中，Occupancy =‘占’和Status =‘保存’。

结果应该如下所示：

| RoomID | Flag    |
+--------+---------+
| 01     | 1       |
| 02     | 0       |
| 03     | 1       |
| 04     | 0       |

我有一个印象，这是容易的，但我现在看不到它，感谢您的帮助提前！

Answer 1

您可以使用条件聚合。

select roomid,
count(distinct case when Occupancy = 'Occupied' and Status = 'Preservation' then 1 end) flag
from tablename
group by roomid

Answer 2

还可以使用UNION使用以下查询。

;with cte_1
 AS
( SELECT DISTINCT RoomId
  FROM YourTable
  WHERE Occupancy='Occupied' AND Status='Predervation')
  SELECT RoomId,1 Status
  FROM cte_1
  UNON
  SELECT DISTINCT RoomId,0 Status
   FROM YourTable t
   WHERE NOT EXISTS(SELECT 1 FROM cte_1 c
               WHERE t.RoomId=c.RoomId)