时间戳

首页 > 解决方案 > MySQL 内部连接查询显示重复条目

mysql - MySQL 内部连接查询显示重复条目

问题描述

我写了以下MySQL内部联接查询。Mysql版本是8.0.18-commercial

select r.network->>"$[0].name" , s.server, s.ipAddr,
    from table1 e
    inner join table2 s
        on e.objectId  = s.envId
    inner join resources r
        on e.objectId  = r.envId    
    inner join tpgs g
        on e.accountId  = g.objectId      
    inner join msgTable a
        on a.id  = (select max(a.id) from msgTable a where a.logId = s.AuditId)  
    ORDER BY dateSubmitted DESC ;

但是，我收到了一些记录的重复条目。

在这里，s.server应该只出现一次。

我已添加group by如下，但出现错误：

select r.network->>"$[0].name" , s.server, s.ipAddr,
    from table1 e
    inner join table2 s
        on e.objectId  = s.envId
    inner join resources r
        on e.objectId  = r.envId    
    inner join tpgs g
        on e.accountId  = g.objectId      
    inner join msgTable a
        on a.id  = (select max(a.id) from msgTable a where a.logId = s.AuditId)  
    GROUP BY s.server
    ORDER BY dateSubmitted DESC ;

错误：

Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.s.ipAddr' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

请建议如何更新查询以消除重复行。

标签： mysqljoinselectleft-joininner-join

解决方案

我可以通过如下修改查询来获得所需的结果：

select MAX(r.network->>"$[0].name") , s.server, MAX(s.ipAddr),
    from table1 e
    inner join table2 s
        on e.objectId  = s.envId
    inner join resources r
        on e.objectId  = r.envId    
    inner join tpgs g
        on e.accountId  = g.objectId      
    inner join msgTable a
        on a.id  = (select max(a.id) from msgTable a where a.logId = s.AuditId)  
    GROUP BY s.server 
    ORDER BY dateSubmitted DESC ;

推荐阅读