평가판 - windows sql server




SQL Server:첫 번째 행에 참여하는 방법 (7)

나는 구체적인, 그러나 가정적인 예를 사용할 것이다.

각 광고 주문 에는 일반적으로 하나의 광고 항목 만 있습니다.

명령:

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

그러나 그것은 오류를 준다 :

'Orders'열 또는 접두사가 쿼리에 사용 된 테이블 이름이나 별칭 이름과 일치하지 않습니다.

아마 내부 select가 외부 테이블을 보지 않기 때문입니다.


, 공통 테이블 표현식을 사용하는 다른 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

@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

때로는 더 나은 성능을 제공하는 쿼리 를 테스트해야합니다 .


나는이 질문이 얼마전에 답변을 받았음을 알고 있지만 큰 데이터 세트를 다룰 때 중첩 된 쿼리는 비용이 많이 든다. 다음은 반환되는 각 행 대신 중첩 된 쿼리가 한 번만 실행되는 다른 솔루션입니다.

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

상관 관계 서브 쿼리는 외부 쿼리에 의존하는 서브 쿼리입니다. 이것은 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, 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 바꿀 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 절을 추가해야합니다.





sql-server-2000