表T的数据如下:
sName dDate
a 2008-10-07
a 2008-10-06
b 2008-10-06
b 2008-10-06
c 2008-10-07
d 2008-10-06
c 2008-10-01
...
以此类推,假如该表记录为n。现在,需要取出dDate最近的10个不重复的sName值。
sName dDate
a 2008-10-07
a 2008-10-06
b 2008-10-06
b 2008-10-06
c 2008-10-07
d 2008-10-06
c 2008-10-01
...
以此类推,假如该表记录为n。现在,需要取出dDate最近的10个不重复的sName值。
(select Max(dDate) from tab as tt2 where tt1.sName=tt2.sName)
as dDate from tab as tt1试试看。
a 2008-10-7
a 2008-10-6
b 2008-10-6
b 2008-10-6
c 2008-10-7
d 2008-10-8
d 2008-10-9
e 2008-10-6
f 2008-10-7
g 2008-10-7
g 2008-10-6
i 2008-10-6
h 2008-10-7
h 2008-10-7
k 2008-10-9
k 2008-10-6
y 2008-10-7
o 2008-10-9
n 2008-10-6
楼上的TOP只能用在SQL SERVER数据库中~~
ORA写法如下:
select distinc sName from
(select sName,dense_rank() over( order by dDate desc) rn
from T)
where rn <=10
(select sName,dDate,ROW_NUMBER() over( order by dDate desc) rn
from (select sName,max(dDate) dDate from T group by sName))
where rn <=10
刚才的有点错
Declare @T table(SName varchar(10),dDate datetime)
insert into @t
select 'a','2008-10-07'
union all Select 'a', '2008-10-06'
union all Select 'b', '2008-10-06'
union all Select 'b', '2008-10-09'
union all Select 'c', '2008-10-07'
union all Select 'd', '2008-10-06'
union all Select 'c', '2008-10-01'
union all Select 'r', '2008-10-06'
union all Select 'z', '2008-10-07'
union all Select 'k', '2008-10-06'
union all Select 'j', '2008-10-01'
union all Select 'i', '2008-10-06'
union all Select 'h', '2008-10-07'
union all Select 'h', '2008-10-08'
union all Select 'g', '2008-10-06'
union all Select 'f', '2008-10-01'
union all Select 'g', '2008-10-09'
union all Select 'f', '2008-10-04'
select * from @Tselect top 10 Sname,Max(dDate)as dDate from @T group by Sname
SName dDate
---------- ------------------------------------------------------
a 2008-10-07 00:00:00.000
a 2008-10-06 00:00:00.000
b 2008-10-06 00:00:00.000
b 2008-10-09 00:00:00.000
c 2008-10-07 00:00:00.000
d 2008-10-06 00:00:00.000
c 2008-10-01 00:00:00.000
r 2008-10-06 00:00:00.000
z 2008-10-07 00:00:00.000
k 2008-10-06 00:00:00.000
j 2008-10-01 00:00:00.000
i 2008-10-06 00:00:00.000
h 2008-10-07 00:00:00.000
h 2008-10-08 00:00:00.000
g 2008-10-06 00:00:00.000
f 2008-10-01 00:00:00.000
g 2008-10-09 00:00:00.000
f 2008-10-04 00:00:00.000(所影响的行数为 18 行)Sname dDate
---------- ------------------------------------------------------
a 2008-10-07 00:00:00.000
b 2008-10-09 00:00:00.000
c 2008-10-07 00:00:00.000
d 2008-10-06 00:00:00.000
f 2008-10-04 00:00:00.000
g 2008-10-09 00:00:00.000
h 2008-10-08 00:00:00.000
i 2008-10-06 00:00:00.000
j 2008-10-01 00:00:00.000
k 2008-10-06 00:00:00.000(所影响的行数为 10 行)
已测试过,给分吧
from T
group by sName
order by dDate desc