英文:
Sorting result based on the date by year followed by month in oracle sql query
问题
Here is the translated code snippet:
我有这个查询来按Month_Year和计数格式分组和显示结果。SELECTTO_CHAR (D_DATE, 'MON')|| '_'|| TO_CHAR (D_DATE, 'YYYY') Month_Year,COUNT (*) Request_CountFROMMY_TABLE WHERE V_TYPE='ABCD'GROUP BYTO_CHAR (D_DATE, 'MON') || '_' || TO_CHAR (D_DATE, 'YYYY')ORDER BYTO_CHAR (D_DATE, 'YYYY'), TO_CHAR (D_DATE, 'MON');
The code will now sort the output first by year and then by month as requested.
英文:
I have this query to group and show results in Month_yeat and count format.
SELECTTO_CHAR (D_DATE,'MON')|| '_'|| TO_CHAR (D_DATE,'YYYY') Month_Year,COUNT (*) Request_CountFROMMY_TABLE WHERE V_TYPE='ABCD'GROUP BYTO_CHAR (D_DATE,'MON') || '_' || TO_CHAR (D_DATE,'YYYY')ORDER BY1;
I require the output should be sorted based on the year first then month.
eg:
The current output is :
MAR_2023 2223FEB_2023 433MAY_2022 10APR_2022 77
Required output is :
FEB_2023 433MAR_2023 2223APR_2022 77MAY_2022 10
I know that this is sorting based on alphabetical if i give the condition in order by, but need a solution which can solve this problem
Note : grouping year and month as separate column and sorting on it is not acceptable.
答案1
得分: 1
你可以这样做，但需要额外添加group by和order by子句，例如：
SELECT TO_CHAR (D_DATE, 'MON') || '_' || TO_CHAR (D_DATE, 'YYYY') Month_Year,COUNT (*) Request_CountFROM MY_TABLEWHERE V_TYPE = 'ABCD'GROUP BY TO_CHAR (D_DATE, 'MON') || '_' || TO_CHAR (D_DATE, 'YYYY'),TO_CHAR (d_date, 'yyyymm')ORDER BY TO_CHAR (d_date, 'yyyymm')
order by确保行正确排序group by随后也需要（否则会出现“ORA-00979: not a GROUP BY expression”错误）
英文:
You can, but with additional part of group by and order by clause, e.g.
SELECT TO_CHAR (D_DATE, 'MON') || '_' || TO_CHAR (D_DATE, 'YYYY') Month_Year,COUNT (*) Request_CountFROM MY_TABLEWHERE V_TYPE = 'ABCD'GROUP BY TO_CHAR (D_DATE, 'MON') || '_' || TO_CHAR (D_DATE, 'YYYY'),TO_CHAR (d_date, 'yyyymm')ORDER BY TO_CHAR (d_date, 'yyyymm')
order bywill make sure that rows are correctly sortedgroup bythen requires it as well (otherwise you'll get "
ORA-00979: not a GROUP BY expression" error)
答案2
得分: 0
将日期截断到月初，按照这个日期分组，然后按截断日期而不是格式化字符串排序。
SELECT TO_CHAR(TRUNC(D_DATE, 'MM'), 'MON_YYYY') 月份_年份,COUNT (*) 请求数量FROM MY_TABLEWHERE V_TYPE='ABCD'GROUP BY TRUNC(D_DATE, 'MM')ORDER BY TRUNC(D_DATE, 'MM');
对于示例数据：
CREATE TABLE my_table (v_type, d_date) ASSELECT 'ABCD', DATE '2022-01-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 19 UNION ALLSELECT 'ABCD', DATE '2023-01-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 12 UNION ALLSELECT 'ABCD', DATE '2023-02-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 5 UNION ALLSELECT 'ABCD', DATE '2023-03-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 7;
输出：
| 月份_年份 | 请求数量 |
|---|---|
| JAN_2022 | 19 |
| JAN_2023 | 12 |
| FEB_2023 | 5 |
| MAR_2023 | 7 |
英文:
Truncate the date to the start of the month and group by that then order by the truncated date and not the formatted string.
SELECT TO_CHAR(TRUNC(D_DATE, 'MM'), 'MON_YYYY') Month_Year,COUNT (*) Request_CountFROM MY_TABLEWHERE V_TYPE='ABCD'GROUP BY TRUNC(D_DATE, 'MM')ORDER BY TRUNC(D_DATE, 'MM');
Which, for the sample data:
CREATE TABLE my_table (v_type, d_date) ASSELECT 'ABCD', DATE '2022-01-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 19 UNION ALLSELECT 'ABCD', DATE '2023-01-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 12 UNION ALLSELECT 'ABCD', DATE '2023-02-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 5 UNION ALLSELECT 'ABCD', DATE '2023-03-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 7;
Outputs:
| MONTH_YEAR | REQUEST_COUNT |
|---|---|
| JAN_2022 | 19 |
| JAN_2023 | 12 |
| FEB_2023 | 5 |
| MAR_2023 | 7 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论