根据一个表中同一字段的不同值进行统计！！！

method1: 

table sql:

CREATE TABLE `tpr_click` (
  `id` int(25) NOT NULL AUTO_INCREMENT,
  `user` char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `clickTime` timestamp NULL DEFAULT NULL,
  `tpr` char(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `csys` char(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'bios',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=135 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

 

这个方法适用于 字段比较少，数据比较少时使用

SELECT user,
COUNT(CASE WHEN csys = 'bios' THEN 1 END) AS bios_count,
COUNT(CASE WHEN csys = 'eco' THEN 1 END) AS eco_count,
COUNT(CASE WHEN csys = 'program' THEN 1 END) AS program_count,
COUNT(CASE WHEN csys = 'npi' THEN 1 END) AS npi_count,
COUNT(CASE WHEN csys = 'testlog' THEN 1 END) AS testlog_count,
COUNT(CASE WHEN csys = 'report' THEN 1 END) AS report_count
FROM tpr_click group by user

method2：

group by

select * ，count（*） from tpr_click   group by 字段1，字段2  
group by 可以多个条件分组！！！

扩展：根据 用户 user 和 csys 分组统计，并且连接最近一次的clickTime 形成一张表：

select t1.id,t1.user,t1.tpr,t1.csys,t1.count,t2.latestClick,t2.user,t2.csys from  
(select *,count(*) as count from tpr_click where user!='' group by user,csys) t1 
right JOIN
(select DISTINCT id,user,clickTime latestClick,tpr, csys from 
tpr_click where clickTime > '2021-06-25 00:00:00' and clickTime < '2021-06-29 23:59:59' 
group by clickTime ORDER BY clickTime desc) t2 on t1.user=t2.user and t1.csys=t2.csys  group by t1.user,t1.csys