前几天碰到一个需求，把项目中，参与项目的人员都查出来，并且拼在一起用逗号隔开。然后研究了一个晚上弄出来了。我的sql水平不行，就马马虎虎的把自己的方法分享出来。举个栗子：
新建三个看着比较顺眼的表。
[SQL] 纯文本查看 复制代码 create table A_UserInfo
(
FID int primary key identity(1,1),
FUserName varchar(328),
FAge int
)
insert into A_UserInfo(FUserName,FAge) values('卡卡',18)
insert into A_UserInfo(FUserName,FAge) values('叶子',18)
insert into A_UserInfo(FUserName,FAge) values('球球',18)
insert into A_UserInfo(FUserName,FAge) values('天涯',18)
create table A_ProjectCourse
(
FID int primary key identity(1,1),
FCourseName varchar(128),
FType varchar(18)
)
insert into A_ProjectCourse(FCourseName,FType) values('语文','A')
insert into A_ProjectCourse(FCourseName,FType) values('数学','B')
insert into A_ProjectCourse(FCourseName,FType) values('英语','C')
create table A_UserScore
(
FID int primary key identity(1,1),
FUserID int,
FCourseType varchar(18),
FScore int
)
insert into A_UserScore(FUserID,FCourseType,FScore) values(1,'A',15)
insert into A_UserScore(FUserID,FCourseType,FScore) values(1,'B',70)
insert into A_UserScore(FUserID,FCourseType,FScore) values(1,'C',50)
insert into A_UserScore(FUserID,FCourseType,FScore) values(2,'A',60)
insert into A_UserScore(FUserID,FCourseType,FScore) values(2,'B',40)
insert into A_UserScore(FUserID,FCourseType,FScore) values(2,'C',30)
insert into A_UserScore(FUserID,FCourseType,FScore) values(3,'A',70)
insert into A_UserScore(FUserID,FCourseType,FScore) values(3,'B',80)
insert into A_UserScore(FUserID,FCourseType,FScore) values(3,'C',90)
insert into A_UserScore(FUserID,FCourseType,FScore) values(4,'A',100)
insert into A_UserScore(FUserID,FCourseType,FScore) values(4,'B',200)
单独查询：
[SQL] 纯文本查看 复制代码 select * from A_UserInfo
select * from A_ProjectCourse
select * from A_UserScore
连接查询：
[SQL] 纯文本查看 复制代码 select T1.FID,T1.FUserName,T3.FCourseName,T2.FScore
from A_UserInfo T1
left join A_UserScore T2 on T1.FID=T2.FUserID
left join A_ProjectCourse T3 on T2.FCourseType=T3.FType
order by T1.FID asc
一：简单的拼接所有人
[AppleScript] 纯文本查看 复制代码 SELECT distinct stuff((SELECT ',' + t1.FUserName
FROM (select FID,FUserName from A_UserInfo ) AS t1
WHERE t1.FID = T1.FID FOR xml path('')), 1, 1, '')
FROM (select FID,FUserName from A_UserInfo) as T1
GROUP BY T1.FID
二：我做需求的时候用到了函数。
整体上就是，传入用户ID进来，然后去查询要拼接的字符串
[SQL] 纯文本查看 复制代码 create function [dbo].[JointCharacter](@userID int)
returns varchar(2048)
as
begin
declare @Names varchar(2048)
SELECT @Names = stuff((SELECT ',' + t.FCourseName FROM
(select T1.FID,T1.FUserName,T3.FCourseName,T2.FScore
from A_UserInfo T1
left join A_UserScore T2 on T1.FID=T2.FUserID
left join A_ProjectCourse T3 on T2.FCourseType=T3.FType
where T1.FID=@userID) AS t
WHERE t.FID = T.FID FOR xml path('')), 1, 1, '')
FROM (select T1.FID,T1.FUserName,T3.FCourseName,T2.FScore
from A_UserInfo T1
left join A_UserScore T2 on T1.FID=T2.FUserID
left join A_ProjectCourse T3 on T2.FCourseType=T3.FType
where T1.FID=@userID) as T
GROUP BY T.FID
return @Names
end
二.1：查询FID为1 的人考了哪几门
[SQL] 纯文本查看 复制代码 select dbo.JointCharacter(1)
二.2：查询所有人考了哪几门
[SQL] 纯文本查看 复制代码 select FID,FUserName,dbo.JointCharacter(FID) FExamName from A_UserInfo
|
|
[复制链接]
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡
