如下数据,要求获取相同TID下的,不同ID的数据,其最大的最大值(MAXVALUE)和此时的时间,最小的最小值(MINVALUE)和此时的时间源数据
ID TID MAXVALUE MAXTIME MINVALUE MINTIME
1 1 2.1 2009-09-09 10:10:11 1.1 2009-09-09 10:12:01
1 1 2.7 2009-09-09 10:13:12 1.2 2009-09-09 10:15:20
1 1 2.8 2009-09-09 10:27:33 1.2 2009-09-09 10:26:00
2 1 3.0 2009-09-09 10:09:08 1.7 2009-09-09 10:10:10
2 1 3.7 2009-09-09 10:20:17 1.3 2009-09-09 19:10:11
结果
ID TID MAX(MAXVALUE) MAXTIME MIN(MINVALUE) MINTIME
1 1 2.8 2009-09-09 10:27:33 1.1 2009-09-09 10:12:01
2 1 3.7 2009-09-09 10:20:17 1.3 2009-09-09 19:10:11
ID TID MAXVALUE MAXTIME MINVALUE MINTIME
1 1 2.1 2009-09-09 10:10:11 1.1 2009-09-09 10:12:01
1 1 2.7 2009-09-09 10:13:12 1.2 2009-09-09 10:15:20
1 1 2.8 2009-09-09 10:27:33 1.2 2009-09-09 10:26:00
2 1 3.0 2009-09-09 10:09:08 1.7 2009-09-09 10:10:10
2 1 3.7 2009-09-09 10:20:17 1.3 2009-09-09 19:10:11
结果
ID TID MAX(MAXVALUE) MAXTIME MIN(MINVALUE) MINTIME
1 1 2.8 2009-09-09 10:27:33 1.1 2009-09-09 10:12:01
2 1 3.7 2009-09-09 10:20:17 1.3 2009-09-09 19:10:11
from tb
group by id,tid
好像理解错了啊
他是要最大MAXVALUE时对应的时间 以及 最小值时对应的时间
a.minvalue,c.mintime
from(select id,tid,max(maxvalue) as maxvalue,
min(minvalue) as minvalue
from tb group by id,tid) as a
join tb as b
on a.id=b.id and a.tid = b.tid
and a.maxvalue = b.maxvalue
join tb as c
on a.id=c.id and a.tid =c.tid
and a.minvalue=c.minvalue
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-28 18:02:05
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (ID int,TID int,MAXVALUE numeric(2,1),MAXTIME datetime,MINVALUE numeric(2,1),MINTIME datetime)
INSERT INTO @tb
SELECT 1,1,2.1,'2009-09-09 10:10:11',1.1,'2009-09-09 10:12:01' UNION ALL
SELECT 1,1,2.7,'2009-09-09 10:13:12',1.2,'2009-09-09 10:15:20' UNION ALL
SELECT 1,1,2.8,'2009-09-09 10:27:33',1.2,'2009-09-09 10:26:00' UNION ALL
SELECT 2,1,3.0,'2009-09-09 10:09:08',1.7,'2009-09-09 10:10:10' UNION ALL
SELECT 2,1,3.7,'2009-09-09 10:20:17',1.3,'2009-09-09 19:10:11'--SQL查询如下:select a.id,a.tid,a.maxvalue,b.maxtime,
a.minvalue,c.mintime
from(select id,tid,max(maxvalue) as maxvalue,
min(minvalue) as minvalue
from @tb group by id,tid) as a
join @tb as b
on a.id=b.id and a.tid = b.tid
and a.maxvalue = b.maxvalue
join @tb as c
on a.id=c.id and a.tid =c.tid
and a.minvalue=c.minvalue/*
id tid maxvalue maxtime minvalue mintime
----------- ----------- --------------------------------------- ----------------------- --------------------------------------- -----------------------
1 1 2.8 2009-09-09 10:27:33.000 1.1 2009-09-09 10:12:01.000
2 1 3.7 2009-09-09 10:20:17.000 1.3 2009-09-09 19:10:11.000(2 行受影响)
*/
--测试数据
declare @table table (ID int,TID int,MAXVALUE dec(18,2),MAXTIME nvarchar(19),MINVALUE dec(18,2),MINTIME nvarchar(19))
insert into @table
select 1,1,2.1,'2009-09-09 10:10:11',1.1 ,'2009-09-09 10:12:01' union all
select 1,1,2.7,'2009-09-09 10:13:12',1.2 ,'2009-09-09 10:15:20' union all
select 1,1,2.8,'2009-09-09 10:27:33',1.2 ,'2009-09-09 10:26:00' union all
select 2,1,3.0,'2009-09-09 10:09:08',1.7 ,'2009-09-09 10:10:10' union all
select 2,1,3.7,'2009-09-09 10:20:17',1.3 ,'2009-09-09 19:10:11' --查询
select t.id,t.tid,t.MAXVALUE,r.MAXTIME,t.MINVALUE,h.MINTIME
from (select id,tid,max(MAXVALUE) as MAXVALUE ,min(MINVALUE) as MINVALUE from @table
group by id,tid) t join @table r
on t.id = r.id and t.MAXVALUE = r.MAXVALUE
join @table h on t.id = h.id and t.MINVALUE = h.MINVALUE
--结果
-----------------------------
1 1 2.80 2009-09-09 10:27:33 1.10 2009-09-09 10:12:01
2 1 3.70 2009-09-09 10:20:17 1.30 2009-09-09 19:10:11