sql - 在Order by子句中使用date_part函数始终返回的列必须出现在Group by子句中

我需要将特定日期列的小时值安排为我自己的值，所以我使用了下面的查询，

SELECT SUM("sa_bikedata"."_Duration") AS "Chart_Column_1",
date_part('hour',"sa_bikedata"."_Start_date") AS "Chart_Column_0"
FROM "sa"."bikedata" AS "sa_bikedata"
GROUP BY date_part('hour',"sa_bikedata"."_Start_date")
ORDER BY CASE CAST(date_part('hour',"sa_bikedata"."_Start_date") as VARCHAR)
WHEN '1' THEN'0' WHEN '2' THEN'1' WHEN '3' THEN'2'
WHEN '4' THEN'3' WHEN '5' THEN'4' WHEN '6' THEN'5'
WHEN '7' THEN'6' WHEN '8' THEN'7' WHEN '9' THEN'8'
WHEN '10' THEN'09' WHEN '0' THEN'10'
ELSE date_part('hour',"sa_bikedata"."_Start_date")  END  LIMIT 1000  OFFSET 0;

但它总是返回错误，
错误：“sa_bikedata.\u Start_date”列必须出现在GROUP BY子句中或用于聚合函数中
第4行：按案例日期“部分（'小时'，”sa_bikedata“，”u Start_date“）
我想在数据表中得到结果

1
2
3
4
5
6
7
8
9
10
0

最佳答案

错误是由于在ORDERBY附近使用了不正确的CASE语法。下面是按大小写排序的语法，

ORDER BY CASE WHEN(condition) THEN result

所以查询应该如下所示，

    SELECT SUM( CASE WHEN ("trxtype"<> 'POH') THEN ( "trxamt") ELSE ( 0) END ) AS
    "Chart_Column_3",CAST(date_part('hour',"trxdate") AS VARCHAR) AS "Chart_Column_7"
    FROM "POS.Sales".postrx POS GROUP BY  CAST(date_part('hour',"trxdate") AS VARCHAR)
    ORDER BY
    CASE WHEN(CAST(date_part('hour',"trxdate") AS VARCHAR) = '7') THEN'0'
    WHEN(CAST(date_part('hour',"trxdate") AS VARCHAR) = '8') THEN '1'
    ...
    END  LIMIT 1000  OFFSET 0;