SQL有条件地选择一行

Question

我想知道是否可以在 SQL 中执行类似下面的操作。

假设您有三个表。Field Id是贯穿所有数据库的关系键

Table1 - PK Id, .... 
Table2 - PK Id, T3FK, TimeStamp,....
Table3 - PK Id, identity, Status, ....

Table1.id (1 to *) Table 2.id
Table1.id (1 to *) Table 3.id
Table2.id+Table2.T3FK (1 to *) Table3.Id+Table3.identity

我正在尝试组合以下选择语句:

SELECT T1.*, T2.* 
  FROM Table1 T1
  JOIN Table2 T2 ON T1.Id = T2.Id
  JOIN Table3 T3 on T1.Id = T3.Id
 WHERE T3.Status = 'A' 
   AND T2.P3Fk = T3.identity 
   AND T1.id IN (SELECT Id 
                   FROM ListOfIds)

...和:

SELECT T1.*, T2.* 
  FROM Table1 T1
  JOIN Table2 T2 ON T1.id = T2.id
  JOIN (SELECT id, MAX(TimeStamp) as TimeStamp 
          FROM Table2 
      GROUP BY id) t2a ON t2.id = t2a.id 
                      AND t2.TimeStamp = t2a.TimeStamp
 WHERE t1.Id IN (SELECT Id 
                   FROM ListOfIds)

所以基本上我想做的是有一个返回的选择语句T2 中的行，其中 T2.P3FK = T3.identity 和 T3.status = 'A' 如果不存在则返回 T2 中具有最新时间戳的行。我需要对 ID 列表以及不仅仅是一个 ID 执行此操作。

Answer 1

可能最简单的方法是将 JOIN 修改为 T2 和 T3，并将 JOIN 添加到 t2a，然后在 T2 中您需要的所有字段上使用 case 或 coalesce。

SELECT T1.*, 
   COALESCE(T2.id,t2a.id) id,
   COALESCE(...
  FROM Table1 T1
  LEFT JOIN (Table2 T2
     INNER JOIN Table3 T3 on T1.Id = T3.Id
             and  T3.status = 'A' )
   ON T1.Id = T2.Id
  INNER JOIN (SELECT id, MAX(TimeStamp) as TimeStamp 
          FROM Table2 
      GROUP BY id) t2a ON t2.id = t2a.id 
                      AND t2.TimeStamp = t2a.TimeStamp
 WHERE t1.Id IN (SELECT Id 
                   FROM ListOfIds)