select ID, A, B, 合计=A+B, 名次=(select count(1)+1 from tb where A+B>t.A+t.B) from tb t
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[A] int,[B] int) insert [tb] select 1,1,1 union all select 2,2,2 union all select 3,3,3
---查询--- select ID, A, B, 合计=A+B, 名次=(select count(1)+1 from tb where A+B>t.A+t.B) from tb t---结果--- ID A B 合计 名次 ----------- ----------- ----------- ----------- ----------- 1 1 1 2 3 2 2 2 4 2 3 3 3 6 1(所影响的行数为 3 行)
declare @t table (id int,a int,b int) insert into @t select 1,1,1 union all select 2,2,2 union all select 3,3,3 select *,合计=(a+b),名次=row_number() over(order by (a+b) desc) from @t order by id /* id a b 合计 名次 ----------- ----------- ----------- ----------- -------------------- 1 1 1 2 3 2 2 2 4 2 3 3 3 6 1(3 行受影响)*/
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id int,a int, b int ) go insert tb SELECT 1, 1, 1 UNION ALL SELECT 2, 2, 2 UNION ALL SELECT 3, 3, 3 go select *, 合计=a+b, 名次=ROW_NUMBER () over (order by a+b desc ) from tb order by id go /*------------ (3 行受影响) id a b 合计 名次 ----------- ----------- ----------- ----------- -------------------- 1 1 1 2 3 2 2 2 4 2 3 3 3 6 1-------*/
select *,合计=a+b,名次=(select count(1) from @tb where a+b>=a.a+a.b) from @tb a/* ID A B 合计 名次 ----------- ----------- ----------- ----------- ----------- 1 1 1 2 3 2 2 2 4 2 3 3 3 6 1(所影响的行数为 3 行) */
--> 测试数据: @s declare @s table (ID int,A int,B int) insert into @s select 1,1,1 union all select 2,2,2 union all select 3,3,3select *,合计=a+b,名次=(select count(1) from @s where a+b>=a.a+a.b) from @s a
名次=(select count(1)+1 from tb where A+B>t.A+t.B) 十年树木,百年树人呵呵 能不能解释一下这句话什么意思 !count(1)+1 什么意思
declare @tb table (id int,a int,b int) insert into @tb select 1,1,1 union all select 2,2,2 union all select 3,3,3 select *,名称=row_number()over(order by 合计 desc) from (select * ,合计=a+b from @tb) a order by id (3 行受影响) id a b 合计 名称 ----------- ----------- ----------- ----------- -------------------- 1 1 1 2 3 2 2 2 4 2 3 3 3 6 1(3 行受影响)
--2005可用 if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[A] int,[B] int) insert [tb] select 1,1,1 union all select 2,2,2 union all select 3,3,3select id,a,b,合计=a+b, 名次=row_number() over( order by (a+b) desc) from tb order by id /*id a b 合计 名次 ----------- ----------- ----------- ----------- -------------------- 1 1 1 2 3 2 2 2 4 2 3 3 3 6 1(3 行受影响)*/
drop table F select ID,A,B,合计,名次=identity(int,1,1) into F from (select ID ,A ,B ,合计=A+B from tb) c order by 合计 desc select * from F
select *, 合计=a+b, 名次=ROW_NUMBER () over (order by a+b desc ) from tb order by id
select *,合计=a+b,名次=(select count(1) from @tb where a+b>=a.a+a.b) from @tb a顶这个
他统计的是2,1,0刚好差1所以加1 如果这样就不用 A+B>=t.A+t.B 加个等于
名次=(select count(1)+1 from tb where A+B>t.A+t.B) 名次=(select count(1) from tb where A+B>=t.A+t.B) 结果一样的
select *, 合计=a+b, 名次=ROW_NUMBER () over (order by a+b desc ) from tb
declare @t table (id int,a int,b int) insert into @t select 1,1,1 union all select 2,2,2 union all select 3,3,3 select *,合计=a+b,名次=row_number() over(order by a+b desc) from @t order by id
ID,
A,
B,
合计=A+B,
名次=(select count(1)+1 from tb where A+B>t.A+t.B)
from
tb t
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[A] int,[B] int)
insert [tb]
select 1,1,1 union all
select 2,2,2 union all
select 3,3,3
---查询---
select
ID,
A,
B,
合计=A+B,
名次=(select count(1)+1 from tb where A+B>t.A+t.B)
from
tb t---结果---
ID A B 合计 名次
----------- ----------- ----------- ----------- -----------
1 1 1 2 3
2 2 2 4 2
3 3 3 6 1(所影响的行数为 3 行)
insert into @t
select 1,1,1 union all
select 2,2,2 union all
select 3,3,3
select *,合计=(a+b),名次=row_number() over(order by (a+b) desc) from @t order by id
/*
id a b 合计 名次
----------- ----------- ----------- ----------- --------------------
1 1 1 2 3
2 2 2 4 2
3 3 3 6 1(3 行受影响)*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int,a int, b int )
go
insert tb SELECT
1, 1, 1 UNION ALL SELECT
2, 2, 2 UNION ALL SELECT
3, 3, 3
go
select *,
合计=a+b,
名次=ROW_NUMBER () over (order by a+b desc )
from tb
order by id
go
/*------------
(3 行受影响)
id a b 合计 名次
----------- ----------- ----------- ----------- --------------------
1 1 1 2 3
2 2 2 4 2
3 3 3 6 1-------*/
insert @tb select 1, 1, 1
insert @tb select 2 ,2 ,2
insert @tb select 3, 3, 3
select *,合计=a+b,名次=(select count(1) from @tb where a+b>=a.a+a.b) from @tb a/*
ID A B 合计 名次
----------- ----------- ----------- ----------- -----------
1 1 1 2 3
2 2 2 4 2
3 3 3 6 1(所影响的行数为 3 行)
*/
declare @s table (ID int,A int,B int)
insert into @s
select 1,1,1 union all
select 2,2,2 union all
select 3,3,3select *,合计=a+b,名次=(select count(1) from @s where a+b>=a.a+a.b) from @s a
insert into @tb select 1,1,1
union all select 2,2,2
union all select 3,3,3
select *,名称=row_number()over(order by 合计 desc) from
(select * ,合计=a+b from @tb) a
order by id (3 行受影响)
id a b 合计 名称
----------- ----------- ----------- ----------- --------------------
1 1 1 2 3
2 2 2 4 2
3 3 3 6 1(3 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[A] int,[B] int)
insert [tb]
select 1,1,1 union all
select 2,2,2 union all
select 3,3,3select id,a,b,合计=a+b,
名次=row_number() over( order by (a+b) desc)
from tb order by id
/*id a b 合计 名次
----------- ----------- ----------- ----------- --------------------
1 1 1 2 3
2 2 2 4 2
3 3 3 6 1(3 行受影响)*/
drop table F
select ID,A,B,合计,名次=identity(int,1,1) into F from
(select ID ,A ,B ,合计=A+B from tb) c order by 合计 desc
select * from F
select *,
合计=a+b,
名次=ROW_NUMBER () over (order by a+b desc )
from tb
order by id
名次=(select count(1) from tb where A+B>=t.A+t.B)
结果一样的
from tb
insert into @t
select 1,1,1 union all
select 2,2,2 union all
select 3,3,3 select *,合计=a+b,名次=row_number() over(order by a+b desc) from @t order by id