--sql 2000 select name , max(case px when 1 then course else '' end) course1, max(case px when 2 then course else '' end) course2 from ( select t.* , px = (select count(1) from a where name = t.name and course < t.course) + 1 from a t ) m group by name--sql 2005 select name , max(case px when 1 then course else '' end) course1, max(case px when 2 then course else '' end) course2 from ( select t.* , px = row_number() over(partition by name order by course) from a t ) m group by name
create table a (name nvarchar(10),course varchar(10)) insert into a values(N'张三', '111') insert into a values(N'张三', '222') insert into a values(N'李四', '111') go--sql 2000 select name , max(case px when 1 then course else '' end) course1, max(case px when 2 then course else '' end) course2 from ( select t.* , px = (select count(1) from a where name = t.name and course < t.course) + 1 from a t ) m group by namedrop table a/* name course1 course2 ---------- ---------- ---------- 李四 111 张三 111 222(所影响的行数为 2 行) */ create table a (name nvarchar(10),course varchar(10)) insert into a values(N'张三', '111') insert into a values(N'张三', '222') insert into a values(N'李四', '111') go--sql 2005 select name , max(case px when 1 then course else '' end) course1, max(case px when 2 then course else '' end) course2 from ( select t.* , px = row_number() over(partition by name order by course) from a t ) m group by namedrop table a/* name course1 course2 ---------- ---------- ---------- 张三 111 222 李四 111 (2 行受影响) */
怎么区分2个列
select name ,
max(case px when 1 then course else '' end) course1,
max(case px when 2 then course else '' end) course2
from
(
select t.* , px = (select count(1) from a where name = t.name and course < t.course) + 1 from a t
) m
group by name--sql 2005
select name ,
max(case px when 1 then course else '' end) course1,
max(case px when 2 then course else '' end) course2
from
(
select t.* , px = row_number() over(partition by name order by course) from a t
) m
group by name
http://topic.csdn.net/u/20080920/15/61bf31bf-518c-41be-9e4a-b166c878dcaf.html6楼
insert into a values(N'张三', '111')
insert into a values(N'张三', '222')
insert into a values(N'李四', '111')
go--sql 2000
select name ,
max(case px when 1 then course else '' end) course1,
max(case px when 2 then course else '' end) course2
from
(
select t.* , px = (select count(1) from a where name = t.name and course < t.course) + 1 from a t
) m
group by namedrop table a/*
name course1 course2
---------- ---------- ----------
李四 111
张三 111 222(所影响的行数为 2 行)
*/
create table a (name nvarchar(10),course varchar(10))
insert into a values(N'张三', '111')
insert into a values(N'张三', '222')
insert into a values(N'李四', '111')
go--sql 2005
select name ,
max(case px when 1 then course else '' end) course1,
max(case px when 2 then course else '' end) course2
from
(
select t.* , px = row_number() over(partition by name order by course) from a t
) m
group by namedrop table a/*
name course1 course2
---------- ---------- ----------
张三 111 222
李四 111 (2 行受影响)
*/
爱新觉罗.毓华 很强大的说!
呵呵能把代码解释下吗 爱新觉罗.毓华?
非常感谢!