我写了下面的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请建议如何更新查询以消除重复行。
发布于 2020-04-09 04:56:07
我可以通过修改查询得到想要的结果,如下:
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 ;https://stackoverflow.com/questions/61109112
复制相似问题