带有UNION ALL的CASE的SQL ORDER BY [英] SQL ORDER BY with CASE with UNION ALL

问题描述

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

Running PostgreSQL (7.4 and 8.x) and I thought this was working but now I'm getting errors.

我可以单独运行查询，并且可以运行很好，但是如果我使用UNION或UNION ALL，则会引发错误。

I can run the queries separately and it works just fine, but if I UNION or UNION ALL it throws an error.

此错误输出：（警告：pg_query（）：查询失败：错误：列 Field1不存在...按案例排序 Field1 W ...）

This errors out: (Warning: pg_query(): Query failed: ERROR: column "Field1" does not exist ... 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，那么它也可以正常工作。

and if I leave off the ORDER BY and just use the UNION or UNION ALL it works as well.

有什么想法吗？

推荐答案

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

Put everything in another 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末尾传递的：

or, better, use the alias in ORDER BY, as it is passed at the end of the 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

这篇关于带有UNION ALL的CASE的SQL ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！