求帮，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') 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
-------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
4 1 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 1 1 0 NULL NULL NULL NULL 4 NULL NULL NULL 0.0068571 NULL NULL SELECT 0 NULL
0 0
[解决办法]
--Compute Scalar(DEFINE:([Expr1009]=[Expr1007]+(1))) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1009]=[Expr1007]+(1)) [Expr1009]=[Expr1007]+(1) 4 0 4E-07 23 0.0068571 [T].[name], [T].[course], [Expr1009] NULL PLAN_ROW 0 1
4 1
[解决办法]
--Nested Loops(Inner Join, OUTER REFERENCES:([T].[name], [T].[course])) 1 3 2 Nested Loops Inner Join OUTER REFERENCES:([T].[name], [T].[course]) NULL 4 0 1.672E-05 23 0.0068567 [T].[name], [T].[course], [Expr1007] NULL PLAN_ROW 0 1
4 1
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[a] AS [T])) 1 4 3 Table Scan Table Scan OBJECT:([tempdb].[dbo].[a] AS [T]) [T].[name], [T].[course] 4 0.003125 0.0001614 19 0.0032864 [T].[name], [T].[course] NULL PLAN_ROW 0 1
0 0
[解决办法]
--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1012],0))) 1 5 3 Compute Scalar Compute Scalar DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1012],0)) [Expr1007]=CONVERT_IMPLICIT(int,[Expr1012],0) 1 0 1.1E-06 11 0.00355358 [Expr1007] NULL PLAN_ROW 0 4
4 4
[解决办法]
--Stream Aggregate(DEFINE:([Expr1012]=Count(*))) 1 6 5 Stream Aggregate Aggregate NULL [Expr1012]=Count(*) 1 0 1.1E-06 11 0.00355358 [Expr1012] NULL PLAN_ROW 0 4
3 4
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:([tempdb].[dbo].[a].[name]=[tempdb].[dbo].[a].[name] as [T].[name] AND [tempdb].[dbo].[a].[course]<[tempdb].[dbo].[a].[course] as [T].[course])) 1 7 6 Table Scan Table Scan OBJECT:([tempdb].[dbo].[a]), WHERE:([tempdb].[dbo].[a].[name]=[tempdb].[dbo].[a].[name] as [T].[name] AND [tempdb].[dbo].[a].[course]<[tempdb].[dbo].[a].[course] as [T].[course]) NULL 1 0.0032035 8.29E-05 19 0.0035351 NULL NULL PLAN_ROW 0 4
*/
