从四个表中的任意一个中选择数据

我想从四个表中的任何一个中选择数据。数据可以在四个中的任何一个上可用。四个表也将包含数据。两个表也将包含数据。一个表也将包含数据。请在下面纠正我。

select top 100  t1.* 
from  
Table1 t1 
left JOIN Table2 t2 on t1.EventId = t2.EventId 
LEFT JOIN Table3 t3 ON t1.EventId = t3.EventId
LEFT JOIN Table4 t4 ON t1.EventId = t4.EventId
WHERE 
t1.EventId = 12345 AND 
t1.EditType = 'D' and 
t2.EditType = 'D'and 
t3.EditType = 'D' and 
t4.EditType = 'D'

将条件放在 WHERE 子句中会将外连接转换为内连接。将条件放在ON条款中

Select *
from Table1 t1 
left JOIN Table2 t2 
  on t1.EventId = t2.EventId 
  and t2.EditType = 'D'
left  JOIN Table3 t3 
  ON t1.EventId = t3.EventId
  and t3.EditType = 'D' 
left  JOIN Table4 t4 
  ON t1.EventId = t4.EventId
  and t4.EditType = 'D'
where t1.EventId = 12345
and t1.EditType = 'D'

如果您的表具有相同的结构，则最好在视图或 CTE(公用表表达式(中进行所有联合，然后从中进行选择，而不是执行左连接 - 这样您的信息就会出现在单独的记录中：

WITH FullData AS (
SELECT *, 1 AS TableSource FROM Table1
UNION ALL
SELECT *, 2 AS TableSource FROM Table2
UNION ALL
SELECT *, 3 AS TableSource FROM Table3
UNION ALL
SELECT *, 4 AS TableSource FROM Table4 )
SELECT * FROM FullData WHERE EventID = 12345 And EventType = 'D'

根据我的示例，您还可以添加源标识符来判断信息是从哪个表中提取的 - 只要结构始终保持不变，联合将正常工作。

相关内容

最新更新

热门标签：