数据库为MSSQL2000,如以下三表:员工表、员工爱好表、爱好配置表DB_Emp:员工
--------------------
Emp_Id name
1 张三
2 李四
3 杜明
4 李果
5 赵刚DB_Emp_Like:员工爱好
---------------------
Emp_Id Like_No
1 101
1 102
1 105
3 101
3 105
4 101
4 102
5 101
5 102
5 103DB_Like:爱好表
--------------------
Like_No Name
101 打篮球
102 打乒乓球
103 唱歌
104 登山
105 游泳如上三表,现在有一需求,公司根据需要可能会根据情况查询同时具备几个爱好的员工来组织活动,请为怎么查询?
目前我能在MSSQL2008下通过FOR XML PATH能实现,不知道在MSSQL2000下怎么实现??? 谢谢!
--------------------
Emp_Id name
1 张三
2 李四
3 杜明
4 李果
5 赵刚DB_Emp_Like:员工爱好
---------------------
Emp_Id Like_No
1 101
1 102
1 105
3 101
3 105
4 101
4 102
5 101
5 102
5 103DB_Like:爱好表
--------------------
Like_No Name
101 打篮球
102 打乒乓球
103 唱歌
104 登山
105 游泳如上三表,现在有一需求,公司根据需要可能会根据情况查询同时具备几个爱好的员工来组织活动,请为怎么查询?
目前我能在MSSQL2008下通过FOR XML PATH能实现,不知道在MSSQL2000下怎么实现??? 谢谢!
IF OBJECT_ID('[DB_Emp]') IS NOT NULL DROP TABLE [DB_Emp]
GO
CREATE TABLE [DB_Emp]([Emp_Id] INT,[name] VARCHAR(4))
INSERT [DB_Emp]
SELECT 1,'张三' UNION ALL
SELECT 2,'李四' UNION ALL
SELECT 3,'杜明' UNION ALL
SELECT 4,'李果' UNION ALL
SELECT 5,'赵刚'--> 测试数据:[DB_Emp_Like]
IF OBJECT_ID('[DB_Emp_Like]') IS NOT NULL DROP TABLE [DB_Emp_Like]
GO
CREATE TABLE [DB_Emp_Like]([Emp_Id] INT,[Like_No] INT)
INSERT [DB_Emp_Like]
SELECT 1,101 UNION ALL
SELECT 1,102 UNION ALL
SELECT 1,105 UNION ALL
SELECT 3,101 UNION ALL
SELECT 3,105 UNION ALL
SELECT 4,101 UNION ALL
SELECT 4,102 UNION ALL
SELECT 5,101 UNION ALL
SELECT 5,102 UNION ALL
SELECT 5,103--> 测试数据:[DB_Like]
IF OBJECT_ID('[DB_Like]') IS NOT NULL DROP TABLE [DB_Like]
GO
CREATE TABLE [DB_Like]([Like_No] INT,[Name] VARCHAR(8))
INSERT [DB_Like]
SELECT 101,'打篮球' UNION ALL
SELECT 102,'打乒乓球' UNION ALL
SELECT 103,'唱歌' UNION ALL
SELECT 104,'登山' UNION ALL
SELECT 105,'游泳'
--------------开始查询--------------------------
IF OBJECT_ID('F_Str') IS NOT NULL
DROP FUNCTION F_Str
go
CREATE FUNCTION F_Str ( @Emp_Id INT )
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @S VARCHAR(100)
SELECT @S = ISNULL(@S + ',', '') + c.[Name]
FROM [DB_Emp_Like] b ,
[DB_Like] c
WHERE b.[Like_No] = c.[Like_No]
AND b.[Emp_Id] = @Emp_Id
RETURN @S
END
go SELECT DISTINCT a.[Emp_Id],a.[name],name=dbo.F_Str(a.[Emp_Id]) FROM [DB_Emp] a,[DB_Emp_Like] b
WHERE a.[Emp_Id]=b.[Emp_Id]
/*
Emp_Id name name
----------- ---- ----------------------------------------------------------------------------------------------------
1 张三 打篮球,打乒乓球,游泳
3 杜明 打篮球,游泳
4 李果 打篮球,打乒乓球
5 赵刚 打篮球,打乒乓球,唱歌(4 行受影响)
*/
IF OBJECT_ID('F_Str') IS NOT NULL
DROP FUNCTION F_Str
go
CREATE FUNCTION F_Str ( @Like_No INT )
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @S VARCHAR(100)
SELECT @S = ISNULL(@S + ',', '') + a.[name]
FROM [DB_Emp] a, [DB_Emp_Like] b
WHERE a.[Emp_Id] = b.[Emp_Id]
AND b.[Like_No] = @Like_No
RETURN @S
END
go SELECT DISTINCT b.[Name],name=dbo.F_Str(a.[Like_No]) FROM [DB_Emp_Like] a,[DB_Like] b
WHERE a.[Like_No]=b.[Like_No]
returns @emp table (emp_id int,name varchar(200))
as
begin
declare @like table (like_no varchar(200))
declare @i int
if CHARINDEX(',',@like_no,1) = 0
begin
insert into @like select @like_no
end
else
begin
while CHARINDEX(',',@like_no,1) >= 0
begin
set @i = CHARINDEX(',',@like_no,1)
insert into @like
select LEFT(@like_no,@i-1)
set @like_no = RIGHT(@like_no,len(@like_no)-@i)
if CHARINDEX(',',@like_no,1) = 0
begin
insert into @like select @like_no
break
end
else
begin
continue
end
end
endinsert into @emp select * from db_empdelete @emp where emp_id in
(select emp_id from (select a.emp_id,b.like_no from db_emp a,@like b) t
where not exists (select 1 from db_emp_like c
where t.emp_id = c.emp_id and t.like_no = c.like_no))
return
end --数据采用筱筱澄的测试数据
--测试
select * from fn_getid('101,102')
--结果
emp_id name
1 张三
4 李果
5 赵刚(3 行受影响)
select * from fn_getid('101,102,103')
结果
emp_id name
5 赵刚
在员工表数据量大的时候可能没什么效率,但是对1000行以内的记录应该很快
如果要得到字符串结果,你在这个函数基础上进行修改就是了。