mysql中如何查詢一段時間的日期

182次閱讀

共計 1992 個字符，預計需要花費 5 分鐘才能閱讀完成。

這篇文章給大家分享的是有關 mysql 中如何查詢一段時間的日期的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。

mysql 中查詢一段時間的日期，要把沒數據的日期也展示出來的方法如下：h2 >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 2017-11-01
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 2017-11-01
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 表要超過你查詢的天數

感謝各位的閱讀！關于“mysql 中如何查詢一段時間的日期”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

正文完