mysql日常积累（一）

知识点：

1、SUBSTRING_INDEX：字段中截取

2、mysql.help_topic：自带数据库表

3、inet_aton：ip转换整型

4、length:是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符

5、REPLACE：替换

SELECT SUM(r.in_bytes) as in_bytes, SUM(r.out_bytes) as out_bytes, SUM(r.total_bytes) as total_bytes,

r.sum_inbytes, r.sum_outbytes,r.sum_totalbytes,
r.group_name, r.group_id
FROM
(select datas.id,datas.ip_begin,datas.in_bytes,datas.out_bytes,
datas.total_bytes,datas.sum_inbytes, datas.sum_outbytes,datas.sum_totalbytes, groups.group_name, groups.group_id
from
(select u.id,u.ip_address as ip_begin,u.in_bytes,u.out_bytes,u.total_bytes,sum_inbytes,
sum_outbytes,sum_totalbytes from
(select u.id,u.ip_address,u.in_bytes,u.out_bytes,u.total_bytes
from yf_flow_users u
where record_time >= '2017-12-18 16:00'
and record_time < '2017-12-25 16:00'
)as u,
(select
sum(sum_inbytes) as sum_inbytes,sum(sum_outbytes) as
sum_outbytes,sum(sum_totalbytes) as sum_totalbytes from
(select sum_inbytes,sum_outbytes,sum_totalbytes
from yf_flow_users
where
record_time >= '2017-12-18 16:00'
and record_time < '2017-12-25 16:00'
GROUP BY sum_inbytes,sum_outbytes,sum_totalbytes
)as s)as sum
ORDER BY
total_bytes DESC) datas,
(SELECT SUBSTRING_INDEX(ips,'-',1) as start_ip,
SUBSTRING_INDEX(SUBSTRING_INDEX(ips,'-',2),'-',-1) as end_ip, g.group_name, g.group_id from 
(SELECT substring_index(substring_index(t.ip_begin,',', b.help_topic_id + 1), ',', -1) as ips, t.name as group_name, t.id as group_id
FROM yf_flow_group t join mysql.help_topic b ON b.help_topic_id < (LENGTH(t.ip_begin) - LENGTH(REPLACE(t.ip_begin, ',', '')) + 1)) g) groups
where inet_aton(datas.ip_begin) >=  inet_aton(groups.start_ip)
and inet_aton(datas.ip_begin) <=  inet_aton(groups.end_ip)) r
GROUP BY r.group_id, r.group_name,r.sum_inbytes, r.sum_outbytes,r.sum_totalbytes

ORDER BY total_bytes DESC