我们如何在不使用if-else块而不是在单个查询中实现逻辑

Question

SELECT column_key,
Column_name
FROM tableA
UNION 
SELECT column_key,
Column_name
FROM tableB
WHERE Database_Name = 'PROD'
AND Table_Name = 'Document_Setup'
AND Column_Name IN
(
'App_Advice',
'Expect_Response_Time',
'Matching_Interchanges',
'Transmit')

现在我想在Doc_Standard_Name='830'可用时在第二个表中添加新的列检查条件，然后添加列名"；Multiple_Transaction_Set；否则它将按当前的工作方式工作但我不想用两个大if语句块来编写逻辑。我们可以用case语句或其他方式实现吗？

SELECT
*
FROM Document_Standard
WHERE Document_key = @Document_key
AND Doc_Standard_Name IN ('830')

SELECT column_key,
Column_name
FROM tableA
UNION 
SELECT column_key,
Column_name
FROM tableB
WHERE Database_Name = 'PROD'
AND Table_Name = 'Document_Setup'
AND Column_Name IN
(
'App_Advice',
'Expect_Response_Time',
'Matching_Interchanges',
'Transmit',
'Multiple_Transaction_Set')

我已经用这种方式写了，但不想写if else块，而是我想在一个块中处理这个：

IF EXISTS
(
SELECT
*
FROM Document_Standard
WHERE Document_key = @Document_key
AND Doc_Standard_Name IN ('830')
)
BEGIN
SELECT column_key,
Column_name
FROM tableA

UNION 
SELECT column_key,
Column_name
FROM tableB
WHERE Database_Name = 'PROD'
AND Table_Name = 'Document_Setup'
AND Column_Name IN
(
'App_Advice',
'Expect_Response_Time',
'Matching_Interchanges',
'Transmit')
END
ELSE
BEGIN
SELECT column_key,
Column_name
FROM tableA

UNION 
SELECT column_key,
Column_name
FROM tableB
WHERE Database_Name = 'PROD'
AND Table_Name = 'Document_Setup'
AND Column_Name IN
(
'App_Advice',
'Expect_Response_Time',
'Matching_Interchanges',
'Transmit',
'Multiple_Transaction_Set')

END;

Answer 1

只需使用一些OR逻辑：

SELECT column_key,
Column_name
FROM tableA
UNION --ALL --Should this is UNION? UNION is a significantly more expensive; it should only be used if you need DISTINCT rows
SELECT column_key,
Column_name
FROM tableA
WHERE Column_Name IN ('App_Advice', 'Expect_Response_Time', 'Matching_Interchanges', 'Transmit')
OR (EXISTS (SELECT 1
FROM Document_Standard
WHERE Document_key = @Document_key
AND Doc_Standard_Name IN ('830'))
AND Column_Name = 'Multiple_Transaction_Set');