SQL Server Use one join for SQL query optimization

smtd7mpg 于 2023-03-22 发布在其他

关注(0)|答案(2)|浏览(151)

I am using a SQL Server database and pulling several columns from subqueries (Invoice date, Est ship date, etc). How to replace multiple left joins with just one join and use the case statement in the Select statement? Or is there a better solution? Thanks in advance.

SELECT Orders.ID
       , InvDate.Event_Time     AS Invoice_date
       , EstShipDate.Event_Time AS Est_Ship_date
       , DelDate.Event_Time     AS Del_date
       , ReturnDate.Event_Time  AS Return_date 
FROM  
    Orders
LEFT JOIN 
    (SELECT EventType, OrderID, Event_Time 
     FROM Ship_LOG_TIMES) InvDate ON InvDate.OrderID = Order.ID 
                                  AND INvDate.EventType = '1'  --Invoice date
LEFT JOIN 
    (SELECT EventType, OrderID, Event_Time 
     FROM Ship_LOG_TIMES) EstShipDate ON EstShipDate.OrderID = Order.ID 
                                      AND EstShipDate.EventType = '2'      --Est Ship date
LEFT JOIN 
    (SELECT EventType, OrderID, Event_Time 
     FROM Ship_LOG_TIMES) DelDate ON DelDate.OrderID = Order.ID 
                                  AND DelDate.EventType = '4'  -- Delivery date
LEFT JOIN 
    (SELECT EventType, OrderID, Event_Time 
     FROM Ship_LOG_TIMES) ReturnDate ON ReturnDate.OrderID = Order.ID 
                                     AND ReturnDate.EventType = '5'  --Return date'

sql-server

来源：https://stackoverflow.com/questions/75764417/use-one-join-for-sql-query-optimization

2条答案

按热度按时间

mspsb9vt1#

You could try this query

SELECT o.ID
       , MAX(CASE WHEN s.EventType = '1' THEN s.Event_Time END) AS Invoice_date
       , MAX(CASE WHEN s.EventType = '2' THEN s.Event_Time END) AS Est_Ship_date
       , MAX(CASE WHEN s.EventType = '4' THEN s.Event_Time END) AS Del_date
       , MAX(CASE WHEN s.EventType = '5' THEN s.Event_Time END) AS Return_date 
FROM  
    Orders o LEFT JOIN Ship_LOG_TIMES s 
               ON s.OrderID = o.ID AND s.EventType IN ('1','2','4','5')  
GROUP BY o.ID

赞(0）回复(0）举报 2023-03-22

aelbi1ox2#

I would use a sub query like so:

SELECT Orders.ID, AggData.*
FROM Orders
LEFT JOIN (
    SELECT OrderID
         , MAX(CASE WHEN EventType = '1' THEN Event_Time END) AS Invoice_date
         , MAX(CASE WHEN EventType = '2' THEN Event_Time END) AS Est_Ship_date
         , MAX(CASE WHEN EventType = '4' THEN Event_Time END) AS Del_date
         , MAX(CASE WHEN EventType = '5' THEN Event_Time END) AS Return_date 
    FROM Ship_LOG_TIMES
    WHERE EventType IN ('1', '2', '4', '5')
    GROUP BY OrderID
) AS AggData ON Orders.ID = AggData.OrderID

赞(0）回复(0）举报 2023-03-22

我来回答

SQL Server Use one join for SQL query optimization

2条答案

相关问题

热门标签

最新问答