SELECT ADDDATE(y.first, x.d - 1) as d,x.d as b
创新互联公司自2013年起,先为云和等服务建站,云和等地企业,进行企业商务咨询服务。为云和企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
FROM
(
SELECT @xi:=@xi+1 as d from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) xc2,
(SELECT 1 ) xc3,
(SELECT @xi:=0) xc0
) X,
(
SELECT '2018-11-01' - INTERVAL DAY('2018-11-01') - 1 DAY AS first,
DAY(LAST_DAY('2018-11-01')) AS last) Y
WHERE x.d = y.last
或者
SELECT ADDDATE(y.first, x.d - 1) as d
FROM
(
SELECT 1 AS d UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 26 UNION ALL
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 31
) x,
(
SELECT '2018-11-01' - INTERVAL DAY('2018-11-01') - 1 DAY AS first,
DAY(LAST_DAY('2018-11-01')) AS last) y
WHERE x.d = y.last
然后你应该知道了吧
SELECT
d.d,
count(t.id)
FROM
(
-- 这里贴前面两段代码中的任意一段
) d
LEFT JOIN
table t ON t.day = d.d -- 这个table是你要查的数据表
GROUP BY
d.d
select
*
from
shipmentlist,shipmentscrib
where
(shipmentlist.shipmentlistno=shipmentscrib.shipmentlistno)
and (year(shipmentlist.shipmentdate)=year(now()))
and (month(shipmentlist.shipmentdate)=month(now())
or month(shipmentlist.shipmentdate)=month(now())-1 )
ORDER BY shipmentdate DESC
格式化了一下你的SQL,分析一下。
假如今天是 2012年1月1日。
那么上面的条件。
将变为
year = 2012 and month = 1 OR month = 0
其实,对于 查询 当月和上一月
相当于
shipmentlist.shipmentdate = 上月的1号
AND shipmentlist.shipmentdate 下月的1号
LAST_DAY(NOW()) 可以获取 本月的最后一天.
DATE_ADD( LAST_DAY(NOW()) INTERVAL 1 DAY ) 可以获取下月第一天。
DATE_SUB ( DATE_ADD( LAST_DAY(NOW()) INTERVAL 1 DAY ) INTERVAL 2 MONTH ) 可以获取上月的1号
(也就是用 下月的1号 减少2个月,从而获取 上月的1号)
最后 SQL 修改为:
select
*
from
shipmentlist,shipmentscrib
where
(shipmentlist.shipmentlistno=shipmentscrib.shipmentlistno)
and shipmentlist.shipmentdate = DATE_SUB ( DATE_ADD( LAST_DAY(NOW()) INTERVAL 1 DAY ) INTERVAL 2 MONTH )
AND shipmentlist.shipmentdate DATE_ADD( LAST_DAY(NOW()) INTERVAL 1 DAY )
ORDER BY shipmentdate DESC
select * from 表 where date_format(日期,'%Y-%m-%d')='2014-04-01' 日期
select * from 表 where date_format(日期,'%Y-%m')='2014-04' 月份
select * from 表 where date_format(日期,'%Y')='2014' 年
就是date_format(日期,'%Y-%m-%d') 这里的参数长短
网页题目:mysql在1月份怎么查 mysql年月日查询
标题来源:https://www.cdcxhl.com/article48/ddsjohp.html
成都网站建设公司_创新互联,为您提供企业建站、网站设计公司、建站公司、关键词优化、网站收录、软件开发
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联