表结构
userid username groupname
1 a group1
1 a group3
2 b group2
2 b group3
........要得到的结果
userid username groupname1 groupname2 groupname3
1 a group1 group2 null
2 b group2 group3 null
.....
userid username groupname
1 a group1
1 a group3
2 b group2
2 b group3
........要得到的结果
userid username groupname1 groupname2 groupname3
1 a group1 group2 null
2 b group2 group3 null
.....
select * from ((select a.userid as userid0,a.username as username0,c.groupname as groupname0
from
user2 a,relation b,group2 c
where
a.userid = b.userid
and b.groupid = c.groupid) as table1join(select a.userid as userid1,a.username as username1,c.groupname as groupname1
from
user2 a,relation b,group2 c
where
a.userid = b.userid
and b.groupid = c.groupid) as table2on table1.userid0 = table2.userid1 and table1.groupname0 <> table2.groupname1)
userid username groupname1 groupname2 groupname3
1 a group1 group2 null
2 b group2 group3 null
.....
userid username groupname
1 a group1
1 a group3 1 a group1 group2 null --group2 那来的?
insert into tb select 1,'a','group1'
insert into tb select 1,'a','group3'
insert into tb select 2,'b','group2'
insert into tb select 2,'b','group3'
--静态的
select userid,username , max(case when groupname='group1' then groupname end ) as group1,
max(case when groupname='group2' then groupname end ) as group2,
max(case when groupname='group3' then groupname end ) as group3
from tb group by userid,username--动态的
declare @sql varchar(1000)
set @sql='select userid,username '
select @sql=@sql+', max(case when groupname='''+groupname+''' then groupname end ) as '+groupname
from (select distinct groupname from tb) a
set @sql=@sql+' from tb group by userid,username'
exec(@sql)userid username group1 group2 group3
----------- ---------- ---------- ---------- ----------
1 a group1 NULL group3
2 b NULL group2 group3(2 行受影响)
-----------------------------
答案是对的,根据此结果,可以分析出,select语句中筛选数据是从右到左的~