gpt4 book ai didi

mysql - 根据MySQL中的两列查找最小值

转载 作者:行者123 更新时间:2023-11-29 23:09:16 26 4
gpt4 key购买 nike

我在 MySQL 中有一个包含运动表现的表。

结构:

id (PRIMARY KEY)    
athlete_id (FOREIGN KEY)
perform
category_id
discipline_id

如果我想从此表中选择最佳表现(每个运动员最多只能进入一次结果),我使用以下查询:

SELECT 
*
FROM
performs NATURAL
JOIN athletes
JOIN
(SELECT
athlete_id,
MIN(perform) AS perform,
category_id,
discipline_id
FROM
zaznamy
WHERE discipline_id = 4
AND category_id = 3
GROUP BY athlete_id) rec
ON performs.athlete_id = rec.athlete_id
AND performs.perform = rec.perform
AND performs.category_id = rec.category_id
AND performs.discipline_id = rec.discipline_id
ORDER BY performs.perform
LIMIT 25

我得到了正确的结果。但我想从一个学科和多个类别中选出最好的表现(例如男子与青少年等)。如何对两列使用子句 GROUP BY?

最佳答案

也许我不明白什么,但是您可以在分组依据中添加两列,如下例所示。只需在两列或多列之间添加 ,(逗号)即可。

SELECT 
*
FROM
performs NATURAL
JOIN athletes
JOIN
(SELECT
athlete_id,
MIN(perform) AS perform,
category_id,
discipline_id
FROM
zaznamy
WHERE discipline_id = 4
GROUP BY athlete_id,category_id) rec
ON performs.athlete_id = rec.athlete_id
AND performs.perform = rec.perform
AND performs.category_id = rec.category_id
AND performs.discipline_id = rec.discipline_id
ORDER BY performs.perform
LIMIT 25 enter code here

编辑 - 更新

好的,现在我明白你的问题了。实际上,这很常见,您希望首先找到组的最大值,然后请求有关该值的附加信息。

实现此目的的一种方法是使用如下的嵌套查询

SELECT ss.athlete_id,ss.perform,category_id
FROM performs ss
inner join
(SELECT
athlete_id, MIN(perform) AS perform
FROM
performs
WHERE
discipline_id = 4 AND category_id IN (1,3,5,7,9)
GROUP BY
athlete_id) tt
on tt.athlete_id = ss.athlete_id and ss.perform = tt.perform

结果就是您上面描述的结果。

关于mysql - 根据MySQL中的两列查找最小值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28147528/

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