java - 在多个表中执行选择查询后删除重复数据

Question

我有三个表 [users,projects,scenarios] 我需要根据 modified Date 列获取最新的更新项目详细信息，不包含重复值

表格是:

users Table :

project table

scenario Table

我尝试下面的查询，但如果我使用 group by 它返回重复值，然后旧值出现但我需要最新值

没有按查询分组

SELECT
    p.`PROJECT_NAME`,
    p.`CREATED_DATE`,
    s.`MODIFIED_DATE`
FROM
    `projects` p
JOIN `scenarios` s ON
    s.`PROJECT_ID` = p.`PROJECT_ID`
WHERE
    P.`USER_ID` =(
    SELECT
        USER_ID
    FROM
        users
    WHERE
        EMAIL = 'test@gmail.com'
)
ORDER BY
    s.`MODIFIED_DATE`
DESC

输出:

按查询分组:

SELECT
    p.`PROJECT_NAME`,
    p.`CREATED_DATE`,
    s.`MODIFIED_DATE`
FROM
    `projects` p
JOIN `scenarios` s ON
    s.`PROJECT_ID` = p.`PROJECT_ID`
WHERE
    P.`USER_ID` =(
    SELECT
        USER_ID
    FROM
        users
    WHERE
        EMAIL = 'test@gmail.com'
)
group by p.PROJECT_NAME
ORDER BY
    s.`MODIFIED_DATE`
DESC

输出:

Answer 1

您可以使用子查询分别获取每个项目的最新场景，然后如果需要，结果行将再次连接以获取其他列。

SELECT  p.PROJECT_NAME,
        p.CREATED_DATE,
        s.MODIFIED_DATE
        -- all columns in s.* will have the latest row
FROM    projects p
        INNER JOIN scenarios s
            ON  p.PROJECT_ID = s.PROJECT_ID
        INNER JOIN
        (
            SELECT  project_ID, MAX(modified_date) MAX_modified_date
            FROM    scenarios
            GROUP   BY project_ID
        ) t ON s.project_ID = t.project_ID
                AND s.modified_date = t.MAX_modified_date
        INNER JOIN users u
            ON P.USER_ID = u.USER_ID
WHERE   u.EMAIL = 'test@gmail.com'
ORDER   BY s.MODIFIED_DATE DESC

但是，如果不需要获取其他列，可以直接使用MAX()和GROUP BY。

SELECT  p.PROJECT_NAME,
        p.CREATED_DATE,
        MAX(s.MODIFIED_DATE) AS MAX_MODIFIED_DATE
FROM    projects p
        INNER JOIN scenarios s
            ON  p.PROJECT_ID = s.PROJECT_ID
        INNER JOIN users u
            ON P.USER_ID = u.USER_ID
WHERE   u.EMAIL = 'test@gmail.com'
GROUP   BY p.PROJECT_NAME,
            p.CREATED_DATE
ORDER   BY MAX_MODIFIED_DATE DESC