连接这两个表以获得这个特定的输出？

6jjcrrmo 于 2021-08-09 发布在 Java

关注(0)|答案(3)|浏览(304)

我正在使用sql server 2014。我正在处理一个查询，我想从两个特定的表中提取信息来创建最终的输出。
两个表的摘录( Rebookings 以及 ResaList )如下所示。
rebookings表（每个CanceleDid都有相应的rebookingid）：

CancelledID            RebookingID
  102                     541
  250                     351
  129                     800
  ...

列表：

ID      Property         ArrivalDate       RN
100       X              2020-05-22         9
102       X              2020-03-05         7
250       D              2020-04-12        10
129       E              2020-03-15         8
351       D              2020-09-23         5
541       X              2020-06-01         7
800       E              2020-07-11         8
...

这是我想要的结果：

ID       Property        ArrivalDate         RN    RebookingID        Rebooking_ArrivalDate    Rebooking_RN     Tag
102         X            2020-03-05           7       541              2020-06-01                   7           Cancelled            
250         D            2020-04-12          10       351              2020-09-23                   5           Re-booked           
129         E            2020-03-15           8       800              2020-07-11                   8           Re-booked

这就是我目前所做的：

USE [MyDatabase]

select    
  a.[ID],
  a.[Property],
  a.[Arrival Date],
  a.[RN],
  b.[RebookingID],
  (CASE WHEN a.[ID] in (SELECT [CancelledID] FROM [Rebookings]) THEN 'Re-booked'
      ELSE 'Cancelled'
      END) as [Tag]    
from [ResaList] a    
LEFT JOIN [Rebookings] b on b.[CancelledID] = a.[ID]       
where a.[ID] in (SELECT [CancelledID] FROM [Rebookings])        
GROUP BY a.[ID], a.[Property], a.[ArrivalDate], b.[RebookingID]

我被困在如何带来 Rebooking_ArrivalDate 以及 Rebooking_RN 输入上述输出。任何帮助都将不胜感激。

sql sql-server tsql

来源：https://stackoverflow.com/questions/62148042/join-these-2-tables-to-get-this-specific-output

3条答案

按热度按时间

yr9zkbsy1#

我在SQLServer中尝试过这个。

DECLARE @cancelled Table (cancelledId int, rebookingId int)

INSERT into @cancelled values
(102    , 541  ),
( 250   ,  351 ),
( 129   ,  800 );

DECLARE @ResaList TABLE(Id int, property CHAR(1), ArrivalDate date, RN int)

INSERT INTO @ResaList values
(100  ,'X','2020-05-22',  9),
(102  ,'X','2020-03-05',  7),
(250  ,'D','2020-04-12', 10),
(129  ,'E','2020-03-15',  8),
(351  ,'D','2020-09-23',  5),
(541  ,'X','2020-06-01',  7),
(800  ,'E','2020-07-11',  8);

SELECT r.Id, r.Property, r.ArrivalDate, r.rn,
  rebooking.RebookingId
, rebooking.Rebooking_ArrivalDate
, rebooking.Rebooking_RN
, CASE WHEN rebooking.RebookingId IS NOT NULL THEN 'Re-booked' ELSE 'cancelled' end as tag
FROM @ResaList as r
OUTER APPLY (SELECT rc.Id, rc.ArrivalDate, rc.RN 
FROM @cancelled as c
INNER JOIN @ResaList AS rc
ON rc.Id = c.rebookingId
WHERE c.cancelledId = r.Id) as rebooking(RebookingId, Rebooking_ArrivalDate, Rebooking_RN)

+-----+----------+-------------+----+-------------+-----------------------+--------------+-----------+
| Id  | Property | ArrivalDate | rn | RebookingId | Rebooking_ArrivalDate | Rebooking_RN |    tag    |
+-----+----------+-------------+----+-------------+-----------------------+--------------+-----------+
| 100 | X        | 2020-05-22  |  9 | NULL        | NULL                  | NULL         | cancelled |
| 102 | X        | 2020-03-05  |  7 | 541         | 2020-06-01            | 7            | Re-booked |
| 250 | D        | 2020-04-12  | 10 | 351         | 2020-09-23            | 5            | Re-booked |
| 129 | E        | 2020-03-15  |  8 | 800         | 2020-07-11            | 8            | Re-booked |
| 351 | D        | 2020-09-23  |  5 | NULL        | NULL                  | NULL         | cancelled |
| 541 | X        | 2020-06-01  |  7 | NULL        | NULL                  | NULL         | cancelled |
| 800 | E        | 2020-07-11  |  8 | NULL        | NULL                  | NULL         | cancelled |
+-----+----------+-------------+----+-------------+-----------------------+--------------+-----------+

赞(0）回复(0）举报 2021-08-09

rhfm7lfc2#

在内部查询中使用select查找现有记录是非常糟糕的方法。在更大的table上，你会很痛苦。这是一个紧凑而有效的方法-

select
    a.ID,
    a.Property,
    a.Arrival Date,
    a.RN,
    b.RebookingID,
    b.Rebooking_ArrivalDate,
    b.Rebooking_RN,
    coalesce(b.tag,'Cancelled')as tag
from ResaList a left join
    (
    select *, 'Re-booked' as tag from Rebookings
    ) b 
    on a.ID=b.CancelledID

赞(0）回复(0）举报 2021-08-09

qoefvg9y3#

可以使用别名选择同一个表两次。

SELECT
    *
    ,(CASE WHEN b.[ID] IS NOT NULL THEN 'Re-booked'
      ELSE 'Cancelled'
      END) as [Tag]
FROM ResaList AS a
LEFT JOIN Rebookings ON a.ID = Rebookings.CancelledID
LEFT JOIN ResaList AS b ON b.ID = Rebookings.RebookingID

赞(0）回复(0）举报 2021-08-09

我来回答

连接这两个表以获得这个特定的输出？

3条答案

相关问题

热门标签

最新问答