一, 问题
按tid(身份编号)分组, 统计每组中新用户数和老用户数二, 建表, 初始化数据
-- 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
五, 全部 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