联接以返回单个行

Question

我需要编写一个SQL查询，它将填充DELTA列。如果状态发生了变化，那么是的，否则就不会。我尝试过内在的连接，但这是行不通的。

[Current Week Project]      |       [Last Week Project] 
ProjectID   Status  Delta   |   ProjectID   Status
       8614 Amber           |   8614    Green
       8548 Unknown         |   8548    Unknown
       2591 Green           |   2591    Green
       7813 Red             |   7813    Green
       8413 Green           |   8413    Green
       8183 Green           |   8183    Green
       7431 Red             |   7431    Red
       7399 Green           |   7399    Green
       7776 Green           |   7776    Green
       8609 Green           |   8609    Green
       8068 Red             |   8068    Amber
       8019 Green           |   8019    Green
       7799 Green           |   7799    Green
       6727 Amber           |   6727    Unknown
       7482 Green           |   7482    Green
       8384 Red             |   8384    Green

SELECT a.[Status],b.[Status] 
FROM [Current Week Project] a 
INNER JOIN [Last Week Project] b  
ON a.[ProjectID]= b.[ProjectID]

Answer 1

试试这个：

SELECT a.[ProjectID], a.[Status], b.[Status], 
      (CASE WHEN a.[Status] = b.[Status] THEN 'No' ELSE 'YES' END) AS Delta
FROM [Current Week Project] a 
INNER JOIN [Last Week Project] b ON a.[ProjectID]= b.[ProjectID];