先上代码,着急的同学直接复制就能用
新建一张计数表
新建 num 表
CREATE TABLE `num (
`i` int(11) NOT NULL,
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
添加数据
INSERT INTO num (`i`) VALUES ('0');
INSERT INTO num (`i`) VALUES ('1');
INSERT INTO num (`i`) VALUES ('2');
INSERT INTO num (`i`) VALUES ('3');
INSERT INTO num (`i`) VALUES ('4');
INSERT INTO num (`i`) VALUES ('5');
INSERT INTO num (`i`) VALUES ('6');
INSERT INTO num (`i`) VALUES ('7');
INSERT INTO num (`i`) VALUES ('8');
INSERT INTO num (`i`) VALUES ('9');
INSERT INTO num (`i`) VALUES ('10');
INSERT INTO num (`i`) VALUES ('11');
INSERT INTO num (`i`) VALUES ('12');
INSERT INTO num (`i`) VALUES ('13');
INSERT INTO num (`i`) VALUES ('14');
INSERT INTO num (`i`) VALUES ('15');
INSERT INTO num (`i`) VALUES ('16');
INSERT INTO num (`i`) VALUES ('17');
INSERT INTO num (`i`) VALUES ('18');
INSERT INTO num (`i`) VALUES ('19');
INSERT INTO num (`i`) VALUES ('20');
INSERT INTO num (`i`) VALUES ('21');
INSERT INTO num (`i`) VALUES ('22');
INSERT INTO num (`i`) VALUES ('23');
INSERT INTO num (`i`) VALUES ('24');
INSERT INTO num (`i`) VALUES ('25');
INSERT INTO num (`i`) VALUES ('26');
INSERT INTO num (`i`) VALUES ('27');
INSERT INTO num (`i`) VALUES ('28');
INSERT INTO num (`i`) VALUES ('29');
INSERT INTO num (`i`) VALUES ('30');
INSERT INTO num (`i`) VALUES ('31');
查询语句
SELECT
DATE_FORMAT(lefttable.date,'%Y-%m') AS date
FROM
(
SELECT
date_add(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), interval numlist.id month) AS 'date'
FROM
(
SELECT * from
(SELECT i AS id FROM num ) a
where a.id <=11
) AS numlist
WHERE
adddate(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), interval numlist.id month) <=concat(YEAR(now()),'-12-31')
) AS lefttable
GROUP BY lefttable.date
查询结果
2020-01
2020-02
2020-03
2020-04
2020-05
2020-06
2020-07
2020-08
2020-09
2020-10
2020-11
2020-12
对应业务查询今年每月订单总数
date
orderSum
2020-01
0
2020-02
0
2020-03
0
2020-04
0
2020-05
84
2020-06
14
2020-07
0
2020-08
0
2020-09
0
2020-10
0
2020-11
0
2020-12
0
关键字
DATE_FORMAT(date,format)
这个不用多说吧,都知道,日期格式化嘛
参数:
date: 日期
format: 格式('%Y-%m-%d')
DATE_ADD(date,INTERVAL expr unit)
定义:函数向日期添加指定的时间间隔。
参数:
date: 参数是合法的日期表达式
expr: 参数是您希望添加的时间间隔。
type 参数可以是下列值:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
示例
假设我们有如下的表:
OrderId
ProductName
OrderDate
1
Computer
2008-12-29
现在,我们希望向 “OrderDate” 添加 2 天,这样就可以找到付款日期。我们使用下面的 SELECT 语句:
SELECT
OrderId,
DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM
Orders
结果:
OrderId
OrderPayDate
1
2008-12-31
DATE_SUB(date,INTERVAL expr type)
定义:DATE_SUB() 函数从日期减去指定的时间间隔。
参数:
date: 参数是合法的日期表达式
expr: 参数是您希望添加的时间间隔。
type 参数可以是下列值:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
示例
假设我们有如下的表:
OrderId
ProductName
OrderDate
1
Computer
2008-12-29
现在,我们希望从 “OrderDate” 减去 2 天。我们使用下面的 SELECT 语句:
SELECT
OrderId,
DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM
Orders
结果:
OrderId
OrderPayDate
1
2008-12-27
DAYOFYEAR(date)
定义:返回年份为日期的天,范围为1至366。
参数:
date: 日期
YEAR(date)
定义:从指定日期值中来获取年份值
参数:
date: 日期