是否可以在SQL查询的WHERE子句中比较元组(thanks,a_horse_with_no_name)？那样,我可以转换这个：
/* This is actually a sub-sub-sub-query in the middle *
* of an incredibly complex stored procedure. */
SELECT ISNULL(SUM(DT.DetailField),0)
FROM DetailTable DT
WHERE DT.ForeignKey = ...
AND EXISTS (/* I know this sub-sub-sub-sub-query *
* will return at most one row. */
SELECT 'X'
FROM HeaderTable HT
WHERE HT.HeaderKey = DT.HeaderKey
AND HT.HeaderField1 = ...
AND HT.HeaderField2 = ...)
进入类似的东西：
SELECT ISNULL(SUM(DetailField),0)
FROM DetailTable DT
WHERE DT.ForeignKey = ...
AND (SELECT HT.HeaderField1,HT.HeaderField2
FROM HeaderTable HT
WHERE HT.HeaderKey = DT.HeaderKey) = (...,...)
解决方法
Goran的回答对我来说最好,我投了赞成票.只是为了添加另一个选项,因为您使用的是sql Server,从子查询中获取多个列的灵活方法是外部应用.你可以比较两个值(一个元组),如：
select * from SoMetable t1 outer apply ( select * from SomeOtherTable t2 where t1.Stuff = t2.Unit ) sub1 outer apply ( select * from YetAnotherTable t3 where t1.Stuff = t3.jetser ) sub2 where sub1.col1 = sub2.col1 and sub1.col2 = sub2.col2
