table person:
person_id,
name,
age
...
table person_skill:
skill_id,
person_id
person与person_skill是一对多的关系,即没个人会有多种skill.
现在我根据多个skill_id,把符合条件的人查出来.
注意:结果必须是完全符合多个skill才行.
例:
person
person_id
1
2
3
person_skill:
person_id skill_id
1 1
1 2
1 3
2 1
3 2
如果查询skill_id为1的person,结果为:1,2
如果查询skill_id为1,2,3的person,结果为:1
如果查询skill_id为1,4的person,结果没有.
有什么好办法吗?
---------------------------------------------------------------
select person_id from person
where person_id in(
select person_id from person_skill
where skill_id=1 and skill_id=2....and skill_id=n)
---------------------------------------------------------------
declare @v varchar(1000)
set @v=''
select @v=@v+','+person_id from person_skill
where skill_id = 你的条件 order by person_id;
set @v=right(@v,len(@v)-1)
print @v
---------------------------------------------------------------
--可以做到。
create table person_skill(skill_id int,person_id int)
insert into person_skill values(1,1)
insert into person_skill values(1,2)
insert into person_skill values(1,3)
insert into person_skill values(2,1)
insert into person_skill values(3,2)
insert into person_skill values(3,5)
--如果查询skill_id为1的person
declare @table table(id int)
insert into @table values(1)
select skill_id
from person_skill
where person_id in (select id from @table)
group by skill_id
having count(distinct person_id) = (select count(distinct id) from @table)
--结果为1,2
--如果查询skill_id为1,2,3的person
declare @table table(id int)
insert into @table values(1)
insert into @table values(2)
insert into @table values(3)
select skill_id
from person_skill
where person_id in (select id from @table)
group by skill_id
having count(distinct person_id) = (select count(distinct id) from @table)
--结果为:1
--等等...
---------------------------------------------------------------
create table person_skill ( person_id int,skill_id int)
go
insert person_skill values (1,1)
insert person_skill values (1,2)
insert person_skill values (1,3)
insert person_skill values (2,1)
insert person_skill values (3,2)
go
create function get_list (@person_id int)
returns varchar(800)
as
begin
declare @str varchar(800)
select @str =''
select @str =@str + ','+convert(varchar(8),skill_id) from person_skill where person_id = @person_id
return @str
end
go
select person_id from (
select person_id,dbo.get_list(person_id) list from person_skill group by person_id) t1
where list like '%,1%' and list like '%,3%'