create table mainTable
(
mainId int
);create table tagTable
(
tagId int,
tagName varchar(15)
);create table relationTable
(
mainId int,
tagId int
);基本结构就是这样了.mainTable是主数据表,tagTable是标签信息的数据表,relationTable记录mainTable的所有标签关系,单向的一对多关系.能否利用sql语句,匹配同时包含某几个tagName的mainId
select mainId
from relationTable
where tagId in
(
select tagId from tagTable where tagName in ('A','B','C')
)之前用过上边的sql语句.但是发现mainId检索的只是在relationTable里tagId符合其中任何一个标签的的结果.
比如说:mainId 1 关联标签ABC,mainId 2关联BC.当搜索A,B标签的记过时候,居然显示
mainId 1 A
mainId 1 B
mainId 2 B (mainId 2 也包含B标签)而需要的结果是,同时包含A B标签的结果.
请问sql语句该怎么修改啊?
如果你是这么传参数: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
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,3select * 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
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,3select * 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)
exists(select 1 from tagTable n where tagName = 'A' and m.tagId = n.tagId) and
exists(select 1 from tagTable n where tagName = 'B' and m.tagId = n.tagId) and
exists(select 1 from tagTable n where tagName = 'C' and m.tagId = n.tagId)
select mainId from relationTable where tagId in
(
select tagid from
(
select distinct tagId from tagTable where tagName = 'A'
union all
select distinct tagId from tagTable where tagName = 'B'
union all
select distinct tagId from tagTable where tagName = 'C'
) t group by tagid having count(1) = 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);现在希望比如给出 标签1 标签2 这样的tagName,能搜索出同时包含这两个标签的所有mainId.而mainId是未知的.
(
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'
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 = 'tag3'
) t group by mainId having count(1) = 3/*
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') and
exists(select 1 from relationTable m , tagTable n where t.mainId = m.mainId and m.tagId = n.tagId and tagName = 'tag3')
/*
mainId
-----------
1(所影响的行数为 1 行)
*/drop table mainTable,tagTable,relationTable
按照你的要求可以更改为如下:
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 mainTable,tagTable,relationTable