by取最大值 - mysql group最大値




為每組分組的SQL結果獲取最大值的記錄 (12)

axiac的解決方案是最適合我的。 然而,我有一個額外的複雜性:計算“最大值”,來自兩列。

讓我們用同樣的例子:我想每組中最老的人。 如果有同樣老的人,請帶上最高的人。

我必須執行左連接兩次才能獲得此行為:

SELECT o1.* WHERE
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o1
LEFT JOIN
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o2
ON o1.Group = o2.Group AND o1.Height < o2.Height 
WHERE o2.Height is NULL;

希望這可以幫助! 我想應該有更好的方法來做到這一點,雖然...

你如何獲得包含每個分組集合的最大值的行?

我在這個問題上看到了一些過於復雜的變體,沒有一個答案很好。 我試圖把最簡單的例子放在一起:

給出如下的表格,包括人員,組別和年齡欄,你如何得到每個組中最年長的人? (組內聯繫應該給出第一個按字母順序排列的結果)

Person | Group | Age
---
Bob  | 1     | 32  
Jill | 1     | 34  
Shawn| 1     | 42  
Jake | 2     | 29  
Paul | 2     | 36  
Laura| 2     | 39  

預期結果集:

Shawn | 1     | 42    
Laura | 2     | 39  

不確定MySQL是否具有row_number函數。 如果是這樣,你可以使用它來獲得所需的結果。 在SQL Server上,你可以做類似於:

CREATE TABLE p
(
 person NVARCHAR(10),
 gp INT,
 age INT
);
GO
INSERT  INTO p
VALUES  ('Bob', 1, 32);
INSERT  INTO p
VALUES  ('Jill', 1, 34);
INSERT  INTO p
VALUES  ('Shawn', 1, 42);
INSERT  INTO p
VALUES  ('Jake', 2, 29);
INSERT  INTO p
VALUES  ('Paul', 2, 36);
INSERT  INTO p
VALUES  ('Laura', 2, 39);
GO

SELECT  t.person, t.gp, t.age
FROM    (
         SELECT *,
                ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row
         FROM   p
        ) t
WHERE   t.row = 1;

你可以加入一個抽取MAX(Group)Age的子查詢。 這種方法在大多數RDBMS中是可移植的。

SELECT
  yourtable.*
FROM
  yourtable
  JOIN (
    SELECT `Group`, MAX(Age) AS age
    FROM yourtable 
    GROUP BY `Group`
  ) maxage
     /* join subquery against both Group and Age values */
     ON yourtable.`Group` = maxage.`Group` 
        AND yourtable.Age = maxage.age

使用CTE - 公用表表達式:

WITH MyCTE(MaxPKID, SomeColumn1)
AS(
SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1
FROM MyTable1 a
GROUP BY a.SomeColumn1
  )
SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado)
FROM MyTable1 b
INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID
GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2

--Note: MyTablePKID is the PrimaryKey of MyTable

因為它是保留字,所以我不會將Group用作列名。 但是,下面的SQL會起作用。

SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a
INNER JOIN 
(
  SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME] 
  GROUP BY `Group`
) b ON a.Group = b.Group AND a.Age = b.oldest

在mysql中有這樣一個超級簡單的方法:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

這是可行的,因為在mysql中你可以聚合非分組列,在這種情況下,mysql只返回第一行。 解決方案是首先對數據進行排序,以便為每個組選擇您想要的行,然後按您想要的值進行分組。

你避免了複雜的子查詢,它們試圖找到max()等等,還有當有多個具有相同最大值的多行時返回多行的問題(和其他答案一樣)

注意:這是一個僅限mysql的解決方案。 我所知道的所有其他數據庫都會在消息“非聚合列未列在group by子句中”或類似消息中引發SQL語法錯誤。 由於此解決方案使用未記錄的行為,因此更謹慎的方法可能需要包含一個測試來聲明在未來版本的MySQL更改此行為時它仍然可以正常工作。

版本5.7更新:

自5.7版以來,默認情況下, sql-mode設置包括ONLY_FULL_GROUP_BY ,所以為了使其工作,您不能使用此選項(編輯服務器的選項文件以刪除此設置)。


我有一個簡單的解決方案,使用WHERE IN

SELECT a.* FROM `mytable` AS a    
WHERE a.age IN( SELECT MAX(b.age) AS age FROM `mytable` AS b GROUP BY b.group )    
ORDER BY a.group ASC, a.person ASC

我的解決方案僅適用於只需要檢索一列的情況,但對於我的需求而言,它是性能方面最好的解決方案(它只使用一個查詢!):

SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
   column_z
FROM table_name
GROUP BY column_z;

它使用GROUP_CONCAT為了創建一個有序的連續列表,然後我只將substring分配給第一個。


正確的解決方案是:

SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found

怎麼運行的:

它將來自o每一行與來自b所有行在列Group具有相同的值並且在列Age具有較大的值相匹配。 o列中沒有其組的最大值的任何行將匹配來自b一行或多行。

LEFT JOIN使它與來自b ('沒有最大年齡的組')中的滿足NULL的行匹配組中最老的人(包括在他們組中單獨的人)。
使用INNER JOIN使這些行不匹配,它們被忽略。

WHERE子句只保留從b提取的字段中具有NULL s的行。 他們是每個組別中年齡最大的人。

更多的讀物

SQL解決方案:避免數據庫編程的陷阱 “一書中解釋了這個解決方案和許多其他解決方案


讓表名成為人

select O.*              -- > O for oldest table
from people O , people T
where O.grp = T.grp and 
O.Age = 
(select max(T.age) from people T where O.grp = T.grp
  group by T.grp)
group by O.grp; 

這種方法的好處是可以讓您按照不同的列進行排名,而不會甩掉其他數據。 在您嘗試列出項目列的訂單時,它非常有用,首先列出最重的項目。

資料來源: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat : http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT person, group,
    GROUP_CONCAT(
        DISTINCT age
        ORDER BY age DESC SEPARATOR ', follow up: '
    )
FROM sql_table
GROUP BY group;

with CTE as 
(select Person, 
[Group], Age, RN= Row_Number() 
over(partition by [Group] 
order by Age desc) 
from yourtable)`


`select Person, Age from CTE where RN = 1`




greatest-n-per-group