我正在使用标签系统,它应该做的是您可以查询并选择标签,例如jQuery和javascript,库标签.它仅应显示与查询相关的脚本,并且仅显示具有标签的脚本.这是数据库布局:
脚本表:
+-----------+---------------+
| script_id | name |
+-----------+---------------+
| 1 | jQuery |
| 2 | Sencha Touch |
| 3 | Codeigniter |
| 4 | Google Chrome |
| 5 | memcached |
| 6 | PHP |
| 7 | MooTools |
| 8 | node.js |
| 9 | jQuery Mobile |
+-----------+---------------+
标签表:
+--------+-------------+-------------+
| tag_id | name | url_name |
+--------+-------------+-------------+
| 1 | JavaScript | javascript |
| 2 | Library | library |
| 3 | PHP | php |
| 4 | MySQL | mysql |
| 5 | Cache | cache |
| 6 | HTML | html |
| 7 | Open source | open-source |
+--------+-------------+-------------+
标记表:
+-----------+--------+-----------+
| tagged_id | tag_id | script_id |
+-----------+--------+-----------+
| 1 | 1 | 1 | # javascript -- jQuery
| 2 | 2 | 1 | # library -- jQuery
| 3 | 1 | 9 | # javascript -- jQuery mobile
+-----------+--------+-----------+
当我运行SQL时,它仍会选择jQuery Mobile,但不应这样做,因为它不包含jQuery所包含的库标签,我需要它来约束必须满足所选标签的结果.
这是我的SQL:
SELECT scripts.script_id,
scripts.name
FROM
(
scripts scripts
LEFT OUTER JOIN tagged tagged ON tagged.script_id = scripts.script_id
LEFT OUTER JOIN tags tags ON tags.tag_id = tagged.tag_id
)
WHERE MATCH(scripts.name) AGAINST ('jquery*' IN BOOLEAN MODE) AND ( tags.url_name = 'javascript' OR tags.url_name = 'library' )
GROUP BY script_id
ORDER BY scripts.name
LIMIT 0, 25
它返回:
+-----------+---------------+
| script_id | name |
+-----------+---------------+
| 1 | jQuery |
| 9 | jQuery Mobile |
+-----------+---------------+
如果将OR更改为AND,则根本不会返回任何内容,或者如果我从标签(和)中删除了括号,也不会返回任何内容.
如何使查询约束标签?
解决方法:
试试看:
select s.script_id, s.name from scripts s
join tagged tj on s.script_id = tj.script_id
join tags t on t.tag_id = tj.tag_id
where s.name like 'jQuery%' and t.url_name in ('javascript', 'library')
group by s.script_id, s.name
having count(*) = 2
order by s.name
limit 25
唯一需要考虑的是,必须将2替换为in子句中的元素数量.
这是fiddle.