mysql SQL选择“分组依据”，最大值为

zazmityj 于 2022-12-17 发布在 Mysql

关注(0)|答案(1)|浏览(116)

Create Table table_name(p_id VARCHAR(50), d_id VARCHAR(50), t_id VARCHAR(50), version INT, source VARCHAR(50), name VARCHAR(50), county VARCHAR(50));
        
Insert Into table_name Values('p1','d1','t1',2,'online','penny','usa'),
        ('p1','d1','t1',2,'manual','penny','india'),
        ('p1','d1','t1',1,'online','penny','india'),
        ('p1','d1','t1',1,'manual','penny','usa'),
        ('p2','d2','t2',4,'online','david','india'),
        ('p2','d2','t2',4,'online','david','usa'),
        ('p2','d2','t2',1,'online','david','usa'),
        ('p2','d2','t2',1,'manual','david','india'),
        ('P3','d3','d3',3,'online','raj','india');
    
select * from table
    where (p_id, d_id, t_id, version)
    in (
        select p_id, d_id, t_id, version from table
            where (p_id, d_id, t_id, version)
                in ( select p_id, d_id, t_id, max(version) from table group by p_id, d_id, t_id ) //1. fetch distinct of (p_id, d_id, t_id) and with max(version)
        group by p_id, d_id, t_id, version
        having count(*)>1   //2. get only records which has both source = online and source = manual. note-- we should ignore p3 because it doesn't have both online and manual'
        );

输出：对于（p_id，d_id，t_id）的每个唯一值，我们应该获得两个具有最大可用版本的记录

results: p2-d2-t2 with version 4

p1-d1-t1  and p3-d3-t3 should be ignored because it has only one value.

mysql

来源：https://stackoverflow.com/questions/74763099/sql-select-group-by-with-max

1条答案

按热度按时间

qyswt5oh1#

可以按如下方式使用max和count窗口函数：

Select p_id, d_id, t_id, version, source, name, county  
From
(
  Select *,
    Max(version) Over (Partition By p_id, d_id, t_id) max_ver,
    Count(*) Over (Partition By p_id, d_id, t_id, version) cnt
  From table_name
) T
Where version = max_ver And cnt = 2
Order By p_id, d_id, t_id

另一种选择是，您可以按如下方式使用聚合：

Select p_id, d_id, t_id, version, source, name, county
From table_name T
Where (p_id, d_id, t_id, version) In
(
  Select p_id, d_id, t_id, Max(version)
  From table_name
  Group By p_id, d_id, t_id
)
And 
(
  Select Count(*) H From table_name D 
  Where 
  D.p_id=T.p_id And D.d_id=T.d_id And D.t_id=T.t_id And D.version=T.version
) = 2 
Order By p_id, d_id, t_id

参见demo。

赞(0）回复(0）举报 2022-12-17

我来回答

mysql SQL选择“分组依据”，最大值为

1条答案

相关问题

热门标签

最新问答