mysql查询去年1-12所有月份，查询今年1月至当前月的数据，查询今年1月至12月所有数据

 

-----------------------查询去年1-12月所有的月份，粘贴即可运行

SELECT
CASE

WHEN
length( mon ) = 1 THEN
concat( LEFT ( CURRENT_DATE, 5)-1, '-0', mon ) ELSE concat( LEFT ( CURRENT_DATE, 5 )-1,'-', mon )
END months
FROM
( SELECT @m := @m + 1 mon FROM t_mscm_sales, ( SELECT @m := 0 ) a ) aa
LIMIT 12

-----------------------------------查询去年1-12月所有数据，若当月无数据，展示0

SELECT
a.months,
IFNULL( ROUND( b.total / 10000, 2 ), 0 ) total
FROM
(
SELECT
CASE

WHEN
length( mon ) = 1 THEN
concat( LEFT ( CURRENT_DATE, 5 ) - 1, '-0', mon ) ELSE concat( LEFT ( CURRENT_DATE, 5 ) - 1, '-', mon )
END months
FROM
( SELECT @m := @m + 1 mon FROM t_mscm_sales, ( SELECT @m := 0 ) a ) aa
LIMIT 12
) a
LEFT JOIN ( SELECT SUM( f_total ) total, DATE_FORMAT( update_time, '%Y-%m' ) time, c_number FROM t_mscm_sales WHERE n_states = 6 GROUP BY time ) b ON a.months = b.time
ORDER BY
a.months

 

 

 

 ------------------------------------查询今年1月至当前月数据，若无数据展示0

 

 

 

SELECT m.months,
IFNULL( ROUND( t.total / 10000, 2 ), 0 ) total
FROM
(
SELECT
CASE

WHEN
length( mon ) = 1 THEN
concat( LEFT ( CURRENT_DATE, 5 ), '0', mon ) ELSE concat( LEFT ( CURRENT_DATE, 5 ), mon )
END months
FROM
( SELECT @m := @m + 1 mon FROM t_mscm_sales, ( SELECT @m := 0 ) a ) aa
LIMIT 9
) m
LEFT JOIN (
SELECT
SUM( f_total ) total,
DATE_FORMAT( update_time, '%Y-%m' ) update_time
FROM
t_mscm_sales
WHERE
n_states = 6
GROUP BY
DATE_FORMAT( update_time, '%Y-%m' )
) t ON m.months = t.update_time
ORDER BY
m.months