如何写这个sql 语句,用一个SQL语句写出
比如,有个用户表,里面是有个字段是MONEY
我想 统计出我NAME为我的这个字段在MONEY排行中排第几位.
要求用一个SQL语句写出,排序简单关键是要得出我随遍指定的某一记录在这个排序中处于第几位.
[解决办法]
declare @name varchar(20)
select count(1) from tbl1 where money < (select money from tabl1 where name = @name)
或者
select count(1) from tbl1 a,(select select money from tabl1 where name = @name)b where a.money < b.money
[解决办法]
select [order]
from (
select *,[order]=(select count(*) from t1 b where a.name <b.name) --改为 <
from t1 a)t
where t.name= 'li '
[解决办法]
--lz试试这个
select count(1) as '排行第几 ' from 用户表 a where exists
(select 1 from 用户表 where name= '我 ' and money <=a.money)
[解决办法]
declare @name varchar(10)
set @name = '张三 ' --给变量赋值,作为查询条件的姓名
select count(1)+1 from gzb where salary > (select salary from gzb where name = @name)
[解决办法]
--lz,如果存在相同的moneyV的话，试试这样
---创建测试数据
declare @t table(id int, name varchar (30)primary key, moneyV money)
insert into @t select 1, 'zhao ', 11
union all select 2, 'li ', 12
union all select 3, 'wang ', 15
union all select 4, 'lu ', 12 --加此行与li的moneyV相同并列第二
union all select 5, 'zh ', 6
---查看测试数据
select * from @t
---查看结果
select count(1)+1 as '排行第几 ' from @t a where exists
(select 1 from @t where name= 'li ' and moneyV <a.moneyV)
/*
排行第几
-----------
2
（所影响的行数为 1 行）
*/
