我需要什么样的连接来执行这种查询？
我需要什么样的连接来执行这种查询？
提问于 2012-05-18 08:58:05
假设我有三张桌子：
- Cars (CarID)包含10个分叉的汽车行。
- SafetyTests (TestID，TestName)包含20个不同的安全测试行。
- TestResults (ID、TestID、CarID、TestValue)包含0到200个安全测试行之间的数字。
在安全测试表中，它包含了从汽车表中对10辆汽车进行的每一次安全测试的结果。最多有20个安全测试--这些测试显然是从SafetyTests表的select语句中获得的。然而，10辆车中有些已经全部进行了20次测试，而另一些则只有5次。
我想要产生一个10×20矩阵，这将显示每辆车20个结果(即使他们没有20个安全测试结果)。如果尚未在car上执行测试，它将只显示测试名称，但显示值为零(或null)。
我认为这将是SafetyTests表上的一个选择(以获得不同的测试ID列表)和一个左连接到Cars和TestResults之间的连接组合上，但问题是，由于没有匹配CAR表，所以它返回空值的Car ID。
回答 2
Stack Overflow用户
回答已采纳
发布于 2012-05-18 09:00:00
您可以从汽车中交叉连接到安全测试-然后左加入到TestResults -交叉连接确保您得到10 x 20矩阵，左连接给您的结果。
使用ISNULL或聚结将结果集中的空替换为零。
例如：
SELECT car.name, testresults.testname, isnull(testresults.result, 0) FROM
cars CROSS JOIN
safetytests
LEFT JOIN testresults on safetytests.testid = testresults.testid AND car.id = testresults.carid
有人建议说:)这个网站真的很好吗？
http://sqlfiddle.com/#!3/2bc73/2
Stack Overflow用户
发布于 2012-05-18 09:12:06
如果我正确理解了这个问题，您需要一个10x20的结果集，如下所示：
CarID | Test1 | Test2 | .... | Test20
-------------------------------------------------------
1 | NULL | Fail | .... | true
2 | 2 | Pass | .... | false要做到这一点，我将利用Server 2008的PIVOT功能。
WITH Results AS
( SELECT cars.CarID,
TestName,
TestValue
FROM Cars
CROSS JOIN SafetyTests s
LEFT JOIN TestResults res
ON res.CarID = Cars.CarID
AND res.TestID = s.TestID
)
SELECT *
FROM Results
PIVOT
( MAX(TestValue)
FOR TestName IN ([TesT1], [Test2], [Test3], [Test4])
-- LIST ALL 20 TEST NAMES HERE
) pvt这样做的缺点是，您必须列出所有要旋转的测试名称，否则它们将不会显示为列，但是可以动态地这样做。下面的查询实际上与上面的查询完全相同，但我已经动态地生成了所有列名的列表，并将它们插入到查询中。
DECLARE @SQL NVARCHAR(MAX) = ''
SELECT @SQL = @SQL + ',' + QUOTENAME(TestName)
FROM SafetyTests
SET @SQL = 'WITH Results AS
( SELECT Cars.CarID,
TestName,
TestValue
FROM Cars
CROSS JOIN SafetyTests s
LEFT JOIN TestResults res
ON res.CarID = Cars.CarID
AND res.TestID = s.TestID
)
SELECT *
FROM Results
PIVOT
( MAX(TestValue)
FOR TestName IN (' + STUFF(@SQL, 1, 1, '') + ')
) pvt'
EXECUTE SP_EXECUTESQL @SQLSQL Fiddle
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/10649617
复制相关文章
相似问题
腾讯云开发者
