有这样一张表
create table haha(
round_number int,
name_id int,
point int,
leiji int)insert haha values
(1,1,1,5)insert haha values
(2,1,2,45)insert haha values
(1,2,400,889)insert haha values
(1,4,100,525)
insert haha values
(2,3,125,987)select name_id, (select count(*)+1 from haha (nolock) where round_number = 2 and point > T.point)
from haha T(nolock)
where round_number = 2
------------------------name_id
----------- -----------
1 2
3 1(所影响的行数为 2 行)运行出来的结果是这样的,我想问下,后面个字段的数字(2,1)是怎么出来的,不明白那个语句
.
谢谢
create table haha(
round_number int,
name_id int,
point int,
leiji int)insert haha values
(1,1,1,5)insert haha values
(2,1,2,45)insert haha values
(1,2,400,889)insert haha values
(1,4,100,525)
insert haha values
(2,3,125,987)select name_id, (select count(*)+1 from haha (nolock) where round_number = 2 and point > T.point)
from haha T(nolock)
where round_number = 2
------------------------name_id
----------- -----------
1 2
3 1(所影响的行数为 2 行)运行出来的结果是这样的,我想问下,后面个字段的数字(2,1)是怎么出来的,不明白那个语句
.
谢谢
从这里出来的 这个也相当于一个列
select name_id, (select count(*)+1 from haha (nolock) where round_number = 2 and point > T.point)
from haha T(nolock)
where round_number = 2数据格式如下:
round_number name_id point leiji
1 1 1 5
2 1 2 45
1 2 400 889
1 4 100 525
2 3 125 987
需要理解子查询和表别名两个概念
(select count(*)+1 from haha (nolock) 为子查询,它会对父查询返回的每条记录进行过滤
父查询会返回所有round_number = 2的记录当父查询查到第一条记录时,也就是
2 1 2 45, point为2
所以,此时子查询的T.point为2,子查询变为
select count(*)+1 from haha (nolock) where round_number = 2 and point > 2
记录 2 3 125 987 满足条件,所以返回1,再加1为2当父查询查到第二条记录时,也就是
2 3 125 987, point为125
所以,此时子查询的T.point为2,子查询变为
select count(*)+1 from haha (nolock) where round_number = 2 and point > 125
表里没有比125还大的数据,所以返回0,加1为1T为表的别名,用处就是表识父查询返回的记录
如果不用别名的话就成了point > point ,引擎就不知道去哪里比了