sql max/min 查询和数据转换 [英] sql max/min query and data transformation

问题描述

更新:更改一次以显示每批货物的时间可能并非始终按顺序排列.

update: changed one time to show that the times per shipment may not be in sequential order always.

这是我的输入

create table test
(
    shipment_id int,
    stop_seq tinyint,
    time datetime
)

insert into test values (1,1,'2009-8-10 8:00:00')
insert into test values (1,2,'2009-8-10 9:00:00')
insert into test values (1,3,'2009-8-10 10:00:00')
insert into test values (2,1,'2009-8-10 13:00:00')
insert into test values (2,2,'2009-8-10 14:00:00')
insert into test values (2,3,'2009-8-10 20:00:00')
insert into test values (2,4,'2009-8-10 18:00:00')

我想要的输出在下面

shipment_id  start    end
-----------  -----    ---
     1        8:00    10:00
     2        13:00   18:00

我需要从每次发货的 min(stop) 行和 max(stop) 行中获取时间并分别放在 start/end 中.我知道这可以通过多个查询轻松完成，但我想看看单个选择查询是否可以做到这一点.

i need to take the time from the min(stop) row for each shipment and the time from the max(stop) row and place in start/end respectively. i know this can be done with multiple queries rather easily but i am looking to see if a single select query can do this.

谢谢！

推荐答案

我认为您能够做到这一点的唯一方法是使用子查询.

I think the only way you'll be able to do it is with sub-queries.

SELECT shipment_id
    , (SELECT TOP 1 time 
        FROM test AS [b] 
        WHERE b.shipment_id = a.shipment_id 
        AND b.stop_seq = MIN(a.stop_seq)) AS [start]
    , (SELECT TOP 1 time 
        FROM test AS [b] 
        WHERE b.shipment_id = a.shipment_id 
        AND b.stop_seq = MAX(a.stop_seq)) AS [end]
FROM test AS [a]
GROUP BY shipment_id

您需要使用 DATEPART 函数来切分时间列以获得准确的输出.

You'll need to use the DATEPART function to chop up the time column to get your exact output.

这篇关于sql max/min 查询和数据转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！