Sql 获取所有用户的第一次和最后一次发货以及到达日期

select NationalID,
    min(shippmetdate) as first_shippment,
    min(arrivalDate) as arrival_first,
    max(shippmetdate) as last_shippment,
    max(arrivalDate)as arrival_last
from (
    select NationalID, shippmetdate, arrivalDate,
    ROW_NUMBER() over (partition by NationalID order by shippmetdate) as seq1, 
    ROW_NUMBER() over (partition by NationalID order by arrivalDate) as seq2 
    from shippment
) t 
group by NationalID

SELECT
  NationalID,
  first_shipment,
  (SELECT arrivalDate FROM shippment WHERE NationalID = t.NationalID AND shippmetdate = t.first_shipment) AS arrival_first,
  last_shipment,
  (SELECT arrivalDate FROM shippment WHERE NationalID = t.NationalID AND shippmetdate = t.last_shipment) AS arrival_last
FROM (
  SELECT
    NationalID,
    MIN(shippmetdate) AS first_shipment,
    MAX(shippmetdate) AS last_shipment
  FROM shippment
  GROUP BY NationalID
) t;

Sql相关问答推荐

SQL查询以创建手头的流动余额？

如何退回当年的所有参赛作品？""

SQL使最终结果显示两个表后的所有数据(匹配和不匹配)？

从以前的非空值行中获取值

使用Mac日志(log)时间找出SQL中的好小时和坏小时

通过之前的连接-这是Oracle的错误吗？

正在try 从SQL获取最新的ID和一个唯一名称

对多个条件的SQL进行排名

如何在AWS Athena中 Select JSON数组的最后一个元素？

排除具有部分匹配条件的记录

Postgresql - 如何根据阈值计算累积和

根据不同日期标准分配组的逻辑

汇总具有连续日期范围的行

查询以查找今天和昨天的数据之间的差异以及伪列

SQL获取两个日期范围之间的计数

根据条件列出不同的值

SQL Server Where 条件

如何在 Oracle 中获取此变量的值？

在sql server中创建唯一标识符列

SQL：有没有办法根据另一列的数据细节过滤和形成另一列？