我将使用一个具体但假设的例子。
Each Order通常只有一个行项目:
Orders:
OrderGUID OrderNumber
========= ============
{FFB2...} STL-7442-1
{3EC6...} MPT-9931-8A
行项目:
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
我想显示订单上的单个商品。但是,对于偶尔包含两个(或更多)商品的订单,订单将appear be 重复的:
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
但这给出了错误:
列或前缀“订单”不
与表名或别名匹配
在查询中使用。
大概是因为内部选择看不到外部表。