我有一个包含BedTypes和RoomID列的表,如下所示.
BedType | RoomId | FirstName |
---|---|---|
Double | 4 | Andrew |
Double | 4 | Janet |
Double | 5 | Anne |
Double | 5 | Robert |
Double | 7 | Michael |
Double | 7 | Steven |
Triple | 8 | Howard |
Triple | 8 | John |
Triple | 8 | Carlos |
Triple | 9 | Jaime |
Triple | 9 | Giovanni |
Triple | 9 | Rene |
Double | 10 | Alejandra |
Double | 10 | Pascale |
Single | 11 | Paolo |
我收到了这个表的查询如下;
SELECT B.Name 'BedType',Ro.Id'RoomId',C.FirstName From Customers C
JOIN Rooms Ro ON Ro.Id = C.RoomId
JOIN BedTypes B ON B.Id = Ro.BedTypeId
ORDER BY Ro.ReservationId
我无法解决的问题是,我想做一个"基于字符串的专栏".如果房间的床类型是Double,我只需要一个Double Cell,第二个Customer的BedType单元格将为空,如下表所示.
BedType | RoomId | FirstName |
---|---|---|
Double | 4 | Andrew |
4 | Janet | |
Double | 5 | Anne |
5 | Robert | |
Double | 7 | Michael |
7 | Steven | |
Triple | 8 | Howard |
8 | John | |
8 | Carlos | |
Triple | 9 | Jaime |
9 | Giovanni | |
9 | Rene | |
Double | 10 | Alejandra |
10 | Pascale | |
Single | 11 | Paolo |
我try 编写一个查询,如下所示,但到目前为止我无法成功:
SELECT
(
CASE WHEN B.Id =1 THEN (SELECT Ro.Id )
WHEN B.Id =3 THEN NULL
WHEN B.Id = 4 THEN Null
END ) AS Trial
,B.Name 'BedType',Ro.Id'RoomId',C.FirstName From Customers C
JOIN Rooms Ro ON Ro.Id = C.RoomId
JOIN BedTypes B ON B.Id = Ro.BedTypeId
ORDER BY Ro.ReservationId