mysql查询date不带时间_mysql中查询一段时间的日期，要把没数据的日期也展示出来的方法如下：...

mysql中查询一段时间的日期，要把没数据的日期也展示出来的方法如下：mysql获取两个日期内的所有日期列表：

select  t1.create_time ,ifnull(t2.yuyin_order_nums,0)yuyin_order_nums,IFNULL(t2.yuyin_ordersuccess_nums,0) yuyin_ordersuccess_nums,

ifnull(t2.yuyin_percent,'0.00%')yuyin_percent,ifnull(t2.yuyin_deal_nums,0)yuyin_deal_nums,

ifnull(t3.shaoma_order_nums,0)shaoma_order_nums,IFNULL(t3.shaoma_ordersuccess_nums,0) shaoma_ordersuccess_nums,

ifnull(t3.shaoma_percent,'0.00%')shaoma_percent,ifnull(t3.shaoma_deal_nums,0)shaoma_deal_nums

from

(select @num:=@num+1,date_format(adddate('2017-10-01', INTERVAL @num DAY)-1,'%Y-%m-%d') as create_time

from np_order,(select @num:=0) t where adddate('2017-10-01', INTERVAL @num DAY) <= date_format('2017-11-01','%Y-%m-%d')

order by create_time ) t1

LEFT JOIN

(

select  DATE_FORMAT(m.create_time,'%Y-%m-%d') create_time ,count(1) yuyin_order_nums ,

sum(

CASE  when  m.order_status ='2' then

1

ELSE

0

end ) yuyin_ordersuccess_nums,

concat(FORMAT((

sum(

CASE  when  m.order_status ='2' then

1

ELSE

0

end )/count(1))*100,2),'%')  yuyin_percent,

sum(

CASE  when  m.order_status ='4' then

1

ELSE

0

end ) yuyin_deal_nums

from  np_order  m  where  m.order_m_type in ('19')

and   m.create_time >='2017-08-01'

and   m.create_time

group by DATE_FORMAT(m.create_time,'%Y-%m-%d') ) t2

on  t1.create_time =t2.create_time

LEFT JOIN

(

select  DATE_FORMAT(m.create_time,'%Y-%m-%d') create_time ,count(1) shaoma_order_nums ,

sum(

CASE  when  m.order_status ='2' then

1

ELSE

0

end ) shaoma_ordersuccess_nums,

concat(FORMAT((

sum(

CASE  when  m.order_status ='2' then

1

ELSE

0

end )/count(1))*100,2),'%')  shaoma_percent,

sum(

CASE  when  m.order_status ='4' then

1

ELSE

0

end ) shaoma_deal_nums

from  np_order  m  where  m.order_m_type in ('20')

and   m.create_time >='2017-08-01'

and   m.create_time

group by DATE_FORMAT(m.create_time,'%Y-%m-%d') ) t3

on t1.create_time =t3.create_time

order by t1.create_time ;

此方法优点就是不需要创建存储过程或者是日历表，缺点就是你必须要有一个表，它的数据条数大到足够支撑你要查询的天数,也就是说比如

np_order  表 总条数是 20条的话，那你查询的时间是一个月，那就需要30条数据，那最多会只给你展示20条，所以要保证 np_order 表要超过你查询的天数

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31369373/viewspace-2146893/，如需转载，请注明出处，否则将追究法律责任。