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

Question

场景:我有一张要获取的工作表。每个作业都有一个或多个与其关联的项目，这些项目存储在 jobItems 表中。项目的代码和详细信息保存在项目表中。 jobs 和 jobItems 表之间存在一对多的关系。另请记住，它具有大型数据集。

我想显示在其 jobItems 中具有一项特定项目的所有工作。

MySQL fiddle : 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 上有一个 LEFT JOIN 和一个 GROUP BY，它们在实现基于项目的职位过滤器时产生了问题。

尝试过的选项:

• 我试图删除 GROUP BY 和 GROUP_CONCAT，所以它会返回所有可能的工作项目组合。我想操纵他们在后端使用 php。但它有一个缺点，就是会扰乱分页。
• 我还尝试将 LEFT JOIN 动态更改为 INNER JOIN 并且在子查询中，我在 INNER JOIN INNER JOIN 上添加了一个条件jobItems.itemId = items.id AND jobItems.itemId IN (1) 但是，它不会因为 GROUP BY 而获取所需的结果只返回只有一个项目具有给定 itemId 的作业。它不会返回具有多个项目的工作，包括项目itemId= 1.

简而言之，我想获取所有包含 itemId = 1 项目的作业。预期结果是 job1 和 job2，因为它们都包含 item1。

Answer 1

试试这个

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