更新：改一次表明，每票货的时间可能不按顺序始终。
这里是我的输入
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)分别列和放置在开始/结束。 我知道这可以用多个查询，而很容易做到，但我想看看是否一个选择查询可以做到这一点。
谢谢！
我想，你就可以做到这一点的唯一方法是使用子查询。
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函数砍时间列，让您精确的输出。
使用公用表表达式（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
该SeqMinMax CTE为每个“shipment_id”选择最小值和最大值“stop_seq”值，并且查询的其余部分然后生成这些值从表中“测试”检索相关联的时间。
CTE的支持SQL Server 2005的（并且是SQL：2003标准的特点 - 没有微软的“发明”，真的）。
渣
我是正确的，你要在第一时间，而不是“分”的时间思考，而上一次的序列，而不是“最大”时间？
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
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
我建议你把ROW_NUMBER和枢纽的优势。 这可能会显得凌乱，但我认为这将表现良好，而且它更适用于各种假设。 例如，它不认为最新的日期时间值对应于给定的货物最大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
是不是真的需要枢轴，但它很方便。 但是，请注意，PIVOT表达式中的MAX没有做任何有用的东西。 还有每个标签只有一个[时间]值，那么MIN会很好的工作。 语法要求在这个位置上的聚合函数。
附录：这里的，如果你有一台出货量可能比使用MIN和MAX更有效CptSkippy的解决方案的适应性：
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];
