统计分组中多列数据

谁借莪１个温暖的怀抱￠ 2021-11-11 02:26 351阅读 0赞

# 一, 问题 #

按tid(身份编号)分组, 统计每组中新用户数和老用户数

# 二, 建表, 初始化数据 #

# 三, 分别求新老用户 #

-- 老用户: ~ 2013-07-01
    SELECT tid, birth
    FROM test1
    WHERE birth < '2013-07-01'
    
    -- 新用户: 2013-07-01 ~ 
    SELECT tid, birth
    FROM test1
    WHERE birth >= '2013-07-01'

# 四, 一次求出个分组中新老用户数 #

-- 求新老用户
    SELECT 	tid AS '省份', 
    	COUNT(CASE WHEN birth < '2013-07-01' THEN 1 ELSE NULL END) AS '老用户',
    	COUNT(CASE WHEN birth >= '2013-07-01' THEN 1 ELSE NULL END) AS '新用户'	
    FROM test1
    GROUP BY tid

![SouthEast][]

# 五, 全部 sql #

-- drop table test1;
    CREATE TABLE `test1` (
    	`id` INT (11),
    	`name` VARCHAR (60),
    	`birth` DATETIME ,
    	`tid` VARCHAR (60)
    ); 
    INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('1','a','2013-07-01 00:00:00','11');
    INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('2','b','2013-06-01 00:00:00','22');
    INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('3','c','2013-08-01 00:00:00','11');
    INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('4','d','2013-07-01 00:00:00','22');
    INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('5','e','2013-08-01 00:00:00','11');
    
    SELECT * FROM test1;
    
    
    -- 老用户: ~ 2013-07-01
    SELECT tid, birth
    FROM test1
    WHERE birth < '2013-07-01'
    
    -- 新用户: 2013-07-01 ~ 
    SELECT tid, birth
    FROM test1
    WHERE birth >= '2013-07-01'
    
    
    -- 求新老用户
    SELECT 	tid AS '省份', 
    	COUNT(CASE WHEN birth < '2013-07-01' THEN 1 ELSE NULL END) AS '老用户',
    	COUNT(CASE WHEN birth >= '2013-07-01' THEN 1 ELSE NULL END) AS '新用户'	
    FROM test1
    GROUP BY tid

[SouthEast]: /images/20211109/5fbbe0f07ef54c65aa540e8d03fbda91.png