sql-server - 在查询的 SELECT 部分使用 IF

Question

我需要创建这样的东西

SELECT x.id
   , x.name
   , x.type
   ,(
      IF x.type = 1
         (SELECT SUM(Col1) FROM TableA WHERE ... etc)
      ELSE IF x.type = 2
         (SELECT SUM(Col2) FROM TableB WHERE ... etc)
    ) AS Total
FROM TableX as x

所以我尝试根据x.type的值选择不同的子查询

翅膀

Answer 1

尝试使用LEFT JOIN和COALESCE。使用 x.type 条件来连接表。

COALESCE (Transact-SQL): Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. https://msdn.microsoft.com/en-us/library/ms190349.aspx

SELECT x.id
   , x.name
   , x.type
   , COALESCE(SUM(TableA.Column), SUM(TableB.Column)) as column_xyz
FROM TableX as x
LEFT JOIN TableA ON x.type = 1 AND ...
LEFT JOIN TableB ON x.type = 2 AND ...

您还可以使用 CASE WHEN ... THEN ... 而不是 COALESCE 来定义要使用的列。