MySQL 命令行 导出数据 加状态判断 导出excel格式 ip地址转换 直接导出excel文件

 

直接导出excel文件

SELECT user.userid,department.`name`,user.username,user.name,user.`email`,keyusage.`productname`,keyusage.`status`,(case when keyusage.status=1 then '申请激活' when keyusage.status=2 then '激活成功' else '激活失败' end) as flag,INET_NTOA(keyusage.`ip`),keyusage.`computerid`,keyusage.`errorcode`,keyusage.`enddate` FROM keyusage LEFT JOIN `user` ON user.userid=keyusage.userid INNER JOIN department ON user.`departmentid`=department.`departmentid` WHERE keyusage.`enddate`>'2022-06-01 00:00:00' into outfile '/tmp/list.xlsx' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

 

分类统计
SELECT productname,COUNT(*) FROM keyusage WHERE enddate>'2022-06-01 00:00:00' and enddate<'2023-07-01 00:00:00' GROUP BY productname into outfile '/tmp/list.xlsx' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';