我将使用一个具体但假设的例子。
每个订单通常只有一个订单项:
订单:
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
我想在订单上显示单个项目。但是这个偶尔包含两个(或更多)项目的订单,订单会出现重复:
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
但这给出了错误:
列或前缀“订单”与查询中使用的表名或别名不匹配。
大概是因为内部选择没有看到外部表。
group by
吗?
group by
需要列出所有其他列,不包括您不想重复的列。 Source
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
:
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
请注意,没有 ORDER BY
的 TOP 1
不是确定性的:此查询将为每个订单获取一个订单项,但未定义它将是哪一个。
查询的多次调用可以为同一订单提供不同的行项目,即使底层证券没有改变。
如果您想要确定的顺序,您应该在最里面的查询中添加一个 ORDER BY
子句。
我知道这个问题不久前就得到了回答,但是在处理大型数据集时,嵌套查询可能会很昂贵。这是一个不同的解决方案,其中嵌套查询将只运行一次,而不是针对返回的每一行。
SELECT
Orders.OrderNumber,
LineItems.Quantity,
LineItems.Description
FROM
Orders
INNER JOIN (
SELECT
Orders.OrderNumber,
Max(LineItem.LineItemID) AS LineItemID
FROM
Orders INNER JOIN LineItems
ON Orders.OrderNumber = LineItems.OrderNumber
GROUP BY Orders.OrderNumber
) AS Items ON Orders.OrderNumber = Items.OrderNumber
INNER JOIN LineItems
ON Items.LineItemID = LineItems.LineItemID
@Quassnoi 的回答很好,在某些情况下(尤其是在外部表很大的情况下),更有效的查询可能是使用窗口函数,如下所示:
SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM Orders
LEFT JOIN
(
SELECT LineItems.Quantity, LineItems.Description, OrderId, ROW_NUMBER()
OVER (PARTITION BY OrderId ORDER BY (SELECT NULL)) AS RowNum
FROM LineItems
) LineItems2 ON LineItems2.OrderId = Orders.OrderID And RowNum = 1
有时您只需 need to test 哪个查询提供更好的性能。
(SELECT NULL)
意味着什么?
你可以这样做:
SELECT
Orders.OrderNumber,
LineItems.Quantity,
LineItems.Description
FROM
Orders INNER JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
WHERE
LineItems.LineItemID = (
SELECT MIN(LineItemID)
FROM LineItems
WHERE OrderID = Orders.OrderID
)
这需要 LineItems.LineItemID
上的索引(或主键)和 LineItems.OrderID
上的索引,否则会很慢。
LineItems.LineItemID = null
并从结果中完全删除左侧实体订单。
从 SQL Server 2012 起,我认为这可以解决问题:
SELECT DISTINCT
o.OrderNumber ,
FIRST_VALUE(li.Quantity) OVER ( PARTITION BY o.OrderNumber ORDER BY li.Description ) AS Quantity ,
FIRST_VALUE(li.Description) OVER ( PARTITION BY o.OrderNumber ORDER BY li.Description ) AS Description
FROM Orders AS o
INNER JOIN LineItems AS li ON o.OrderID = li.OrderID
,另一种使用公用表表达式的方法:
with firstOnly as (
select Orders.OrderNumber, LineItems.Quantity, LineItems.Description, ROW_NUMBER() over (partiton by Orders.OrderID order by Orders.OrderID) lp
FROM Orders
join LineItems on Orders.OrderID = LineItems.OrderID
) select *
from firstOnly
where lp = 1
或者,最后也许你想显示所有加入的行?
逗号分隔的版本在这里:
select *
from Orders o
cross apply (
select CAST((select l.Description + ','
from LineItems l
where l.OrderID = s.OrderID
for xml path('')) as nvarchar(max)) l
) lines
相关子查询是依赖于外部查询的子查询。这就像 SQL 中的 for 循环。子查询将为外部查询中的每一行运行一次:
select * from users join widgets on widgets.id = (
select id from widgets
where widgets.user_id = users.id
order by created_at desc
limit 1
)
我最喜欢运行此查询的方式是使用 not exists 子句。我相信这是运行此类查询的最有效方法:
select o.OrderNumber,
li.Quantity,
li.Description
from Orders as o
inner join LineItems as li
on li.OrderID = o.OrderID
where not exists (
select 1
from LineItems as li_later
where li_later.OrderID = o.OrderID
and li_later.LineItemGUID > li.LineItemGUID
)
但是我没有针对这里建议的其他方法测试这种方法。
编辑:没关系,Quassnoi 有一个更好的答案。
对于 SQL2K,如下所示:
SELECT
Orders.OrderNumber
, LineItems.Quantity
, LineItems.Description
FROM (
SELECT
Orders.OrderID
, Orders.OrderNumber
, FirstLineItemID = (
SELECT TOP 1 LineItemID
FROM LineItems
WHERE LineItems.OrderID = Orders.OrderID
ORDER BY LineItemID -- or whatever else
)
FROM Orders
) Orders
JOIN LineItems
ON LineItems.OrderID = Orders.OrderID
AND LineItems.LineItemID = Orders.FirstLineItemID
尝试了十字架,效果很好,但需要的时间稍长。调整行列以具有最大值并添加保持速度并丢弃额外记录的组。
这是调整后的查询:
SELECT Orders.OrderNumber, max(LineItems.Quantity), max(LineItems.Description)
FROM Orders
INNER JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
Group by Orders.OrderNumber
尝试这个
SELECT
Orders.OrderNumber,
LineItems.Quantity,
LineItems.Description
FROM Orders
INNER JOIN (
SELECT
Orders.OrderNumber,
Max(LineItem.LineItemID) AS LineItemID
FROM Orders
INNER JOIN LineItems
ON Orders.OrderNumber = LineItems.OrderNumber
GROUP BY Orders.OrderNumber
) AS Items ON Orders.OrderNumber = Items.OrderNumber
INNER JOIN LineItems
ON Items.LineItemID = LineItems.LineItemID
不定期副业成功案例分享
CROSS APPLY
代替INNER JOIN
和OUTER APPLY
代替LEFT JOIN
(与LEFT OUTER JOIN
相同)。