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

I have written following MySQL Inner Join query. The Mysql version is 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 ;

但是,我得到很少的记录重复的条目。

Here, s.server should appear only once.

I have added group by as below but getting an error:

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

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

评论