--建立测试环境
set nocount on
create table test(A varchar(20),B varchar(20),C float)
insert into test select '2','150055','2239'
insert into test select '2','150056','2088'
insert into test select '2','143831','1341'
insert into test select '2','143830','800'
insert into test select '2','144298','799'
insert into test select '2','103018','638'
insert into test select '2','150054','542'
insert into test select '2','150007','504'
insert into test select '2','125301','460'
insert into test select '2','105004','449'
insert into test select '1','143831','3235'
insert into test select '1','125465','1244'
insert into test select '1','104002','1158'
insert into test select '1','111266','1081'
insert into test select '1','101065','963'
insert into test select '1','173091','906.8144'
insert into test select '1','143716','799'
insert into test select '1','144298','788'
insert into test select '1','150054','761'
insert into test select '1','104026','760'
go
--测试
select * from(
select *,(select count(*) from test where A=a.a and c>a.c)+1 as rn from test a)t
where rn<=10drop table test/*
A B C rn
-------------------- -------------------- ---------------------- -----------
2 150055 2239 1
2 150056 2088 2
2 143831 1341 3
2 143830 800 4
2 144298 799 5
2 103018 638 6
2 150054 542 7
2 150007 504 8
2 125301 460 9
2 105004 449 10
1 143831 3235 1
1 125465 1244 2
1 104002 1158 3
1 111266 1081 4
1 101065 963 5
1 173091 906.8144 6
1 143716 799 7
1 144298 788 8
1 150054 761 9
1 104026 760 10*/
set nocount on
create table test(A varchar(20),B varchar(20),C float)
insert into test select '2','150055','2239'
insert into test select '2','150056','2088'
insert into test select '2','143831','1341'
insert into test select '2','143830','800'
insert into test select '2','144298','799'
insert into test select '2','103018','638'
insert into test select '2','150054','542'
insert into test select '2','150007','504'
insert into test select '2','125301','460'
insert into test select '2','105004','449'
insert into test select '1','143831','3235'
insert into test select '1','125465','1244'
insert into test select '1','104002','1158'
insert into test select '1','111266','1081'
insert into test select '1','101065','963'
insert into test select '1','173091','906.8144'
insert into test select '1','143716','799'
insert into test select '1','144298','788'
insert into test select '1','150054','761'
insert into test select '1','104026','760'
go
--测试
select * from(
select *,(select count(*) from test where A=a.a and c>a.c)+1 as rn from test a)t
where rn<=10drop table test/*
A B C rn
-------------------- -------------------- ---------------------- -----------
2 150055 2239 1
2 150056 2088 2
2 143831 1341 3
2 143830 800 4
2 144298 799 5
2 103018 638 6
2 150054 542 7
2 150007 504 8
2 125301 460 9
2 105004 449 10
1 143831 3235 1
1 125465 1244 2
1 104002 1158 3
1 111266 1081 4
1 101065 963 5
1 173091 906.8144 6
1 143716 799 7
1 144298 788 8
1 150054 761 9
1 104026 760 10*/
解决方案 »
- 列转行问题(紧急)!!!!!!!!
- [求助]只有10分了,两个sql语句的查询结果为啥不一样??
- MS-SQL Server 求救!
- 一个SQL的普通问题,请高手指教一下!解决即结分
- 急 在线等
- 我建立的SQL 警报总是没有发生响应, 请问怎样才能使它发挥作用?
- 取字段的问题,大家帮忙看看有什么好办法?
- 两个Numeric类型的字段怎样按要求和字符串连接呢?
- csdn的网管向法挺多,什么不让up,gz了,现在又不让连续发3次言,解决这个问题好像还真有点难度,可见网管的智慧,但是为什么他不想一想,难道一个人只能有一个账号吗?又可见网管的智慧
- 哪里可以找到DB2的中文或英文帮助文档
- 求一段统计SQL并排序语句,内详!
- 急!!!Sql server添加用户
select * from(select ,Row_number() over(partition by b order c) rank from tb) T where rank<=10
INSERT @TB
SELECT 2, 150055, 2239 UNION ALL
SELECT 2, 150056, 2088 UNION ALL
SELECT 2, 143831, 1341 UNION ALL
SELECT 2, 143830, 800 UNION ALL
SELECT 2, 144298, 799 UNION ALL
SELECT 2, 103018, 638 UNION ALL
SELECT 2, 150054, 542 UNION ALL
SELECT 2, 150007, 504 UNION ALL
SELECT 2, 125301, 460 UNION ALL
SELECT 2, 105004, 449 UNION ALL
SELECT 1, 143831, 3235 UNION ALL
SELECT 1, 125465, 1244 UNION ALL
SELECT 1, 104002, 1158 UNION ALL
SELECT 1, 111266, 1081 UNION ALL
SELECT 1, 101065, 963 UNION ALL
SELECT 1, 173091, 906.8144 UNION ALL
SELECT 1, 143716, 799 UNION ALL
SELECT 1, 144298, 788 UNION ALL
SELECT 1, 150054, 761 UNION ALL
SELECT 1, 104026, 760SELECT *
FROM (
SELECT *,RANK=RANK() OVER (PARTITION BY A ORDER BY C DESC)
FROM @TB) T
WHERE RANK<=10
/*(20 row(s) affected)
A B C RANK
----------- ----------- ---------------------- --------------------
1 143831 3235 1
1 125465 1244 2
1 104002 1158 3
1 111266 1081 4
1 101065 963 5
1 173091 906.8144 6
1 143716 799 7
1 144298 788 8
1 150054 761 9
1 104026 760 10
2 150055 2239 1
2 150056 2088 2
2 143831 1341 3
2 143830 800 4
2 144298 799 5
2 103018 638 6
2 150054 542 7
2 150007 504 8
2 125301 460 9
2 105004 449 10(20 row(s) affected)
*/
select a,b,c from (select row_number() over(partition by a order by b desc) rank,* from tb) T
where rank<=10
create table test(A varchar(20),B varchar(20),C float)
insert into test select '2','150055','2239'
insert into test select '2','150056','2088'
insert into test select '2','143831','1341'
insert into test select '2','143830','800'
insert into test select '2','144298','799'
insert into test select '2','103018','638'
insert into test select '2','150054','542'
insert into test select '2','150007','504'
insert into test select '2','125301','460'
insert into test select '2','105004','449'
insert into test select '1','143831','3235'
insert into test select '1','125465','1244'
insert into test select '1','104002','1158'
insert into test select '1','111266','1081'
insert into test select '1','101065','963'
insert into test select '1','173091','906.8144'
insert into test select '1','143716','799'
insert into test select '1','144298','788'
insert into test select '1','150054','761'
insert into test select '1','104026','760'select * from(select *,Row_number() over(partition by A order by c) rank from test) T where rank<=10A B C rank
-------------------- -------------------- ---------------------- --------------------
1 104026 760 1
1 150054 761 2
1 144298 788 3
1 143716 799 4
1 173091 906.8144 5
1 101065 963 6
1 111266 1081 7
1 104002 1158 8
1 125465 1244 9
1 143831 3235 10
2 105004 449 1
2 125301 460 2
2 150007 504 3
2 150054 542 4
2 103018 638 5
2 144298 799 6
2 143830 800 7
2 143831 1341 8
2 150056 2088 9
2 150055 2239 10(20 行受影响)
select * from
(
SELECT *,(select count(1) from tableName where a.A=A) count
FROM tableName a
) a where count<=10
--刚刚笔误
select * from
(
SELECT *,(select count(1) from tableName where a.A=A and c<a.c) count
FROM tableName a
) a where count<=10
select a,
b,
c,
rank=(select count(1)+1 from test where t.a=a and t.c>c)
from test t A B C rank
-------------------- -------------------- ---------------------- --------------------
1 104026 760 1
1 150054 761 2
1 144298 788 3
1 143716 799 4
1 173091 906.8144 5
1 101065 963 6
1 111266 1081 7
1 104002 1158 8
1 125465 1244 9
1 143831 3235 10
2 105004 449 1
2 125301 460 2
2 150007 504 3
2 150054 542 4
2 103018 638 5
2 144298 799 6
2 143830 800 7
2 143831 1341 8
2 150056 2088 9
2 150055 2239 10
select * from #t A left join (
select id row_number over(partition by id order by id) Cnt from #t
where Cnt <= 10
group by id) B
on A.id = B.id