id date name
1 2011-09-09 10:00:00 cxd
2 2011-09-08 10:00:00 cxd
3 2011-09-07 10:00:00 cxd
4 2011-09-08 10:00:00 yejin
5 2011-09-09 10:00:00 yejin
6 2011-09-10 10:00:00 yejin我需要返回的数据是以CXD来分组,时间最早的一条
返回的结果是:
3 2011-09-07 10:00:00 cxd
4 2011-09-08 10:00:00 yejin请教各位高手
1 2011-09-09 10:00:00 cxd
2 2011-09-08 10:00:00 cxd
3 2011-09-07 10:00:00 cxd
4 2011-09-08 10:00:00 yejin
5 2011-09-09 10:00:00 yejin
6 2011-09-10 10:00:00 yejin我需要返回的数据是以CXD来分组,时间最早的一条
返回的结果是:
3 2011-09-07 10:00:00 cxd
4 2011-09-08 10:00:00 yejin请教各位高手
解决方案 »
- 安装完vs2005后,再安装上sql server2005没有management studio 怎么办?
- 关于动态表查询的问题
- SQL查询问题
- 请问高手:两个客户端同时连接数据库服务器,怎样才能做到在一个客户端插入记录时另一个客户端得到插入的信息实时显示这条记录的信息?
- 弱问:如何用sql语句创建具有多个关键字的表阿?
- 数据库中表的字段更新的问题?
- 跨库查询分页
- access和sql server的区别,大家来讨论讨论!!!来者有分!!!!
- 如何知道某个表中是否有 identity 字段?
- ——高分——如何得到这样的数据集??
- 怎么在查询分析器里查看字符串内容
- 数据库中两张表无法建立主外键关系!!!!!!
where not exists(select 1 from tb where t.id = id and t.date<date)
where not exists(select 1 from tb where name=t.name and dt<t.dt)
as
(select rownum=row_number()over(partition by CXD order by [date]),
*
from tb)
select * from tmp
where rownum=1
(
id int,
date datetime,
name varchar(10)
)
insert into tb values(1,'2011-09-09 10:00:00','cxd')
insert into tb values(2,'2011-09-08 10:00:00','cxd')
insert into tb values(3,'2011-09-07 10:00:00','cxd')
insert into tb values(4,'2011-09-08 10:00:00','yejin')
insert into tb values(5,'2011-09-09 10:00:00','yejin')
insert into tb values(6,'2011-09-10 10:00:00','yejin')select id ,date , name from tb t
where not exists(select 1 from tb where t.name = name and t.date>date)
/*
id,date,name
3,2011-09-07 10:00:00.000,cxd
4,2011-09-08 10:00:00.000,yejin(2 行受影响)
修改一下
select t.* from tb t where date = (select min(date) from tb where CXD = t.CXD) order by t.id
select t.* from tb t where not exists (select 1 from tb where CXD = t.CXD and date < t.date) order by t.id