我有表"数据",有两个字段是Date_date1,也是Data_date2,我想根据月份进行计数。 这是我的数据库
Table: Data
Data_date1 Data_date2
---------------------------------
2019-07-23 2019-01-23
2019-08-23 2019-01-24
2019-08-24 2019-02-23
2019-09-21 2019-07-23
2019-09-22 2019-09-22
2019-09-23 2019-09-23
我想要这样的结果
Month Count_Date1 Count_Date2
Jan 0 2
Feb 0 1
July 1 1
Aug 2 0
Sep 3 9
试试这个:
SELECT MONTH(data_date) m
,SUM(d=1) d1
,SUM(d=2) d2
FROM
(SELECT 1 d, data_date1 data_date FROM my_table
UNION
SELECT 2, data_date2 FROM my_table
) x
GROUP BY m
下面是用于测试此查询的一些设置,该查询可生成所需的结果:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(data_date1 DATE NOT NULL
,data_date2 DATE NOT NULL
);
INSERT INTO my_table VALUES
('2019-07-23','2019-01-23'),
('2019-08-23','2019-01-24'),
('2019-08-24','2019-02-23'),
('2019-09-21','2019-07-23'),
('2019-09-22','2019-09-22'),
('2019-09-23','2019-09-23');
您可以使用union all
和group by
:
select month(dte), sum(cnt1), sum(cnt2)
from ((select data_date1 as dte, 1 as cnt1, 0 as cnt2
from t
) union all
(select data_date2, 0, 1
from t
)
) dd
group by month(dte);
这将显示月份编号而不是月份名称。
如果你想要月份名称,你可以做:
select monthname(dte), sum(cnt1), sum(cnt2)
from ((select data_date1 as dte, 1 as cnt1, 0 as cnt2
from t
) union all
(select data_date2, 0, 1
from t
)
) dd
group by monthname(dte), month(dte)
order by month(dte);