sql max / min查询和数据转换

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

更新：更改一次以显示每个货件的时间可能不是按顺序排列。

here is my input

这是我的意见

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')

the output that i want is below

我想要的输出如下

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

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.

我需要花费每次发货的最小（停止）行的时间和从最大（停止）行到开始/结束的时间。我知道这可以通过多个查询很容易地完成，但我希望看看单个选择查询是否可以做到这一点。

thanks!

谢谢！

6 个解决方案

#1

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

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

您需要使用DATEPART功能来切断时间列以获得准确的输出。

#2

Use a Common Table Expression (CTE) - this works (at least on my SQL Server 2008 test system):

使用公用表表达式（CTE） - 这是有效的（至少在我的SQL Server 2008测试系统上）：

WITH SeqMinMax(SeqID, MinID, MaxID) AS
(
    SELECT Shipment_ID, MIN(stop_seq), MAX(stop_seq)
    FROM test
    GROUP BY Shipment_ID
)
SELECT 
    SeqID 'Shipment_ID',
    (SELECT TIME FROM test 
       WHERE shipment_id = smm.seqid AND stop_seq = smm.minid) 'Start',
    (SELECT TIME FROM test 
       WHERE shipment_id = smm.seqid AND stop_seq = smm.maxid) 'End'
FROM seqminmax smm

The SeqMinMax CTE selects the min and max "stop_seq" values for each "shipment_id", and the rest of the query then builds on those values to retrieve the associated times from the table "test".

SeqMinMax CTE为每个“shipment_id”选择最小和最大“stop_seq”值，然后查询的其余部分构建在这些值上，以从表“test”中检索相关时间。

CTE's are supported on SQL Server 2005 (and are a SQL:2003 standard feature - no Microsoft "invention", really).

SQL Server 2005支持CTE（并且是SQL：2003标准功能 - 实际上没有Microsoft“发明”）。

Marc

渣子

#3

Am I correct in thinking that you want the first time rather than the 'min' time, and the last time in the sequence rather than the 'max' time?

我是否正确地认为你想要的是第一次而不是'min'时间，以及最后一次是在序列而不是'max'时间？

#4

SELECT C.shipment_id, C.start, B2.time AS stop FROM
(    
   SELECT A.shipment_id, B1.time AS start, A.max_stop_seq FROM
   (
      SELECT shipment_id, MIN(stop_seq) as min_stop_seq, MAX(stop_seq) as max_stop_seq 
      FROM test
      GROUP BY shipment_id
   ) AS A

   INNER JOIN 

   (
      SELECT shipment_id, stop_seq, time FROM test
   ) AS B1

   ON A.shipment_id = B1.shipment_id AND A.min_stop_seq = B1.stop_seq
) AS C

INNER JOIN

(
   SELECT shipment_id, stop_seq, time FROM test
) AS B2

ON C.shipment_id = B2.shipment_id AND C.max_stop_seq = B2.stop_seq

#5

select t1.shipment_id, t1.time start, t2.time [end]
from (
    select shipment_id, min(stop_seq) min, max(stop_seq) max
    from test
    group by shipment_id
) a
inner join test t1 on a.shipment_id = t1.shipment_id and a.min = t1.stop_seq 
inner join test t2 on a.shipment_id = t2.shipment_id and a.max = t2.stop_seq

#6

I suggest you take advantage of row_number and PIVOT. This may look messy, but I think it will perform well, and it's more adaptable to various assumptions. For example, it doesn't assume that the latest datetime value corresponds to the largest stop_seq value for a given shipment.

我建议你利用row_number和PIVOT。这可能看起来很混乱，但我认为它会表现良好，并且它更适应各种假设。例如，它不假设最新的datetime值对应于给定货件的最大stop_seq值。

with test_ranked(shipment_id,stop_seq,time,rankup,rankdown) as (
  select
    shipment_id, stop_seq, time,
    row_number() over (
      partition by shipment_id
      order by stop_seq
    ),
    row_number() over (
      partition by shipment_id
      order by stop_seq desc
    )
  from test
), test_extreme_times(shipment_id,tag,time) as (
  select
    shipment_id, 'start', time
  from test_ranked where rankup = 1
  union all
  select
    shipment_id, 'end', time
  from test_ranked where rankdown = 1
)
  select
    shipment_id, [start], [end]
  from test_extreme_times
  pivot (max(time) for tag in ([start],[end])) P
  order by shipment_id;
  go

The PIVOT isn't really needed, but it's handy. However, do note that the MAX inside the PIVOT expression doesn't do anything useful. There's only one [time] value for each tag, so MIN would work just as well. The syntax requires an aggregate function in this position.

PIVOT并不是真的需要，但它很方便。但是，请注意PIVOT表达式中的MAX不会执行任何有用的操作。每个标签只有一个[时间]值，因此MIN也可以正常工作。语法需要此位置的聚合函数。

Addendum: Here's an adaptation of CptSkippy's solution that may be more efficient than using MIN and MAX if you have a shipments table:

附录：这是CptSkippy解决方案的改编版，如果您有货运表，可能比使用MIN和MAX更有效：

SELECT shipment_id
    , (SELECT TOP 1 time 
        FROM test AS [b] 
        WHERE b.shipment_id = a.shipment_id 
        ORDER BY stop_seq ASC) AS [start]
    , (SELECT TOP 1 time 
        FROM test AS [b] 
        WHERE b.shipment_id = a.shipment_id 
        ORDER BY stop_seq DESC) AS [end]
FROM shipments_table AS [a];

#1