我使用mysql5.1,我有一个表,我们在其中记录有关作业的操作。下面是它的模式定义:
CREATE TABLE `actions_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`action` varchar(45) DEFAULT NOT NULL,
`job_id` int(10) unsigned NOT NULL,
`candidate_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
我需要创建一个请求生成器,以便用户可以使用“and”和“or”运算符以及括号搜索表。sql请求应该返回候选id。
例如,他们应该能够进行以下搜索:“actiona或(actionb和actionc)”。
我使用了工会条款来实现这一点,当提供了特定的工作时,工会就可以做到这一点:
SELECT `candidate_id` FROM (
SELECT `candidate_id` FROM `actions_log` WHERE `job_id` = 1858 AND `action` = 'a'
UNION DISTINCT
(
SELECT `candidate_id` FROM (
SELECT `candidate_id`, COUNT(`candidate_id`) AS `count` FROM (
SELECT DISTINCT `candidate_id` FROM `actions_log` WHERE `job_id` = 1858 AND `action` = 'b'
UNION ALL
SELECT DISTINCT `candidate_id` FROM `actions_log` WHERE `job_id` = 1858 AND `action` = 'c'
) AS level1
GROUP BY `candidate_id`
) AS level2
WHERE `count` = 2
)
) AS candidates;
用户也可以在不指定作业的情况下进行搜索。在这种情况下,任何作业都应该匹配。
这就是问题所在:要使同一个作业获得有效结果,操作必须是相同的。
例如,使用上面的示例,作业1的“actionA”和作业2的“actionB和actionC”不应是有效的匹配。
如果没有指定工作,我就找不到办法使这项工作成功。在这种情况下,也许工会条款不是解决办法。
最佳答案
我想这已经足够了:
SELECT candidate_id
FROM actions_log AS a
WHERE job_id = 1858
AND ( action = 'a'
OR action = 'b'
AND EXISTS
( SELECT candidate_id
FROM actions_log
WHERE job_id = a.job_id
AND action = 'c'
)
) ;
或者,如果您希望分离条件,以便可以更轻松地构建更复杂的查询:
SELECT candidate_id
FROM actions_log AS a
WHERE job_id = 1858
AND action = 'a'
UNION DISTINCT
SELECT b.candidate_id
FROM actions_log AS b
JOIN actions_log AS c
ON c.candidate_id = b.candidate_id
AND c.job_id = b.job_id
WHERE b.job_id = 1858
AND b.action = 'b'
AND c.action = 'c' ;