问题带有UNION ALL的CASE的SQL ORDER BY

运行PostgreSQL（7.4和8.x），我认为这是有效的，但现在我遇到了错误。

我可以单独运行查询，它工作正常，但如果我UNION或UNION ALL它会抛出一个错误。

这个错误输出:(警告：pg_query（）：查询失败：错误：列“Field1”不存在... ORDER BY CASE“Field1”W ...）

SELECT "Field1" AS field_1, "Field2" AS field_2,
"Field3" AS field_3, "Field4" AS field_4
FROM "TableName" 
WHERE condition
AND other_condition
UNION ALL
SELECT "Field1" AS field_1, "Field2" AS field_2,
"Field3" AS field_3, "Field4" AS field_4
FROM "TableName" 
WHERE yet_another_condition
AND yet_another_other_condition
ORDER BY CASE "Field1"
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END

这有效：

SELECT "Field1" AS field_1, "Field2" AS field_2,
"Field3" AS field_3, "Field4" AS field_4
FROM "TableName" 
WHERE yet_another_condition
AND yet_another_other_condition
ORDER BY CASE "Field1"
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END

这也有效：

SELECT "Field1" AS field_1, "Field2" AS field_2,
"Field3" AS field_3, "Field4" AS field_4
FROM "TableName" 
WHERE condition
AND other_condition
ORDER BY CASE "Field1"
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END

如果我放弃ORDER BY并且只使用UNION或UNION ALL它也可以。

有任何想法吗？

5280

2018-06-21 14:56

起源

答案:

将所有内容放入另一个SELECT：

SELECT * FROM (
  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE condition
  AND other_condition
  UNION ALL
  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE yet_another_condition
  AND yet_another_other_condition
) As A
ORDER BY CASE field_1
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END

或者，更好的是，在ORDER BY中使用别名，因为它在UNION的末尾传递：

  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE condition
  AND other_condition
  UNION ALL
  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE yet_another_condition
  AND yet_another_other_condition
  ORDER BY CASE field_1
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
  END

2018-06-21 14:58

不会工作，因为 "Field1" 不是列名。 - Benoit

抱歉没用，同样的错误 - Phill Pafford

是的，注意到了。答案修改.... - CristiC

@Phill：现在检查。 - CristiC

是的确有效，谢谢！ - Phill Pafford

答案:

将所有内容放入另一个SELECT：

SELECT * FROM (
  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE condition
  AND other_condition
  UNION ALL
  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE yet_another_condition
  AND yet_another_other_condition
) As A
ORDER BY CASE field_1
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END

或者，更好的是，在ORDER BY中使用别名，因为它在UNION的末尾传递：

  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE condition
  AND other_condition
  UNION ALL
  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE yet_another_condition
  AND yet_another_other_condition
  ORDER BY CASE field_1
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
  END

2018-06-21 14:58

不会工作，因为 "Field1" 不是列名。 - Benoit

抱歉没用，同样的错误 - Phill Pafford

是的，注意到了。答案修改.... - CristiC

@Phill：现在检查。 - CristiC

是的确有效，谢谢！ - Phill Pafford

第一个不起作用，因为你应该这样做

ORDER BY CASE field_1

该 "Field1" 仅在单个子查询中可用，并且在使用公共别名创建UNION之后，您不能将该列引用为 "Field1" 再也没有。

2018-06-21 15:00

+1用于帮助解决问题。我这样做了但是我得到了这个错误：错误：UNION / INTERSECT / EXCEPT结果上的ORDER BY必须在其中一个结果列上 - Phill Pafford

问题 带有UNION ALL的CASE的SQL ORDER BY

答案:

答案:

热门问题

问题带有UNION ALL的CASE的SQL ORDER BY