按月份名称对查询输出进行排序 [英] Sorting query output by Month name

问题描述

我正在尝试使用SQL查询从Excel中提取值，但是我一直在努力的是按照月份的顺序对月份进行排序。现在，该表正在按AZ排序，我尝试使用 DATEPART ，但是由于遇到了Int16错误而不能非常成功。

I am trying to extract values from Excel using an SQL Query, but what I've been struggling with is sorting the months in their respective order. Right now the table is being sorted A-Z, I tried playing around with DATEPART, but was not very successful as I was getting an Int16 error.

Select F1,
SUM(F2),
ROUND(SUM(REPLACE(F3, ',', '.')), 2),
ROUND(SUM(REPLACE(F4, ',', '.')), 2)
FROM [Sheet1$]
WHERE F1 IN ('January', 'February', 'March', 'April', 'May', 'June',
'July', 'August', 'September', 'October', 'November', 'December')
GROUP BY F1;

这是有效的查询，但结果是：

This is the query that works, but the result is:

由于文档很大，我也欢迎任何可以提高查询速度的建议，大约5万行，谢谢。

I am also open to any suggestions that could improve my query speed as the document is quite big, about 50k rows, thanks.

对于任何想知道最终查询有效的人是：

For anyone wondering the final query that worked was:

Select F1,
SUM(F2),
ROUND(SUM(REPLACE(F3, ',', '.')), 2),
ROUND(SUM(REPLACE(F4, ',', '.')), 2)
FROM [Sheet1$]
WHERE F1 IN ('January', 'February', 'March', 'April', 'May', 'June',
'July', 'August', 'September', 'October', 'November', 'December')
GROUP BY F1
ORDER BY SWITCH(
F1='January', 1,
F1='February', 2,
F1='March', 3,
F1='April', 4,
F1='May', 5,
F1='June', 6,
F1='July', 7,
F1='August', 8,
F1='September', 9,
F1='October', 10,
F1='November', 11,
F1='December', 12
);

推荐答案

要么使用 CASE 表达式：

Select
  f1,
  SUM(f2),
  ROUND(SUM(REPLACE(f3, ',', '.')), 2),
  ROUND(SUM(REPLACE(f4, ',', '.')), 2)
FROM [Sheet1$]
WHERE F1 IN ('January', 'February', 'March', 'April', 'May', 'June',
             'July', 'August', 'September', 'October', 'November', 'December')
GROUP BY F1
ORDER BY
  CASE F1
    WHEN 'January' THEN 1
    WHEN 'February' THEN 2
    WHEN 'March' THEN 3
    WHEN 'April' THEN 4
    WHEN 'May' THEN 5
    WHEN 'June' THEN 6
    WHEN 'July' THEN 7
    WHEN 'August' THEN 8
    WHEN 'September' THEN 9
    WHEN 'October' THEN 10
    WHEN 'November' THEN 111
    WHEN 'December' THEN 12
  END;

或动态创建月份表：

Select
  s.f1,
  SUM(s.f2),
  ROUND(SUM(REPLACE(s.f3, ',', '.')), 2),
  ROUND(SUM(REPLACE(s.f4, ',', '.')), 2)
FROM [Sheet1$] s
JOIN VALUES
(
  ('January', 1),
  ('February', 2),
  ('March', 3),
  ('April', 4),
  ('May', 5),
  ('June', 6),
  ('July', 7),
  ('August', 8),
  ('September', 9),
  ('October', 10),
  ('November', 11),
  ('December', 12)
) months(month_name, month_number) ON s.f1 = months.month_name
GROUP BY s.f1
ORDER BY months.month_number;

或创建真实的月份表。

这篇关于按月份名称对查询输出进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！