如何利用sql语句匹配多行数据的同一个字段

怎么利用sql语句匹配多行数据的同一个字段

SQL code

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

SQL code

select mainIdfrom relationTablewhere 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语句该怎么修改啊?

[解决办法]

SQL code

如果你是这么传参数：A,Bselect mainIdfrom relationTablewhere  charindex(tagId,'A,B,C')>0如果是这样：ABselect mainIdfrom relationTablewhere  charindex(tagId,'ABC')>0如果是这样：A Bselect mainIdfrom relationTablewhere  charindex(tagId,'A B C')>0
[解决办法]
SQL code
create table relationTable(  mainId int,  tagId int);insert into relationTableselect 1,1 union allselect 1,2 union allselect 1,3 union allselect 2,1 union allselect 2,3select * from(select mainId from relationTable where tagId=1 union allselect mainId from relationTable where tagId=2union allselect 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 relationTableselect 1,1 union allselect 1,2 union allselect 1,3 union allselect 2,1 union allselect 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)
[解决办法]
我上面是按照三个标签来判断
按照你的要求可以更改为如下:
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') andexists(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 
  

 

查看更多 下一篇