mysql - 如何根据子查询中的特定值过滤记录？

脚本：
我有一张桌子要拿。每个作业都有一个或多个与之关联的项，这些项存储在job items表中。项目的代码和详细信息保存在项目表中。jobs和jobitems表之间存在一对多关系。还要记住，它有很大的数据集。
我想显示所有在其作业项中有一个特定项的作业。
mysql小提琴：http://sqlfiddle.com/#!9/4a5a47
架构：

CREATE TABLE jobs (
    `id` INT,
    `jobRef` VARCHAR (55)
);

INSERT INTO jobs (`id`, `jobRef`)
VALUES
    (1, 'job1'),
    (2, 'job2'),
    (3, 'job3');

CREATE TABLE jobItems (
    `id` INT,
    `itemId` INT,
    `jobId` INT
);

INSERT INTO jobItems (`id`, `itemId`, `jobId`)
VALUES
    (1, 1, 1),
    (2, 2, 1),
    (3, 3, 1),
    (4, 1, 2),
    (5, 2, 2),
    (6, 3, 3);

CREATE TABLE items (
    `id` INT,
    `itemCode` VARCHAR (55)
);

INSERT INTO items (`id`, `itemCode`)
VALUES
    (1, 'item1'),
    (2, 'item2'),
    (3, 'item3');

查询：

SELECT
    jobs.*, ji.allItems
FROM
    jobs
LEFT JOIN (
    SELECT
        jobItems.jobId,
        GROUP_CONCAT(items.itemCode) AS allItems
    FROM
        jobItems
    INNER JOIN items ON jobItems.itemId = items.id
    GROUP BY
        jobItems.jobId
) AS ji ON ji.jobId = jobs.id

正如您注意到的，jobitems.jobid上有一个左连接和一个group by，这在实现基于项的作业筛选器时会产生问题。
尝试的选项：
我试图删除group by和group_concat，因此它将返回
所有可能的工作项组合。我想操纵他们
在后端使用php。但它有一个缺点，那就是它干扰了
分页。
我还尝试动态地将左连接更改为内部连接，并
在子查询中，我在内部联接内部联接上添加了一个条件
jobitems.itemid中的items=items.id和jobitems.itemid（1），但是，
它无法获取所需的结果，因为它将
只返回只有一个具有给定项ID的项的作业。
它不返回具有多个项的作业，包括
项目ID=1。
简而言之，我想获取包含itemid=1的项的所有作业。预期结果是job1和job2，因为它们都有item1。

最佳答案

试试这个

SELECT jobs.*, ji.allItems
FROM jobs
INNER JOIN (
    SELECT
        jobItems.jobId,
        GROUP_CONCAT(items.itemCode) AS allItems
    FROM
        jobItems
    INNER JOIN items ON jobItems.itemId = items.id
    GROUP BY
        jobItems.jobId
    HAVING MAX(CASE WHEN jobItems.itemId = 1 THEN 1 ELSE 0 END) > 0
) AS ji ON ji.jobId = jobs.id

SQLFiddle
对于多个项筛选器（例如，要查找与项1和项2关联的作业）

SELECT jobs.*, ji.allItems
FROM jobs
INNER JOIN (
    SELECT
        jobItems.jobId,
        GROUP_CONCAT(items.itemCode) AS allItems
    FROM
        jobItems
    INNER JOIN items ON jobItems.itemId = items.id
    GROUP BY
        jobItems.jobId
    HAVING
    MAX(CASE WHEN jobItems.itemId = 1 THEN 1 ELSE 0 END) > 0 AND
    MAX(CASE WHEN jobItems.itemId = 2 THEN 1 ELSE 0 END) > 0
) AS ji ON ji.jobId = jobs.id