mysql - SQL中如何用union语句获取不同的列名

Question

我正在查找数据中的错误；遍及表中的不同列。我正在使用 UNION 语句一次运行多个查询，以查找表中的错误。表中找到的所有错误都放在发现错误的第一个查询的列名下的一列中。有没有一种方法可以拆分列，使通过 UNION 语句中的单独查询发现的每个错误也显示为单独的列/实体？

这是我正在使用的代码:

SELECT DISTINCT
[1001account] 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE
StresstestaccountEnabled LIKE '%Yes%' AND BalancesheetAmount <> 0
AND
PATINDEX('%[^0-9]%', [1001account]) > 0 OR [1001account] IS NULL

UNION

SELECT DISTINCT
[5028account] 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE
StresstestaccountEnabled LIKE '%Yes%' AND BalancesheetAmount <> 0
AND
PATINDEX('%[^0-9]%', [5028account]) > 0 OR [5028account] IS NULL

UNION

SELECT DISTINCT
[BalanceSheetType] 
FROM
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE
StressTestAccountEnabled LIKE '%Yes%' AND BalanceSheetAmount <> 0
AND
[BalanceSheetType] NOT LIKE '%Assets%'
AND
BalanceSheetType NOT LIKE '%Liabilities%'

UNION

SELECT DISTINCT 
CorepRRR 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE '%Yes%' AND BalanceSheetAmount<>0
AND
CorepRRR NOT LIKE '%D%'
AND
CorepRRR NOT LIKE '%R%'
AND
CorepRRR NOT LIKE '%P%'
AND
CorepRRR NOT LIKE '%Not Applicable%'

UNION

SELECT 
[CoreGroupId] 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE '%Yes%' AND BalanceSheetAmount<>0
AND
[CoreGroupId] NOT IN (2, 3, 4, 5, 6, 7, 8, 9, 11 ,14, 15, 99)

UNION

SELECT 
CoreGroupDescription 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE '%Yes%' AND BalanceSheetAmount<>0
AND
[CoreGroupDescription] NOT IN 
( 'DLL', 'ABB_Assets', 'ABB_Liab', 'LRDW', 'OBV', 'VRN', 'Force', 'ROB', 'HedgeAccounting', 'LRDW_US', 'CorepDefaultsCorrections', 'Corrections'  )

UNION

SELECT DISTINCT 
[Country] 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE 'Yes'
AND
BalanceSheetAmount <> 0 
AND
Country NOT LIKE '%[^a-z]%'
AND
(
LEN(Country)>2 OR LEN(Country)<2 
)

UNION

SELECT DISTINCT 
CountryOfRisk 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE 'Yes'
AND
BalanceSheetAmount != 0
AND
Country NOT LIKE '%[^a-z]%'
AND
(
LEN(Country)>2 OR LEN(Country)<2
)

UNION


SELECT DISTINCT 
InterestType
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE 'Yes'
AND
BalanceSheetAmount <> 0
AND
InterestType NOT LIKE 'Fixed'
AND
InterestType NOT LIKE 'Floating'

UNION

SELECT DISTINCT
InterestRate
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE
BalanceSheetAmount <> 0
AND
StressTestAccountEnabled LIKE 'Yes'
AND
(
InterestRate > 20
OR
InterestRate < -2
)

UNION

SELECT DISTINCT 
LocalBalance
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE 'Yes'
AND
BalanceSheetAmount <> 0
AND
LocalBalance IS NULL

这是输出示例:

[1001account]
NULL
1 -19.4163150000
2 -17.4100000000
3 -7.0000000000
4 -6.0000000000

NULL 在我的查询中被归类为错误，它位于 [1001account] 列中，但第 2、3 和 4 行中的其他值属于另一列:Swaprates 和 Interestrates。

我如何以用户可以看到第 2、3 和 4 行属于不同列的方式进行可视化？

Answer 1

只需在 SELECT 列表中添加第二列，它会告诉您值的来源:

SELECT DISTINCT
[1001account] AS [value], '1001account' AS [column]

其他列同理:

SELECT DISTINCT
[5028account] AS [value], '5028account' AS [column]