mysql - 是否可以在同一查询中执行搜索查询并指定不返回哪些匹配项？

Question

我有一个搜索框，它使用 LIKE 运算符从多个表中返回值。但是我现在想确保不返回 x = y 的任何匹配项。这可以在同一个查询中完成吗？

我的尝试:

$search_sql = "SELECT * FROM User 
    INNER JOIN UserSkills ON User.UserId = UserSkills.UserId 
    INNER JOIN Skills ON UserSkills.SkillId = Skills.SkillId 
    INNER JOIN UserTasks on User.UserId = UserTasks.UserId
    WHERE Description LIKE '%".$value."%' 
    OR FName LIKE '%".$value."%' 
    OR LName LIKE '%".$value."%' 
    OR JobRole LIKE '%".$value."%' 
    OR UserId NOT IN (
        SELECT UserID from UserTasks WHERE TaskID = $Task) 
    GROUP BY Description" or die(mysqli_error($con));

    $search_query = mysqli_query($con, $search_sql);
            if(mysqli_num_rows($search_query) !=0) {
                $search_rs = mysqli_fetch_assoc($search_query);
                }

此尝试返回 false(无结果)。有结果待查。

这是在添加新表 (UserTasks) 和 NOT IN 之前成功返回结果的先前版本:

$search_sql = "SELECT * FROM User 
    INNER JOIN UserSkills ON User.UserId = UserSkills.UserId 
    INNER JOIN Skills ON UserSkills.SkillId = Skills.SkillId 
    WHERE Description LIKE '%".$value."%' 
    OR FName LIKE '%".$value."%' 
    OR LName LIKE '%".$value."%' 
    OR JobRole LIKE '%".$value."%' 
    GROUP BY Description" or die(mysqli_error($con));

Answer 1

如果要排除 x = y 处的记录，您需要添加类似 AND x <> y 的内容(x 不等于 y) 到你的 WHERE .请记住，您需要将 WHERE 括起来组合时基于您意图的组件 AND s 和 OR

没有看到您表格中的数据，我不确定您目前没有得到结果的原因。我的建议是开始取出部分，直到获得预期的结果，然后将它们加回去测试您的预期，直到出现不匹配为止。

一些其他注意事项:

• or die(..)在这里我认为没有任何作用，因为左侧只是字符串连接，我认为它不会失败。分配给 $search_sql 时，您实际上并没有做任何与 MySQL 相关的工作.
• 我建议研究参数化查询，而不是使用字符串连接和变量插值来生成查询。最初会做更多的工作，但往往更容易维护和推理，并且很容易变得更安全。
• 我想INNER JOIN和 JOIN没有功能差异，所以我会选择一个或另一个并保持一致。
• 使用 SELECT *当按某些事物分组时，可能会产生意想不到的/不一致的结果。任何不在 GROUP BY 中的列包含在聚合中的子句或将返回组的 first 行上的值，但不保证 first 是一致的(除非你 ORDER )。其他 DBMS(我认为至少是 Oracle 和 SQL Server)实际上禁止这种行为。

更新

SELECT Description
FROM User u
    JOIN UserSkills us ON u.UserId = us.UserId 
    JOIN Skills s ON us.SkillId = s.SkillId 
WHERE
    (
        Description LIKE '%".$value."%' 
        OR FName LIKE '%".$value."%' 
        OR LName LIKE '%".$value."%' 
        OR JobRole LIKE '%".$value."%' 
    )
    AND UserId NOT IN (
        SELECT UserID
        FROM User u
            JOIN UserTasks ut ON u.UserId = ut.UserId
        WHERE TaskID = $Task
    )
GROUP BY Description

使用模式和数据更新

CREATE TABLE users (
  id INT NOT NULL,
  first VARCHAR(16),
  last VARCHAR(16),

  PRIMARY KEY (id)
);

CREATE TABLE skills (
  id INT NOT NULL,
  description VARCHAR(64) NOT NULL,
  job_role VARCHAR(64) NOT NULL,

  PRIMARY KEY (id)
);

CREATE TABLE user_skills (
  user_id INT NOT NULL,
  skill_id INT NOT NULL,

  FOREIGN KEY user_skills_user (user_id) REFERENCES users (id),
  FOREIGN KEY user_skills_skill (skill_id) REFERENCES skills (id)
);

CREATE TABLE user_tasks (
  user_id INT NOT NULL,
  task_id INT NOT NULL,

  PRIMARY KEY (user_id,task_id),
  FOREIGN KEY user_tasks_user (user_id) REFERENCES users (id)
);

INSERT INTO users VALUES (0,'FA','LA');
INSERT INTO users VALUES (1,'FB','LB');
INSERT INTO users VALUES (2,'FC','LC');

INSERT INTO skills VALUES (0,'Skill Description A','Job Role A');
INSERT INTO skills VALUES (1,'Skill Description B','Job Role B');
INSERT INTO skills VALUES (2,'Skill Description C','Job Role C');

INSERT INTO user_skills VALUES (0,0);
INSERT INTO user_skills VALUES (0,1);
INSERT INTO user_skills VALUES (0,2);
INSERT INTO user_skills VALUES (1,0);
INSERT INTO user_skills VALUES (1,1);
INSERT INTO user_skills VALUES (2,1);

INSERT INTO user_tasks VALUES (0,1);
INSERT INTO user_tasks VALUES (1,2);
INSERT INTO user_tasks VALUES (2,1);

SELECT *
FROM users u
    JOIN user_skills us ON u.id = us.user_id
    JOIN skills s ON us.skill_id = s.id
WHERE
    (
        s.description LIKE ''
        OR u.first LIKE '%F%'
        OR u.last LIKE ''
        OR s.job_role LIKE ''
    )
    AND u.id NOT IN (
        SELECT user_id
        FROM user_tasks
        WHERE task_id = 2
    )
;

这应该返回 4 条记录，3 条用于 FA LA 的 3 种技能，一条用于 FC LC 的 1 种技能。 FB LB 未返回，因为分配给任务 2。更改为 task_id = 1应该返回2条记录，FB LB 2技能。