sql用法 - sql連接




SQL Server:如何加入第一行 (7)

@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

有時你只需要測試哪個查詢可以提供更好的性能。

我會用一個具體的,但是假設的例子。

每個訂單通常只有一個訂單項

命令:

OrderGUID   OrderNumber
=========   ============
{FFB2...}   STL-7442-1      
{3EC6...}   MPT-9931-8A

了LineItem:

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

但是,這給出了錯誤:

“訂單”列或前綴與查詢中使用的表名或別名不匹配。

大概是因為內部選擇沒有看到外部表格。


,另一個使用公共表格表達的方法:

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

嘗試十字架,很好地工作,但需要稍長。 調整後的行列有最大和增加的組,保持速度並且刪除了額外的記錄。

以下是調整後的查詢:

SELECT Orders.OrderNumber, max(LineItems.Quantity), max(LineItems.Description)
FROM Orders
    INNER JOIN LineItems 
    ON Orders.OrderID = LineItems.OrderID
Group by Orders.OrderNumber

我最喜歡的運行這個查詢的方式是使用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
    )

但是我沒有對這裡提出的其他方法進行測試。


我通過使用LEFT JOIN和GROUP BY Orders.OrderNumber來解決類似的問題。 有沒有這樣做的理由?

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
    LEFT JOIN LineItems 
    ON Orders.OrderID = LineItems.OrderID
GROUP BY Orders.OrderNumber

我會在你自己的問題中回答你的回答問題:

Orders             LineItems
+-------------+    +---------+----------+---------------+
| OrderNumber |    | OrderID | Quantity | Description   |
+-------------+    +---------+----------+---------------+
| 22586       |    | 22586   | 17       | Trunion       |
+-------------+    | 22586   | 3        | Girdle Spring |
                   +---------+----------+---------------+

在OrderNumber上將兩者結合在一起給出:

OrderNumber  Quantity  Description
-----------  --------  -------------
22586        17        Trunion
22586        3         Girdle Spring

2 row(s) affected

我們希望它只返回一行:

OrderNumber  Quantity  Description
-----------  --------  -------------
22586        17        Trunion

1 row(s) affected

這就是我使用GROUP BY Orders.OrderNumber的原因,它只為每個OrderNumber返回一行。


相關的子查詢是依賴於外部查詢的子查詢。 它就像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
)

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及更高版本中,您可以使用CROSS APPLY替換INNER JOIN

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




sql-server-2000