有条件定义的内部选择的T-sql嵌套查询

Question

我需要一个嵌套查询，但有条件地定义了inner-select sql。

这行不通:

SELECT inner.a, inner.b, t4.c FROM 
CASE
  WHEN (@Param1 = 1) THEN
    ( SELECT t1.col1 AS a , t2.col2 AS b
      FROM Table1 t1 
      INNER JOIN Table2 t2 ON t2.pk = t1.fk2 ) AS inner
  WHEN (@Param2 = 2) THEN
   ( SELECT t1.col1 AS a , t3.col3 as b
     FROM Table1 t1 
     LEFT JOIN Table3 t3 ON t3.pk = t1.fk3 ) AS inner
END
LEFT JOIN Table4 t4 ON t4.pk = inner.b
WHERE inner.a = @Param1

Answer 1

SELECT inner1.a, inner1.b, outer1.c 
FROM ( SELECT t1.col1 AS a , t2.col2 AS b
      FROM Table1 t1 
      INNER JOIN Table2 t2 ON t2.pk = t1.fk2 ) AS inner1
    LEFT JOIN Table4 t4 ON t4.pk = inner1.b
WHERE @Param1=1
    AND inner1.a = @Param1

UNION ALL 
SELECT inner1.a, inner1.b, outer1.c 
FROM ( SELECT t1.col1 AS a , t3.col3 as b
     FROM Table1 t1 
     LEFT JOIN Table3 t3 ON t3.pk = t1.fk3 ) AS inner1
LEFT JOIN Table4 t4 ON t4.pk = inner1.b
WHERE @Param2 =2 
    inner1.a = @Param1

Answer 2

试试这个:

SELECT  InnerTable.a, InnerTable.b, t4.c
FROM    (   SELECT  t1.col1 AS a, t2.col2 AS b
            FROM    Table1 t1 INNER JOIN
                    Table2 t2 ON t2.pk = t1.fk2
            WHERE   @Param1 = 1
            UNION
            SELECT  t1.col1 AS a, t3.col3 AS b
            FROM    Table1 t1 LEFT JOIN
                    Table3 t3 ON t3.pk = t1.fk3
            WHERE   @Param2 = 2
        ) AS InnerTable
        LEFT JOIN Table4 t4 ON t4.pk = InnerTable.b
WHERE   InnerTable.a = @Param1

Answer 3

不能这样使用，使用动态查询并执行字符串

添加where条件也可以动态添加column

 DECLARE @SelectStatement NVARCHAR(2000)
DECLARE @FullStatement NVARCHAR(4000)
SET @SelectStatement = 'SELECT TOP 5 * FROM SalesHistory '
SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')
PRINT @FullStatement
EXECUTE sp_executesql @FullStatement