gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-30 21:47:19 24 4
gpt4 key购买 nike

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

表格是:

users Table :

enter image description here

project table

enter image description here

scenario Table

enter image description here

我尝试下面的查询,但如果我使用 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

输出: enter image description here

按查询分组:

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

输出:enter image description here

最佳答案

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

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

关于java - 在多个表中执行选择查询后删除重复数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48861291/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com