优化器有关问题

--创建表#a
create table #a
(
 id  int identity not null,
  a  smallint,
  b  varchar(20),
  c varchar(20)
)

--插入数据
insert into #a (a,b,c)
         values(111,'a','a1'),
(111,'a','a2'),
(111,'bbb','b1'),
(111,'bbb','b2'),
(111,'bbb','b2'),
(11111,'cc','cc'),
(11111,'cc','cc'),
(222,'22','2'),
(222,'22','21'),
(333,'1','1')

;with a as(

select a,b,c,row_number() over(partition by a,b order by c)as rn
from (select distinct a,b,c from #a) as a
)
select replace(case when rn =1 then a when rn >1 then ' ' else '' end ,0,'') ) as a,
       replace(case when rn =1 then b when rn >1 then ' ' else '' end  ,0,'')) as b,
	   c
	   from a

a      b                    c
------ -------------------- --------------------
111    a                    a1
0                           a2
111    bbb                  b1
0                           b2
0                           b2
222    22                   2
0                           21
333    1                    1
11111  cc                   cc
0                           cc

(10 行受影响)

/*
1                    1           0           NULL                           NULL                           NULL                                                                                                                                                                   NULL                                                                                                                                                          10            NULL          NULL          NULL        0.01470812       NULL                                                                                      NULL     SELECT                                                           0        NULL