SQL Server：WHERE 子句中带有子查询的布尔表达式

Question

我在子查询的 WHERE 子句中有一个子查询。尽管存在数据库的设计问题（不是我的工作），但在尝试扩展此表达式中的顶级 WHERE 子句时，我遇到了一些奇怪的错误。下面的例子有效。

        LEFT OUTER JOIN CargoVoyageLocation on CargoVoyageLocation.VoyageLocationID 
                        = (SELECT TOP 1 CargoVoyageLocation.VoyageLocationID FROM CargoVoyageLocation
                                JOIN Cargo on CargoVoyageLocation.CargoID = Cargo.CargoID
                                JOIN CargoType on Cargo.CargoTypeID = CargoType.CargoTypeID
                                WHERE

                (CargoType.SystemName = 'HAZMAT' OR CargoType.SystemName = 'BUNKERS' OR Cargo.IsBunkers = 1)
                    AND 
                        CargoVoyageLocation.VoyageLocationID 
                                = (SELECT VoyageLocationAttribute.VoyageLocationID FROM VoyageLocationAttribute 
                                        JOIN VoyageLocationAttributeName 
                                            ON VoyageLocationAttribute.VoyageLocationAttributeNameID = VoyageLocationAttributeName.VoyageLocationAttributeNameID
                                            WHERE VoyageLocationAttribute.VoyageLocationAttributeNameID = (SELECT VoyageLocationAttributeNameID FROM VoyageLocationAttributeName WHERE SystemName = 'PREVIOUS_VOYAGE_ID')
                                                AND VoyageLocationAttribute.AttributeInt = v.VoyageID) ORDER BY CargoVoyageLocation.ModifiedDate DESC)

然后我添加一个简单的布尔表达式和括号：

            LEFT OUTER JOIN CargoVoyageLocation on CargoVoyageLocation.VoyageLocationID 
                        = (SELECT TOP 1 CargoVoyageLocation.VoyageLocationID FROM CargoVoyageLocation
                                JOIN Cargo on CargoVoyageLocation.CargoID = Cargo.CargoID
                                JOIN CargoType on Cargo.CargoTypeID = CargoType.CargoTypeID
                                WHERE

                (CargoType.SystemName = 'HAZMAT' OR CargoType.SystemName = 'BUNKERS' OR Cargo.IsBunkers = 1)
                    AND 
Here --->                       (1=0 OR CargoVoyageLocation.VoyageLocationID 
                                = (SELECT VoyageLocationAttribute.VoyageLocationID FROM VoyageLocationAttribute 
                                        JOIN VoyageLocationAttributeName 
                                            ON VoyageLocationAttribute.VoyageLocationAttributeNameID = VoyageLocationAttributeName.VoyageLocationAttributeNameID
                                            WHERE VoyageLocationAttribute.VoyageLocationAttributeNameID = (SELECT VoyageLocationAttributeNameID FROM VoyageLocationAttributeName WHERE SystemName = 'PREVIOUS_VOYAGE_ID')
                                                AND VoyageLocationAttribute.AttributeInt = v.VoyageID) ORDER BY CargoVoyageLocation.ModifiedDate DESC))

这会产生以下错误：

1. 关键字“ORDER”附近的语法不正确。
2. “v”附近的语法不正确。

我很困惑。这是怎么回事？

Answer 1

ORDER BY 子句正在被 TOP 1 （第 2 行）使用，所以我怀疑你的括号是错误的，最后一行应该是：

AND VoyageLocationAttribute.AttributeInt = v.VoyageID)) ORDER BY CargoVoyageLocation.ModifiedDate DESC)