在MySQL中两次使用GROUP BY [英] Use of GROUP BY twice in MySQL

问题描述

我的桌子看起来像这样.

My table looks like this.

Location    Head    Id  IntTime
1           AMD     1   1
2           INTC    3   3
3           AMD     2   2
4           INTC    4   4
5           AMD2    1   0
6           ARMH    5   1
7           ARMH    5   0
8           ARMH    6   1
9           AAPL    7   0
10          AAPL    7   1

位置是主键.我需要使用GROUP BY Head和ID，并且在使用GROUP BY时，我需要使行保持最小的IntTime.

Location is the primary key. I need to GROUP BY Head and by Id and when I use GROUP BY, I need to keep the row with the smallest IntTime.

在第一个GROUP BY ID之后，我应该得到(我保持最小的IntTime)

After the first GROUP BY Id, I should get (I keep the smallest IntTime)

Location    Head    Id  IntTime
2           INTC    3   3
3           AMD     2   2
4           INTC    4   4
5           AMD2    1   0
7           ARMH    5   0
8           ARMH    6   1
9           AAPL    7   0

第二个GROUP BY头之后，我应该得到(我保持最小的IntTime)

After the second GROUP BY Head, I should get (I keep the smallest IntTime)

Location    Head    Id  IntTime
2           INTC    3   3
3           AMD     2   2
5           AMD2    1   0
7           ARMH    5   0
9           AAPL    7   0

当我运行以下命令时，我保留了最小的IntTime，但行不守恒.

When I run the following command, I keep the smallest IntTime but the rows are not conserved.

SELECT Location, Head, Id, MIN(IntTime) FROM test 
GROUP BY Id

另外，要运行第二个GROUP BY，我保存此表并再次执行

Also, to run the second GROUP BY, I save this table and do again

SELECT Location, Head, Id, MIN(IntTime) FROM test2 
GROUP BY Head

有没有办法组合两个命令?

Is there a way to combine both commands?

结果不应包含两个具有相同值的Head或两个具有相同值的ID. 删除这些重复项时，应保留IntTime最小的行.

The result should not contain two Head with the same value or two Id with the same value. When deleting those duplicates, the row with the smallest IntTime should be kept.

推荐答案

此查询返回您正在寻找的确切最终结果(示例):

This query returns the exact final results you're looking for (example):

SELECT `final`.*
FROM `tableName` AS `final`
JOIN (
    SELECT `thead`.`Id`, `Head`, MIN(`intTime`) AS `min_intTime`
    FROM `tableName` AS `thead`
    JOIN (
        SELECT `Id`, MIN(intTime) as min_intTime
        FROM `tableName` AS `tid`
        GROUP BY `Id`
    ) `tid`
    ON `tid`.`Id` = `thead`.`Id`
    AND `tid`.`min_intTime` = `thead`.`intTime`
    GROUP BY `Head`
) `thead`
ON `thead`.`Head` = `final`.`Head`
AND `thead`.`min_intTime` = `final`.`intTime`
AND `thead`.`Id` = `final`.`Id`

工作原理

最里面的查询按Id分组，并返回Id和相应的MIN(intTime).然后，中间查询按Head分组，并返回Head和相应的Id和MIN(intTime).缩小后，最终查询返回所有行.您可以将最终查询(最外面的查询)视为仅包含所需行的表查询，因此可以进行其他比较(例如WHERE final.intTime > 3).

How it works

The innermost query groups by Id, and returns the Id and corresponding MIN(intTime). Then, the middle query groups by Head, and returns the Head and corresponding Id and MIN(intTime). The final query returns all rows, after being narrowed down. You can think of the final (outermost) query as a query on a table with only the rows you want, so you can do additional comparisons (e.g. WHERE final.intTime > 3).

这篇关于在MySQL中两次使用GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！