我将使用一个具体的但假设的示例。
每个 订单 通常只有一个 订单项 :
命令:
OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A
LineItems:
LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing
但偶尔会有一个包含两个订单项的订单:
LineItemID Order ID Quantity Description ========== ======== ======== ================================= {A58A1...} 6,784,329 5 pentametric fan {0E9BC...} 6,784,329 5 differential girdlespring
通常在向用户显示订单时:
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders INNER JOIN LineItems ON Orders.OrderID = LineItems.OrderID
我想在订单上显示单个项目。但是,如果这个偶然的订单包含两个(或多个)项目,那么这些订单将 看起来 是 重复的 :
OrderNumber Quantity Description =========== ======== ==================== STL-7442-1 7 prefabulated amulite MPT-9931-8A 32 spurving bearing KSG-0619-81 5 panametric fan KSG-0619-81 5 differential girdlespring
我真正想要的是让SQL Server 仅选择一个 ,因为这样就 足够了 :
OrderNumber Quantity Description =========== ======== ==================== STL-7442-1 7 prefabulated amulite MPT-9931-8A 32 differential girdlespring KSG-0619-81 5 panametric fan
如果我喜欢冒险,可以向用户显示一个省略号,以表明有多个:
OrderNumber Quantity Description =========== ======== ==================== STL-7442-1 7 prefabulated amulite MPT-9931-8A 32 differential girdlespring KSG-0619-81 5 panametric fan, ...
所以问题是如何
我的首次尝试是仅加入“ TOP 1 ”行项目:
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders INNER JOIN ( SELECT TOP 1 LineItems.Quantity, LineItems.Description FROM LineItems WHERE LineItems.OrderID = Orders.OrderID) LineItems2 ON 1=1
但这给出了错误:
列或前缀“订单” 与 查询中使用的表名或别名不匹配。
大概是因为内部选择看不到外部表。
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders JOIN LineItems ON LineItems.LineItemGUID = ( SELECT TOP 1 LineItemGUID FROM LineItems WHERE OrderID = Orders.OrderID )
在SQL Server 2005及更高版本中,您可以替换INNER JOIN为CROSS APPLY:
INNER JOIN
CROSS APPLY
SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description FROM Orders CROSS APPLY ( SELECT TOP 1 LineItems.Quantity, LineItems.Description FROM LineItems WHERE LineItems.OrderID = Orders.OrderID ) LineItems2
请注意,TOP 1如果没有,ORDER BY则不是确定性的:此查询为每个订单提供一个订单项,但未定义为哪个订单项。
TOP 1
ORDER BY
即使基础没有变化,多次调用查询也可以为同一订单提供不同的订单项。
如果要确定顺序,则应ORDER BY在最里面的查询中添加一个子句。