可以用这样的方式create table t
(Tuser varchar(20),Fuser varchar(20),type int)
go
insert t
select 'mary','tom',1 union all
select 'mary','john',2 union all
select 'mary','ken',3 union all
select 'sam','joe',1select * from t
declare @sql varchar(8000)
select @sql='select Tuser'
select @sql=@sql+',[Fuser'+cast(Type as varchar)+']=max(case when Type='+cast(type as varchar)+' then Fuser else '''' end)'
from t group by type
select @sql=@sql+' from t group by Tuser'
exec(@sql)
drop table tTuser Fuser type
-------------------- -------------------- -----------
mary tom 1
mary john 2
mary ken 3
sam joe 1(所影响的行数为 4 行)Tuser Fuser1 Fuser2 Fuser3
-------------------- -------------------- -------------------- --------------------
mary tom john ken
sam joe
(Tuser varchar(20),Fuser varchar(20),type int)
go
insert t
select 'mary','tom',1 union all
select 'mary','john',2 union all
select 'mary','ken',3 union all
select 'sam','joe',1select * from t
declare @sql varchar(8000)
select @sql='select Tuser'
select @sql=@sql+',[Fuser'+cast(Type as varchar)+']=max(case when Type='+cast(type as varchar)+' then Fuser else '''' end)'
from t group by type
select @sql=@sql+' from t group by Tuser'
exec(@sql)
drop table tTuser Fuser type
-------------------- -------------------- -----------
mary tom 1
mary john 2
mary ken 3
sam joe 1(所影响的行数为 4 行)Tuser Fuser1 Fuser2 Fuser3
-------------------- -------------------- -------------------- --------------------
mary tom john ken
sam joe
mary john
sam joe
--动态SQL 或者函数都可以解决!
mary tom
mary john
sam joe
当然有一个注册用户表,如:
CREATE TABLE [TB_user] (
[userid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[username] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_TB_user] PRIMARY KEY CLUSTERED
(
[userid]
) ON [PRIMARY]
) ON [PRIMARY]
GO
再来就是保存讨厌对象的表:
CREATE TABLE [bedfriend] (
[bedid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[userid1] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[userid2] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_bedfriend] PRIMARY KEY CLUSTERED
(
[bedid]
) ON [PRIMARY]
) ON [PRIMARY]
GOOK了,TB_user里存的是注册用户
bedfrend表里存的是如下信息:
bedfrend
---------------------
bedid userid1 userid2
1 1 2
2 1 5
3 1 8
4 2 3
5 2 4
6 3 6注意有一点,就是,这个关系设计的是如果bill讨厌kate,那kate不一定讨厌bill
原因大家都知道,kate喜欢bill嘛,bill喜欢nana
:-)如果问题解决就快给分,我现在还没得过分呢,大家都不知道结帖,呵呵。:-)
mary john
sam joe