create table a (name nvarchar(10),course varchar(10))
insert into a values(N'张三', '12')
insert into a values(N'张三', '111')
insert into a values(N'张三', '222')
insert into a values(N'李四', '111') go
表a
name course
---------- ----------
张三 111
张三 222
李四 111
张三 12
执行语句：
SELECT name, course,
(SELECT COUNT(1) AS Expr1
FROM a
WHERE (name = t.name) AND (course < t.course) ) + 1 AS px
FROM a AS t
结果：name course px
---------- ---------- -----------
张三 111 1
张三 222 3
李四 111 1
张三 12 2
------解决方案--------------------
首先是from a as t也就是最后一句，得出一个虚拟表假设名字为V1，这个表不能在查询过程中查到，第二步是内层FROM A这个，然后进行WHERE条件筛选，第三步把筛选数据和外层的FROM A AS T进行关联，得到一个结果，第四部进行COUNT操作，最后一步返回SELECT 的结果
------解决方案--------------------
或者你用文本化执行计划看过程，从最内层开始，逐步向外执行
create table a (name nvarchar(10),course varchar(10))
insert into a values(N'张三', '12')
insert into a values(N'张三', '111')
insert into a values(N'张三', '222')
insert into a values(N'李四', '111')
SET STATISTICS PROFILE ON
SELECT name ,
course ,
( SELECT COUNT(1) AS Expr1
FROM a
WHERE ( name = t.name )
AND ( course < t.course )
) + 1 AS px
FROM a AS T
/*
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions