gpt4 book ai didi

mysql - 按当前月份订购月份 TABLE

转载 作者:行者123 更新时间:2023-11-29 02:16:42 25 4
gpt4 key购买 nike

我想对月份进行排序,当前月份位于最终位置。

我有一个返回这个的子查询:

+-----+-----------+
| one | monthname |
+-----+-----------+
| 1 | Jan |
| 2 | Feb |
| 3 | Mar |
| 4 | Apr |
| ... | ... |
+-----+-----------+

然后我想命令它获取当前月份 (MONTH(NOW())) 作为最后一个值。但我一直收到默认的月份订单。

例如,今天,在七月(第 7 个月),我想要:

+-----+-----------+
| one | monthname |
+-----+-----------+
| 8 | Aug |
| 9 | Sep |
| 10 | Oct |
| 11 | Nov |
| 12 | Dec |
| 1 | Jan |
| 2 | Feb |
| 3 | Mar |
| 4 | Apr |
| 5 | Mei |
| 6 | Jun |
| 7 | Jul |
+-----+-----------+

是否可以在 SQL 查询中执行?如果是这样,最好的方法是什么?

编辑

我当前的查询如下

SELECT `months`,
IFNULL(case_result.Total_rows,0) results
FROM
(SELECT `one`,`monthname` AS months FROM
(SELECT 1 AS `one`, 'JANUARY' AS `monthname`
UNION SELECT 2 AS `one`,'FEBURARY' AS `monthname`
UNION SELECT 3 AS `one`, 'MARCH' AS `monthname`
UNION SELECT 4 AS `one`, 'APRIL' AS `monthname`
UNION SELECT 5 AS `one`, 'MAY' AS `monthname`
UNION SELECT 6 AS `one`,'JUNE' AS `monthname`
UNION SELECT 7 AS `one`,'JULY' AS `monthname`
UNION SELECT 8 AS `one`, 'AUGUST' AS `monthname`
UNION SELECT 9 AS `one`, 'SEPTEMBER' AS `monthname`
UNION SELECT 10 AS `one`, 'OCTOBER' AS `monthname`
UNION SELECT 11 AS `one`, 'NOVEMBER' AS `monthname`
UNION SELECT 12 AS `one`, 'DECEMBER' AS `monthname` order by ) AS month_table) tempmonths LEFT JOIN (SELECT MONTH(date_surgery) AS `month`, COUNT(id_cases) AS Total_Rows
FROM cases
WHERE date_surgery BETWEEN DATE_SUB(NOW(), INTERVAL 11 MONTH) AND NOW()
AND cases.name = 'Bone Tumor'
GROUP BY MONTH(`date_surgery`)) AS case_result ON tempmonths.one = case_result.month

但是我需要订购的部分是这个子查询

SELECT 1 AS `one`, 'JANUARY' AS `monthname` 
UNION SELECT 2 AS `one`,'FEBURARY' AS `monthname`
UNION SELECT 3 AS `one`, 'MARCH' AS `monthname`
UNION SELECT 4 AS `one`, 'APRIL' AS `monthname`
UNION SELECT 5 AS `one`, 'MAY' AS `monthname`
UNION SELECT 6 AS `one`,'JUNE' AS `monthname`
UNION SELECT 7 AS `one`,'JULY' AS `monthname`
UNION SELECT 8 AS `one`, 'AUGUST' AS `monthname`
UNION SELECT 9 AS `one`, 'SEPTEMBER' AS `monthname`
UNION SELECT 10 AS `one`, 'OCTOBER' AS `monthname`
UNION SELECT 11 AS `one`, 'NOVEMBER' AS `monthname`
UNION SELECT 12 AS `one`, 'DECEMBER' AS `monthname` order by ...

最佳答案

如果 one 是具有月份编号的列的名称,只需将其添加到您的 select 语句中:

order by (one+11-month(now)) % 12

对于当前月份,该表达式将为 11,对于所有其他月份,由于模运算符 (%),该表达式将变小。

关于mysql - 按当前月份订购月份 TABLE,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38459521/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com