最終 - sqlserver rownum=1



SQL Server:最初の行に参加する方法 (7)

私は、具体的な、しかし仮説的な例を使用します。

オーダーには通常、1つの広告申込情報しかありません。

注文:

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

しかし時には2つの広告申込情報がある注文があります。

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

私はオーダーに単一のアイテムを表示したい。 しかし、2つ(またはそれ以上)のアイテムを含むこの時折の注文では、注文は重複しているように見えます。

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, ...

だから問題は、

  • 「重複する」行を排除する
  • 重複を避けるために、行の1つにのみ結合する

最初の試み

最初の単純な試みは、「 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

しかし、それはエラーを与える:

列または接頭辞「Orders」が、照会で使用される表名または別名と一致しません。

おそらく内側の選択が外部テーブルを参照しないためです。


Answer #1

@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

場合によっては、より良いパフォーマンスを提供するクエリをテストするだけでよい場合もあります。


Answer #2

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で2つを結合すると、次のようになります。

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

2 row(s) affected

1行しか返さないようにしたい場合は:

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

1 row(s) affected

これは、OrderNumberごとに1つの行しか返さないGROUP BY Orders.OrderNumberを使用する理由です。


Answer #3

このクエリを実行する私の好きな方法は、存在しない句でです。 私はこれがこの種のクエリを実行する最も効率的な方法だと考えています:

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
    )

しかし、私はここで提案された他のメソッドに対してこのメ​​ソッドをテストしていません。


Answer #4

共通テーブル式を使用する別のaproach:

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

Answer #5

相関サブ問合せは、外部問合せに依存するサブ問合せです。 これは、SQLのforループに似ています。 サブクエリは外部クエリの各行に対して1回実行されます。

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
)

Answer #6

私はこの質問がしばらく前に答えられたことは知っていますが、大きなデータセットを扱う場合、ネストされたクエリはコストがかかることがあります。 ここでは、ネストされたクエリが返される行ごとではなく、一度だけ実行される別のソリューションがあります。

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

Answer #7
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 JOINCROSS 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は確定的ではないことに注意してください。このクエリでは、オーダーごとに1つの広告申込情報が表示されますが、どちらの広告申込情報であるかは定義されていません。

複数のクエリを呼び出すと、同じオーダーの異なる広告申込情報が表示されます(基本広告が変更されていなくても)。

確定的な順序が必要な場合は、最も内側の問合せにORDER BY句を追加する必要があります。





sql-server-2000