create table mainTable
(
  mainId int
);

create table tagTable
(
  tagId int,
  tagName varchar(15)
);

create table relationTable
(
  mainId int,
  tagId int
);

select mainId
from relationTable
where tagId in
(
select tagId from tagTable where tagName in ('A','B','C')
)

如果你是这么传参数：A,B
select mainId
from relationTable
where 
 charindex(tagId,'A,B,C')>0
如果是这样：AB
select mainId
from relationTable
where 
 charindex(tagId,'ABC')>0
如果是这样：A B
select mainId
from relationTable
where 
 charindex(tagId,'A B C')>0

------解决方案--------------------
SQL code

create table relationTable
(
  mainId int,
  tagId int
);

insert into relationTable
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,3

select * from(
select mainId from relationTable where tagId=1 
union all
select mainId from relationTable where tagId=2
union all
select mainId from relationTable where tagId=3
) t group by t.mainId having count(*)=3

------解决方案--------------------
SQL code

create table relationTable
(
  mainId int,
  tagId int
);

insert into relationTable
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,3

select * from (
select * from relationTable where  tagId= 1) t 
where 
exists (select 1 from relationTable a where a.mainId=t.mainId and a.tagId= 2) 
and 
exists (select 1 from relationTable b where b.mainId=t.mainId and b.tagId= 3)

------解决方案--------------------
我上面是按照三个标签来判断
按照你的要求可以更改为如下:
SQL code
create table mainTable
(
  mainId int
);

create table tagTable
(
  tagId int,
  tagName varchar(15)
);

create table relationTable
(
  mainId int,
  tagId int
);

--3条主数据
insert into mainTable values(1);
insert into mainTable values(2);
insert into mainTable values(3);

--3个标签 1,2,3是自增主键
insert into tagTable values(1,'tag1');
insert into tagTable values(2,'tag2');
insert into tagTable values(3,'tag3');

--关系表,根据主数据id和标签id,确定主数据包含哪几个标签.
--mainId 为1的数据 包含 tagId 1,2,3 三个标签.
--mainId 为2的数据包含 tagId 2,3 两个标签
insert into relationTable values(1,1);
insert into relationTable values(1,2);
insert into relationTable values(1,3);

insert into relationTable values(2,2);
insert into relationTable values(2,3);

--方法一：通过数量来判断
select mainId from
(
  select distinct t.mainId from mainTable t , relationTable m , tagTable n where t.mainId = m.mainId and m.tagId = n.tagId and tagName = 'tag1'
  union all
  select distinct t.mainId from mainTable t , relationTable m , tagTable n where t.mainId = m.mainId and m.tagId = n.tagId and tagName = 'tag2'
) t group by mainId having count(1) = 2

/*
mainId      
----------- 
1

（所影响的行数为 1 行）
*/

--方法二：通过数量存在值来判断
select t.* from mainTable t where 
exists(select 1 from relationTable m , tagTable n where t.mainId = m.mainId and m.tagId = n.tagId and tagName = 'tag1') and
exists(select 1 from relationTable m , tagTable n where t.mainId = m.mainId and m.tagId = n.tagId and tagName = 'tag2') 
/*
mainId      
----------- 
1

（所影响的行数为 1 行）
*/

drop table  main