怎样获得这样的结果集呀？！

[TGTechnology]

例如：
STATIONID TIME ICVALUE CVALUE
2520 2003/04/28 22:07:48 -3514 31
2520 2003/04/28 22:13:49 -36 34
2520 2003/04/28 22:41:35 -4248 46
2520 2003/04/28 22:50:28 -3847 1176
2530 2003/04/28 23:02:10 -3208 3
2530 2003/04/28 23:15:10 -57 18
2530 2003/04/28 23:15:57 -3599 1303
2530 2003/04/28 23:20:31 -4436 22
2540 2003/04/28 23:38:05 -4655 425
2540 2003/04/28 23:51:42 -3907 24
2540 2003/04/29 00:02:28 -4228 14
2540 2003/04/28 23:02:28 -4226 12

想得到表集！
STATIONID TIME ICVALUE CVALUE
2520 2003/04/28 22:07:48 -3514 31
2520 2003/04/28 22:13:49 -36 34
2530 2003/04/28 23:02:10 -3208 3
2530 2003/04/28 23:15:10 -57 18
2540 2003/04/28 23:38:05 -4655 425
2540 2003/04/28 23:51:42 -3907 24


怎么做呀？

[chenlh]

得到每个STATIONID的前两条是不?
一条SQL不好实现吧.
我感觉得整个存储过程

• 打赏
• 举报

回复 赞

[zjcxc]

select * from 表 a
where [time] in(select top 2 [time] from 表 where STATIONID=a.STATIONID)

• 打赏
• 举报

回复 赞

[zjcxc]

--测试

--测试数据
create table 表(STATIONID int,TIME datetime,ICVALUE int,CVALUE int)
insert 表 select 2520,'2003/04/28 22:07:48',-3514,31
union all select 2520,'2003/04/28 22:13:49',-36,34
union all select 2520,'2003/04/28 22:41:35',-4248,46
union all select 2520,'2003/04/28 22:50:28',-3847,1176
union all select 2530,'2003/04/28 23:02:10',-3208,3
union all select 2530,'2003/04/28 23:15:10',-57,18
union all select 2530,'2003/04/28 23:15:57',-3599,1303
union all select 2530,'2003/04/28 23:20:31',-4436,22
union all select 2540,'2003/04/28 23:38:05',-4655,425
union all select 2540,'2003/04/28 23:51:42',-3907,24
union all select 2540,'2003/04/29 00:02:28',-4228,14
union all select 2540,'2003/04/28 23:02:28',-4226,12
go

--查询
select * from 表 a
where [time] in(select top 2 [time] from 表 where STATIONID=a.STATIONID)
go

--删除测试
drop table 表

/*--测试结果

STATIONID TIME ICVALUE CVALUE
----------- --------------------------- ----------- ---------
2520 2003-04-28 22:07:48.000 -3514 31
2520 2003-04-28 22:13:49.000 -36 34
2530 2003-04-28 23:02:10.000 -3208 3
2530 2003-04-28 23:15:10.000 -57 18
2540 2003-04-28 23:38:05.000 -4655 425
2540 2003-04-28 23:51:42.000 -3907 24

（所影响的行数为 6 行）
--*/

• 打赏
• 举报

回复 赞

[outwindows]

--用临时表
select *
into #t
from 表
where TIME not in(select min(TIME) from 表 group by STATIONID)
order by STATIONID,TIME

select *
from 表
where TIME in(select min(TIME) from 表 group by STATIONID)
union
select *
from #t
where TIME in(select min(TIME) from #t group by STATIONID)
order by STATIONID,TIME

drop table #t

• 打赏
• 举报

回复 赞

[ctg0116]

select top 2 * from 表 where STATIONID = '2520'
UNION ALL
select top 2 * from 表 where STATIONID = '2530'
UNION ALL
select top 2 * from 表 where STATIONID = '2540'

或者

SELECT * from 表 a where ICVALUE IN(select top 2 ICVALUE from 表 where STATIONID =a.STATIONID )

• 打赏
• 举报

回复 赞

[internetcsdn]

说一下你的规则好吗

• 打赏
• 举报

回复 赞

[internetcsdn]

看明白了

• 打赏
• 举报

回复 赞