问题描述
Hello Experts，
我的源数据有点像下表...
MachineName ComplianceCheckItem状态
M1 C1通票
M1 C2通票
M1 C3失败
M1 C4通票
M2 C3通票
M2 C4通票
M3 C1通票
M3 C3失败
M3 C5通票
有5个符合性检查项目，我需要对每台机器具有状态。源可能有也可能没有与这些机器+合规性检查项目组合相对应的数据。对于源中无法使用的所有合规性检查项目，我希望将其显示为N / A。输出应如下所示..
MachineName ComplianceCheckItem状态
M1 C1通票
M1 C2通票
M1 C3失败
M1 C4通票
M1 C5 N / A
M2 C1 N / A
M2 C2 N / A
M2 C3通票
M2 C4通票
M2 C5 N / A
M3 C1通票
M3 C2 N / A
M3 C3失败
M3 C4 N / A
M3 C5通票
以下是ComplianceCheckItems的主清单
ComplianceCheckItem
C1
C2
C3
C4
C5
我尝试了什么：
试图获得不同的机器名称然后交叉加入不同的ComplianceCheckItem
但是因为数据量巨大，我认为这不是最佳解决方案..
Hello Experts,
I have source data which somewhat looks like the below table..
MachineNameComplianceCheckItemStatus
M1C1Pass
M1C2Pass
M1C3Fail
M1C4Pass
M2C3Pass
M2C4Pass
M3C1Pass
M3C3Fail
M3C5Pass
There are 5 compliance check items for which i need to have the status against each machine. The source may or may not have data corresponding to these machine + compliance check item combination. For all the compliance check items that are not availble in the source i want to show it up as 'N/A'. The output should be as shown below..
MachineNameComplianceCheckItemStatus
M1C1Pass
M1C2Pass
M1C3Fail
M1C4Pass
M1C5N/A
M2C1N/A
M2C2N/A
M2C3Pass
M2C4Pass
M2C5N/A
M3C1Pass
M3C2N/A
M3C3Fail
M3C4N/A
M3C5Pass
Below is the master list of ComplianceCheckItems
ComplianceCheckItem
C1
C2
C3
C4
C5
What I have tried:
Tried to get distinct machine name and then cross join with distinct ComplianceCheckItem
but since the data being huge, i think that it is not the optimal solution..
推荐答案
DECLARE @master TABLE (MachineName VARCHAR(50), ComplianceCheckItem VARCHAR(50), [Status] VARCHAR(50))
INSERT INTO @master (MachineName, ComplianceCheckItem, [Status])
VALUES('M1', 'C1', 'Pass'),
('M1', 'C2', 'Pass'),
('M1', 'C3', 'Fail'),
('M1', 'C4', 'Pass'),
('M2', 'C3', 'Pass'),
('M2', 'C4', 'Pass'),
('M3', 'C1', 'Pass'),
('M3', 'C3', 'Fail'),
('M3', 'C5', 'Pass')
DECLARE @cci TABLE (ComplianceCheckItem VARCHAR(50))
INSERT INTO @cci(ComplianceCheckItem)
VALUES('C1'), ('C2'), ('C3'), ('C4'), ('C5')
SELECT t.MachineName, t.ComplianceCheckItem, COALESCE(ma.[Status] , 'NA') AS [Status]
FROM (
SELECT DISTINCT m.MachineName, c.ComplianceCheckItem
FROM @master AS m , @cci AS c
) AS t LEFT JOIN @master AS ma ON t.MachineName = ma.MachineName AND t.ComplianceCheckItem = ma.ComplianceCheckItem
结果（如预期）：
Result (as expected):
MachineName ComplianceCheckItem Status
M1 C1 Pass
M1 C2 Pass
M1 C3 Fail
M1 C4 Pass
M1 C5 NA
M2 C1 NA
M2 C2 NA
M2 C3 Pass
M2 C4 Pass
M2 C5 NA
M3 C1 Pass
M3 C2 NA
M3 C3 Fail
M3 C4 NA
M3 C5 Pass
欲了解更多详情，请参阅：
[]
[]
[]
For further details, please see:
Visual Representation of SQL Joins[^]
Using Cross Joins[^]
COALESCE (Transact-SQL)[^]
这篇关于需要有关T-SQL查询（MSSQL）的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！