easydb项目需要将采集的全表扫描数据按照每个ip 就是每个主机分组,发送给相关负责人,我的设计是动态可配置的,这个后续再贴出来。
遇到的问题:由于当期有的数据达到7000条,如果直接发送给相关负责人的话邮件可能会僵住,影响心情。。。
数据库为mysql,要解决的问题是如何按照ip分组,并且其他字段不能丢,每个ip暂时先发送200条数据。
努力历程:
先试试分组吧,比如 select * from v_db_sql_scan_current group by ip limit 200;
本地mysql数据库可以执行该sql,但是测试环境报错
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'v_db_sql_scan_current.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
查询mysql系统变量
select @@sql_mode; STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
再查查测试环境系统变量
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
发现测试环境多了个:only_full_group_by
如果改sql_mode参考:
https://blog.csdn.net/wangyunfeis/article/details/77911704
当然测试库也能改,先不讨论,继续思考其他方案:
select b.rownum,a.* from v_db_sql_scan_current a inner join ( SELECT id, ip,case when @ip = ip then @order_num:=@order_num+1 else @order_num:=1 end rownum, @ip:=ip FROM v_db_sql_scan_current order by ip ) b on b.id=a.id and b.ip=a.ip where b.rownum<=200;
注意:要有排序 order by ip 就是你要统计的每个东东多少的那个字段,因为我要实现每个ip只取200条数据
有价值的文章:https://blog.csdn.net/woshihaiyong168/article/details/78803655
比如我之前写的:
SELECT DISTINCT h.DBSERVICENAME,h.DBINSTANCENAME,sc.IP,sc.TYPE,sc.OBJECT_OWNER,sc.OBJECT_NAME,sc.SIZE_MB,sc.SQL_ID,sc.PARSING_SCHEMA_NAME,sc.FIRST_LOAD_TIME,sc.LAST_ACTIVE_TIME,sc.MODULE,sc.EXECUTIONS,sc.ROWS_PER_EXEC,sc.ELAPSED_MS_PER_EXEC,sc.SQL_TEXT,sc.COLLTIME,sc.BATCHNO,sc.RUNENVID,c.DBUID,c.CNNAME,c.NICKNAME,c.RECEIVER,c.REMARK,c.DESCR,c.CC FROM edb_contact c INNER JOIN edb_host h ON c.IP=h.IP AND c.RUNENVID=h.RUNENVID AND h.USEFLAG='0' INNER JOIN (select * from v_db_sql_scan_current where id in (select aa.id from (select a.IP,count(1),max(a.id) id from v_db_sql_scan_current a group by a.ip having count(1)<=200)aa)) sc ON sc.IP=c.IP WHERE c.RUNENVID='%s'
这种写法显然不对了,having count <200 是只取200条以内的ip ,实际是我也要超过200的,只不过是要截取到200,并非不显示了。
未经允许不得转载:最优质网--最有指望 » mysql 按照某字段分组并显示其他字段值,统计某字段相同值个数并只取某条数
评论前必须登录!
登陆 注册