首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >显示重复条目的MySQL内联接查询

问显示重复条目的MySQL内联接查询
EN

Stack Overflow用户
提问于 2020-04-09 04:04:01
回答 1查看 32关注 0票数 0

我写了下面的MySQL内连接查询。Mysql版本为8.0.18-commercial

代码语言:javascript
复制
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,如下所示,但得到一个错误:

代码语言:javascript
复制
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 ;

错误:

代码语言:javascript
复制
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

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

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-04-09 04:56:07

我可以通过修改查询得到想要的结果,如下:

代码语言:javascript
复制
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 ;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/61109112

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档