SQL每日一题(20200512)

引自：https://mp.weixin.qq.com/s?__biz=MzA3MTg4NjY4Mw==&mid=2457305414&idx=4&sn=1a4c519dbcda32a6b60bbf35f2d606a2&chksm=88a59372bfd21a645e97e47f0f80da2b8f0d67a1decc9b697ebf1631edda558b12718df9bba8&mpshare=1&scene=1&srcid=&sharer_sharetime=1589292371547&sharer_shareid=18c156b37f741bf9989098e28bf33e09&exportkey=AWUcwpdGvqyMje56KR4vgto%3D&pass_ticket=bSoGZbthVR47utl6tBR2%2FQnaJqNtNZm3kvRX0wP6B6En0Vw1yyhVTArwlK%2BvCBqu#rd

感谢出题者，如有冒犯，请与我联系，希望和大家一起学习交流。(目前使用oracle数据库环境编写sql)

 

题目

有如下一张表Orders

 

 

查询出每个发货单号(shipid)，最早付款时间(paydate)和最小付款单号(payno) 结果如下：

 

 

该如何写这个SQL？

 

读者可以试着自己思考写下，再往下翻... 如有不同解题方式，大家一起交流。

 

 

 

 

 

 

sys@WIL> with t AS
  2      (SELECT '1001' shipid,date'2019-11-02' paydate,'5' payno
  3      FROM dual
  4      UNION ALL
  5      SELECT '1001' shipid,date'2019-11-02' paydate,'3' payno
  6      FROM dual
  7      UNION ALL
  8      SELECT '1001' shipid,date'2019-11-03' paydate,'1' payno
  9      FROM dual
 10      UNION ALL
 11      SELECT '1001' shipid,date'2019-11-03' paydate,'3' payno
 12      FROM dual
 13      UNION ALL
 14      SELECT '1002' shipid,date'2019-11-09' paydate,'1' payno
 15      FROM dual
 16      UNION ALL
 17      SELECT '1002' shipid,date'2019-11-09' paydate,'4' payno
 18      FROM dual
 19      UNION ALL
 20      SELECT '1002' shipid,date'2019-11-08' paydate,'3' payno
 21      FROM dual
 22      UNION ALL
 23      SELECT '1002' shipid,date'2019-11-08' paydate,'2' payno
 24      FROM dual )
 25  SELECT shipid,
 26  paydate,
 27  payno from
 28      (SELECT t.*,
 29  row_number() over(partition by t.shipid
 30      ORDER BY  paydate,payno) rn
 31      FROM t)
 32  WHERE rn=1;

SHIP     PAYDATE        PAYNO
----　　----------　　　　-----
1001     2019/11/02    　　3
1002     2019/11/08    　　2