mysql - 在MySQL搜索查询上,需要帮助

  显示原文与译文双语对照的内容
0 0

我需要一些关于我正在设置的MySQL查询的帮助。 我需要找到匹配条件的记录,这些条件位于两个不同的表中,并且有许多关系。

这个查询使用三个表,第一个包含项目,第二个包含主题,第三个将它们。 我希望查询查找与用户选定的主题相关的项目。 用户还可以选择多个主题来执行搜索。 这种情况下,我只希望显示与两个主题相关的项目,而不是它们中的任何一个。 这就是我无法确定的。 我希望我不得不这样做,但这个查询没有结果,同时还有一个项目链接到数据库中。

SELECT `projects`.*
FROM `projects`, `topics`, `projects_topics`
WHERE (`name` LIKE '%%' || `vision` LIKE '%%' || `highlights` LIKE '%%' || `optional` LIKE '%%')
 && ((`projects_topics`.`projects_id` = `projects`.`id` && `projects_topics`.`topics_id` = `topics`.`id` && `topics`.`id` = '1')
 && (`projects_topics`.`projects_id` = `projects`.`id` && `projects_topics`.`topics_id` = `topics`.`id` && `topics`.`id` = '9'))
ORDER BY `date_added` DESC

这些是表:

项目

+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment | 
| fields_id | int(11) | NO | PRI | NULL | | 
| name | varchar(255) | YES | | NULL | | 
| address | varchar(255) | YES | | NULL | | 
| zip | varchar(255) | YES | | NULL | | 
| city | varchar(255) | YES | | NULL | | 
| state | varchar(255) | YES | | NULL | | 
| countries_id | int(11) | NO | PRI | NULL | | 
| website | varchar(255) | YES | | NULL | | 
| client | varchar(255) | YES | | NULL | | 
| finished | date | YES | | NULL | | 
| budget | int(11) | YES | | NULL | | 
| vision | text | YES | | NULL | | 
| highlights | text | YES | | NULL | | 
| innovation | text | YES | | NULL | | 
| optional | text | YES | | NULL | | 
| publish | tinyint(1) | YES | | NULL | | 
| date | datetime | YES | | NULL | | 
| featured | tinyint(1) | YES | | NULL | | 
| frontpage | tinyint(1) | YES | | NULL | | 
| date_added | datetime | YES | | NULL | | 
+--------------+--------------+------+-----+---------+----------------+

主题

+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment | 
| topic | varchar(255) | YES | | NULL | | 
| order | int(11) | YES | | NULL | | 
+-------+--------------+------+-----+---------+----------------+

projects_topics

+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| projects_id | int(11) | NO | PRI | NULL | | 
| topics_id | int(11) | NO | PRI | NULL | | 
+-------------+---------+------+-----+---------+-------+
时间:原作者:1个回答

0 0
SELECT p.*
FROM (
 SELECT project_id
 FROM project_topics pt
 WHERE topics_id IN (5, 9)
 GROUP BY
 project_id
 HAVING COUNT(*) = 2
 ) pto
JOIN projects p
ON p.project_id = pto.project_id

或者

SELECT p.*
FROM projects p
WHERE EXISTS
 (
 SELECT NULL
 FROM project_topics pt
 WHERE pt.project_id = p.project_id
 AND pt.topic_id IN (5, 9)
 LIMIT 1 OFFSET 1
 )

确保 project_topics 中的PK 定义为 (project_id, topic_id) ( 按这里顺序),或者在 (topic_id, project_id) 中创建一个附加的UNIQUE 索引。

原作者:
...