求教分类语句
col1 datetime col2 col3 col4
搜索2007-01-17899273897433898170
社区2007-01-17880617878828879561
....................................................
中国2007-01-18239731239255239580
美国2007-01-18136138135834136063
....................................................
生活2007-01-19131117130830131061
女人2007-01-19103850103572103794
....................................................
我想求出每天col2列排名前十位的行，多谢指教。
[解决办法]
select top 10 * from table order by col4 desc
[解决办法]
select * from T as tmp
where (select count(*) from T where datetime=tmp.datetime and col2> tmp.col2) <10
order by col2
[解决办法]
select top 10 * from T
where convert(char(10), [datetime], 120)=convert(char(10), getdate(), 120)
order by col2 desc
[解决办法]
select * from tablename a
where col1 in (
select top 10 col1
from tablename
where [datetime]=a.[datetime]
)
[解决办法]
--try
select * from T as tmp
where (select count(*) from T where col1=tmp.col1 and col2> tmp.col2) <10
[解决办法]
pbsh(大航海家) ( ) 信誉：99 Blog 2007-01-30 11:13:12 得分: 0
没说清，不好意思。
按col1分类，每个col1的前十位。
-----------------------------------
例子给出的可不是按col1分类，按按col1分类的语句如下：
select * from tablename a
where col2 in (
select top 10 col2
from tablename
where [datetime]=a.[datetime]
and col1=a.col1
order by col2 desc
)
上面的更正：
select * from tablename a
where col1 in (
select top 10 col1
from tablename
where [datetime]=a.[datetime]
order by col2 desc
)
