dongpao5658
2014-08-08 13:35
浏览 63
已采纳

SQL Correlated Subquery - MAX所需的帮助

I have two tables, 'backorders' and 'sku'

sku

id | orderdate | sku | expectedship

backorders

orderdate | ordernum | saleschannel | sku | expectedship

In the sku table, is a list of items that have different expected ship dates based on the date of order.

E.g.

1  | Apple  |  01.08.2014  |  19.10.2014
2  | Apple  |  02.08.2014  |  28.10.2014
3  | Pear   |  02.08.2014  |  20.10.2014
4  | Grape  |  02.08.2014  |  22.10.2014

In the backorders table, is a list of items that have been ordered. The Ordernum is the unique reference where multiple items can be ordered.

E.g.

02.08.2014  |  order1  |  a  |  apple
02.08.2014  |  order1  |  a  |  grape
02.08.2014  |  order1  |  a  |  pear
03.08.2014  |  order2  |  c  |  banana

What I am trying to achieve is for each backorder, populate the max expected ship date across all items for that order.
E.g.

02.08.2014  |  order1  |  a  |  apple  |  28.10.2014
02.08.2014  |  order1  |  a  |  grape  |  28.10.2014
02.08.2014  |  order1  |  a  |  pear   |  28.10.2014
03.08.2014  |  order2  |  c  |  banana |  NULL


As you can see above, is order1 shows the maximum/oldest date for all 3 sku's ordered.

The below query shows me the expected ship date for each sku; however cannot work out how to get the max date per order number.

SELECT
    backorders.orderdate,
    backorders.ordernum,
    backorders.saleschannel,
    backorders.sku,
    setup.expectedship
FROM backorders
LEFT OUTER JOIN setup ON backorders.orderdate = setup.orderdate AND backorders.sku =     setup.sku
WHERE (backorders.saleschannel = 'a') 
   OR (backorders.saleschannel ='b') 
   OR (backorders.saleschannel ='c') 
ORDER BY backorders.ordernum DESC



I can also separately manage to identify the max date for each ordernum:

SELECT
    backorders.ordernum,
    MAX(setup.expectedship) AS `MAX(expectedship)`
FROM backorders
INNER JOIN setup ON backorders.sku = setup.sku AND backorders.orderdate = setup.orderdate
WHERE (setup.orderdate = backorders.orderdate) 
  AND (setup.sku = backorders.sku) 
  AND (backorders.saleschannel = 'a') 
  OR  (backorders.saleschannel ='b') 
  OR  (backorders.saleschannel ='c')
GROUP BY backorders.ordernum

Is there a way I can run the first query to identify the expected ship per order line, and then a subquery to update the expected ship across all ordernum to maximum?

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

  • dqz84361326 2014-08-08 13:59
    最佳回答

    to what I was referring in my comment you could just join the two queries together... i would try to replicate this to ensure it works.. but your date formats are off and I don't have the time to format them all.

    SELECT
        t.orderdate,    t.ordernum,
        t.saleschannel, t.sku,
        t1.expectedship
    FROM
    (   SELECT
            b.orderdate,    b.ordernum,
            b.saleschannel, b.sku,
            s.expectedship
        FROM backorders b
        LEFT OUTER JOIN setup s ON b.orderdate = s.orderdate AND b.sku = s.sku
        WHERE b.saleschannel IN('a', 'b', 'c')
        ORDER BY b.ordernum DESC
    )t
    LEFT JOIN
    (   SELECT
            b.ordernum,
            MAX(s.expectedship) AS expectedship
        FROM backorders b
        INNER JOIN setup s ON b.sku = s.sku AND b.orderdate = s.orderdate
        WHERE (s.sku = b.sku) 
          AND b.saleschannel IN('a', 'b', 'c')
        GROUP BY b.ordernum
    )t1 on t1.ordernum = t.ordernum
    order by ordernum;
    

    DEMO

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题