昨天的帖子:
http://topic.csdn.net/u/20120801/10/d5a2a894-f3a1-4808-8739-633054051aff.html?seed=459485272&r=79294856麻烦帮我看看我后面提的问题怎么处理,谢谢!
http://topic.csdn.net/u/20120801/10/d5a2a894-f3a1-4808-8739-633054051aff.html?seed=459485272&r=79294856麻烦帮我看看我后面提的问题怎么处理,谢谢!
SELECT a.[Emp_Id],a.[name]FROM [DB_Emp] a,[DB_Emp_Like] b
WHERE a.[Emp_Id]=b.[Emp_Id] AND b.[Like_No] IN (101,102,103)
AND EXISTS(
SELECT 1 FROM [DB_Emp_Like]
WHERE [Emp_Id]=b. [Emp_Id]
AND [Like_No] IN (101,102,103)
GROUP BY [Emp_Id] HAVING COUNT(DISTINCT [Like_No])=3
)
/*
Emp_Id name
----------- ----
5 赵刚
5 赵刚
5 赵刚(3 行受影响)
*/
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行以内的记录应该很快
如果要得到字符串结果,你在这个函数基础上进行修改就是了。