insert into test(username,time1,time2,num)
select 'as1','2010-09-08 10:50','2010-09-08 10:58','10' union
select 'as1','2010-09-08 10:50','2010-09-08 11:58','15' union
select 'as1','2010-09-08 11:30','2010-09-08 12:58','16' union
select 'as1','2010-09-08 11:30','2010-09-08 13:58','8' union
select 'as2','2010-09-08 13:50','2010-09-08 10:58','10' union
select 'as2','2010-09-08 13:50','2010-09-08 11:58','15' union
select 'as2','2010-09-08 11:30','2010-09-08 12:58','16' union
select 'as2','2010-09-08 11:30','2010-09-08 13:58','8' 根据username来先分组,然后取time1最大的那条记录,如果time1有相等的就取 time2较大的那条记录
结果如下
4 as1 2010-09-08 11:30:00.000 2010-09-08 13:58:00.000 8
8 as2 2010-09-08 13:50:00.000 2010-09-08 11:58:00.000 15除了以下这种方法,还有比较简便的写法吗?
select c.* from test c ,
(
select b.username, max(time2)time2 from test b,
(
select username, max(time1) time1 from test
group by username
)a where b.username=a.username and b.time1=a.time1
group by b.username
)d where c.time2=d.time2 and c.username=d.username
select 'as1','2010-09-08 10:50','2010-09-08 10:58','10' union
select 'as1','2010-09-08 10:50','2010-09-08 11:58','15' union
select 'as1','2010-09-08 11:30','2010-09-08 12:58','16' union
select 'as1','2010-09-08 11:30','2010-09-08 13:58','8' union
select 'as2','2010-09-08 13:50','2010-09-08 10:58','10' union
select 'as2','2010-09-08 13:50','2010-09-08 11:58','15' union
select 'as2','2010-09-08 11:30','2010-09-08 12:58','16' union
select 'as2','2010-09-08 11:30','2010-09-08 13:58','8' 根据username来先分组,然后取time1最大的那条记录,如果time1有相等的就取 time2较大的那条记录
结果如下
4 as1 2010-09-08 11:30:00.000 2010-09-08 13:58:00.000 8
8 as2 2010-09-08 13:50:00.000 2010-09-08 11:58:00.000 15除了以下这种方法,还有比较简便的写法吗?
select c.* from test c ,
(
select b.username, max(time2)time2 from test b,
(
select username, max(time1) time1 from test
group by username
)a where b.username=a.username and b.time1=a.time1
group by b.username
)d where c.time2=d.time2 and c.username=d.username
解决方案 »
- 省市区三级联动的JS的在asp里可用,但是在.NET里报错,大家推荐一个好用的省市区三级联动,JS或者控件都行?
- 问一个sql in 的用户,有答案立刻结贴
- 【------------.ashx改后缀------------------】
- 求这个图片界面的里面可以加入什么样的图片,怎么加(asp.net)
- 关于get方法传值
- 有什么办法能防止恶意的注册
- ImageButton对齐问题
- 请问(object sender,ElapesdEventArgs e)各代表什么意思?
- 新手:在页面上要输入日期,并且格式要能设置为yyyy-mm-dd
- 定制一个日历控件的显示日期问题?
- 页面中使用ReportViewer发布到服务器后不能正常使用
- jquery 删除表格行
Select * from (
select rank() over(partition by userName order by time1 desc,time2 desc) as Rows,
* from dbo.test) a
where Rows=1