按月份分组，并在mysql中按时间而非字母顺序显示 - group by months and display chronologically and not alphabetically in mysql

group by months and display chronologically and not alphabetically in mysql

本文关键字：显示顺序时间 mysql 并在 | 更新日期: 2023-12-10

虽然查询返回结果，但按月份分组按字母顺序返回结果，如四月、八月、六月。。。但它应该按时间顺序返回结果，如1月、2月、，。。。。

SELECT Concat(Monthname(a.fdate), '-', Year(a.fdate)) AS Month, 
       s.new_state AS State, 
       d.new_dist AS District, 
       b.ifbook AS Book, 
       Sum(a.amt) AS Amount 
FROM   str a 
       JOIN sc b 
         ON b.scd = a.isc 
       JOIN USER c 
         ON a.ed = c.str 
       JOIN state_mapping s 
         ON b.state = s.org_state 
       JOIN dist_mapping d 
         ON b.dist = d.org_dist 
WHERE  Trim(b.ifbook) <> '' 
       AND b.ifbook IS NOT NULL 
       AND b.ifbook NOT LIKE '%TR%' 
       AND Trim(d.new_dist) <> '' 
       AND d.new_dist IS NOT NULL 
GROUP  BY b.ifbank, 
          d.new_dist, 
          s.new_state, 
          month

如何修改查询以实现相同的目的？

在对您的模型一无所知的情况下，我可以建议您尝试一下：

select
  concat(monthname(a.fdate),'-',year(a.fdate)) Month,
  s.new_state State,
  d.new_dist District,
  b.ifbook Book,
  sum(a.amt) Amount
from str a
JOIN sc b on b.scd = a.isc
JOIN user c on a.ed = c.str
JOIN state_mapping s on b.state = s.org_state
JOIN dist_mapping d on b.dist = d.org_dist
where TRIM(b.ifbook) <> '' and b.ifbook IS NOT NULL and b.ifbook not like '%TR%'
  and TRIM(d.new_dist) <> '' and d.new_dist IS NOT NULL
group by b.ifbank, d.new_dist, s.new_state, Month
order by year(a.fdate), month(a.fdate)