declare @T table([职位] varchar(4),[姓名] varchar(1)) insert @T select '学生','A' union all select '学生','B' union all select '学生','C';with maco as ( select row_number() over (partition by [职位] order by [姓名])as rid,* from @T )select case when rid=1 then 职位 else '' end as 职位,姓名 from maco /* 职位 姓名 ---- ---- 学生 A B C */
declare @T table([职位] varchar(4),[姓名] varchar(1)) insert @T select '学生','A' union all select '学生','B' union all select '学生','C'select case px when 1 then [职位] else '' end as [职位], [姓名] from (select px=row_number()over(partition by [职位] order by getdate()),* from @T)t /* 职位 姓名 学生 A B C */
如果是2000版本,可用: declare @T table([职位] varchar(4),[姓名] varchar(1)) insert @T select '学生','A' union all select '学生','B' union all select '学生','C'select case (select count(1) from @t a where a.姓名<=b.姓名) when 1 then [职位] else '' end as [职位],[姓名] from @t b
declare @T table([职位] varchar(4),[姓名] varchar(1))
insert @T
select '学生','A' union all
select '学生','B' union all
select '学生','C';with maco as
(
select row_number() over (partition by [职位] order by [姓名])as rid,* from @T
)select case when rid=1 then 职位 else '' end as 职位,姓名 from maco
/*
职位 姓名
---- ----
学生 A
B
C
*/
insert @T
select '学生','A' union all
select '学生','B' union all
select '学生','C'select
case px when 1 then [职位] else '' end as [职位],
[姓名]
from
(select px=row_number()over(partition by [职位] order by getdate()),* from @T)t
/*
职位 姓名
学生 A
B
C
*/
declare @T table([职位] varchar(4),[姓名] varchar(1))
insert @T
select '学生','A' union all
select '学生','B' union all
select '学生','C'select case (select count(1) from @t a where a.姓名<=b.姓名) when 1 then [职位] else '' end as [职位],[姓名]
from @t b