clickhouse数据库,请找出这段sql中的错误并修正SELECT (case when org_type is null or org_type = '' then '未知' else org_type end) AS org_type, (case when type is null or type = '' then '未知' else type end) AS type, (case when fine_type_code is null or fine_type_code = '' then '未知' else fine_type_code end) AS fine_type_name, org_id, count(id) AS count, SUM(CASE WHEN status=7 THEN 1 ELSE 0 END) AS undistributed, SUM(CASE WHEN status=3 THEN 1 ELSE 0 END) AS inProcess, SUM(CASE WHEN status=4 THEN 1 ELSE 0 END) AS unchecked, SUM(CASE WHEN status=5 THEN 1 ELSE 0 END) AS completed FROM analysis.ods_biz_order_record_view GROUP BY (1, 2, 3, 4) ORDER BY count desc
时间: 2024-04-06 18:28:22 浏览: 101
这段 SQL 语句的语法是正确的,但在 GROUP BY 子句中使用了位置索引,这是不推荐的。建议使用列名而不是位置索引来指定分组的列。下面是修正后的 SQL:
SELECT
(CASE WHEN org_type IS NULL OR org_type = '' THEN '未知' ELSE org_type END) AS org_type,
(CASE WHEN type IS NULL OR type = '' THEN '未知' ELSE type END) AS type,
(CASE WHEN fine_type_code IS NULL OR fine_type_code = '' THEN '未知' ELSE fine_type_code END) AS fine_type_name,
org_id,
COUNT(id) AS count,
SUM(CASE WHEN status=7 THEN 1 ELSE 0 END) AS undistributed,
SUM(CASE WHEN status=3 THEN 1 ELSE 0 END) AS inProcess,
SUM(CASE WHEN status=4 THEN 1 ELSE 0 END) AS unchecked,
SUM(CASE WHEN status=5 THEN 1 ELSE 0 END) AS completed
FROM analysis.ods_biz_order_record_view
GROUP BY org_type, type, fine_type_name, org_id
ORDER BY count DESC
阅读全文