22,209
社区成员
发帖
与我相关
我的任务
分享
表A:
A_ID A_Name
1 张三
2 李四
3 王五
表B:
B_ID B_Name
1 Java
2 MySql
3 C#
4 MsSql
我的意思是表A与表B之间是多对多的关系
一个人可以对应多个技能
这样的两个表之间应该怎么关联
例如我想得到的结果:
A_ID A_Name B_Name
1 张三 Java,MySql
2 李四 Java
3 王五 C#,MySql,MsSql
DECLARE @a TABLE(a_id INT,a_name VARCHAR(20))
DECLARE @b TABLE(b_id INT,b_name VARCHAR(20))
DECLARE @c TABLE(a_id INT,b_id INT)
DECLARE @d TABLE(a_id INT,a_name VARCHAR(20),x_name VARCHAR(100))
INSERT INTO @a(a_id,a_name)
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五'
INSERT INTO @b(b_id,b_name)
select 1,'Java' union all
select 2,'MySql' union all
select 3,'C#' union all
select 4,'MsSql'
INSERT INTO @c(a_id,b_id)
select 1,1 union all
select 1,4 union all
select 2,1 union all
select 3,3 union all
select 3,4 union all
select 3,2
DECLARE curcur CURSOR FOR SELECT a.a_id,a.a_name,b.b_name FROM @c AS c LEFT JOIN @a AS a ON c.a_id = a.a_id LEFT JOIN @b AS b ON c.b_id = b.b_id
DECLARE @id INT
DECLARE @name VARCHAR(20)
DECLARE @temp VARCHAR(20)
OPEN curcur
FETCH NEXT FROM curcur INTO @id,@name,@temp
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT 1 FROM @d AS d WHERE d.a_id = @id AND d.a_name=@name) UPDATE @d SET x_name = x_name + ',' + @temp WHERE a_id=@id AND a_name=@name
ELSE INSERT INTO @d(a_id,a_name,x_name) SELECT @id,@name,@temp
FETCH NEXT FROM curcur INTO @id,@name,@temp
END
SELECT * FROM @d
/*
a_id a_name x_name
1 1 张三 Java,MsSql
2 2 李四 Java
3 3 王五 C#,MsSql,MySql
*/