问题补充：去掉其他相似数据，保留最大时间数据
- SQL code
create table tb(id int,date datetime,dvalue int)goinsert into tb select 1,'2011-12-30 19:29:00',1union all select 1,'2011-12-30 19:36:00',1union all select 1,'2011-12-30 20:00:00',1union all select 2,'2011-12-30 20:29:00',3union all select 2,'2011-12-30 20:31:00',2union all select 3,'2011-12-30 21:00:00',5union all select 4,'2011-12-30 22:00:00',6union all select 5,'2011-12-30 23:00:00',7这里需要精确到分钟，取分钟最大值；/*-------显示结果-------1,'2011-12-30 19:36:00',11,'2011-12-30 20:00:00',12,'2011-12-30 20:31:00',23,'2011-12-30 21:00:00',54,'2011-12-30 22:00:00',65,'2011-12-30 23:00:00',7*/
[解决办法]
- SQL code
select * from tb t where not exists (select 1 from tb where id=t.id and datediff(hour,date,t.date)=0 and date>t.date)
[解决办法]
以什么分组不是很名确
取分钟最大值,那应该的结果是:
/*-------显示结果-------
1,'2011-12-30 20:00:00',1
2,'2011-12-30 20:31:00',2
3,'2011-12-30 21:00:00',5
4,'2011-12-30 22:00:00',6
5,'2011-12-30 23:00:00',7
*/
还是:
/*-------显示结果-------
1,'2011-12-30 19:36:00',1
2,'2011-12-30 20:31:00',2
3,'2011-12-30 21:00:00',5
4,'2011-12-30 22:00:00',6
5,'2011-12-30 23:00:00',7
*/
[解决办法]
- SQL code
create table tb(id int,date datetime,dvalue int)goinsert into tb select 1,'2011-12-30 19:29:00',1union all select 1,'2011-12-30 19:36:00',1union all select 1,'2011-12-30 20:00:00',1union all select 2,'2011-12-30 20:29:00',3union all select 2,'2011-12-30 20:31:00',2union all select 3,'2011-12-30 21:00:00',5union all select 4,'2011-12-30 22:00:00',6union all select 5,'2011-12-30 23:00:00',7goselect t.* from tb t where not exists(select 1 from tb where id = t.id and date > t.date) order by t.id/*id date dvalue ----------- ------------------------------------------------------ ----------- 1 2011-12-30 20:00:00.000 12 2011-12-30 20:31:00.000 23 2011-12-30 21:00:00.000 54 2011-12-30 22:00:00.000 65 2011-12-30 23:00:00.000 7（所影响的行数为 5 行）*/select t.* from tb t where date = (select max(date) from tb where id = t.id) order by t.id/*id date dvalue ----------- ------------------------------------------------------ ----------- 1 2011-12-30 20:00:00.000 12 2011-12-30 20:31:00.000 23 2011-12-30 21:00:00.000 54 2011-12-30 22:00:00.000 65 2011-12-30 23:00:00.000 7（所影响的行数为 5 行）*/drop table tb