我就不多問了。直接給你答案
SELECT a.加入日期,b.num AS 加入人數,c.num AS 退出人數 FROM db AS a
LEFT JOIN (SELECT 加入日期,count(加入日期) AS num FROM db Group By 加入日期) AS b on(a.加入日期=b.加入日期)
LEFT JOIN (SELECT 退出日期,count(退出日期) AS num FROM db Group By 退出日期) AS c on(a.加入日期=c.退出日期)
GROUP BY 加入日期
CREATE TABLE my_table (
Id varchar(20),
Date_Join varchar(20),
Date_Quit varchar(20));
INSERT INTO my_table (Id,Date_Join,Date_Quit)
VALUES
('21', '2017', NULL),
('22', '2017', NULL),
('23', '2018', NULL),
('24', '2019', '2022'),
('25', '2020', NULL),
-- 額外加入
('16', '2012', NULL),
('17', '2012', '2012'),
('18', '2013', '2015'),
('19', '2014', '2015');
SELECT X.Date_All AS `加入日期`,SUM(X.Num1) AS `加入人數`,SUM(X.Num2) AS `退出人數`
FROM (
SELECT Date_Join AS 'Date_All',COUNT(Date_Join) AS Num1,0 AS Num2
FROM my_table
GROUP BY Date_Join
UNION
SELECT Date_Quit AS 'Date_All',0 AS Num,COUNT(Date_Quit) AS Num2
FROM my_table
GROUP BY Date_Quit) AS `X`
WHERE X.Date_All IS NOT NULL
GROUP BY X.Date_All
這個資料表規劃尚有精進空間,null 的筆數佔掉太多空間。
改成這樣也不錯,
id,類別(1.加入、2.退出),日期
程式碼就可以簡化了。
SELECT
加入日期,
sum(IF(類別= 1, 1, 0 ) AS 加入人數,
sum(IF(類別= 2, 1, 0 ) AS 退出人數
FROM xxx
GROUP BY 加入日期