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

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

 

posted @ 2021-06-29 15:06  一直在路上aiori  阅读(31)  评论(0编辑  收藏  举报