SQL ORDER BY with CASE with UNION ALL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6427381/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL ORDER BY with CASE with UNION ALL
提问by Phill Pafford
Running PostgreSQL (7.4 and 8.x) and I thought this was working but now I'm getting errors.
运行 PostgreSQL（7.4 和 8.x），我认为这可行，但现在出现错误。
I can run the queries separately and it works just fine, but if I UNION or UNION ALL it throws an error.
我可以单独运行查询并且它工作得很好，但是如果我 UNION 或 UNION ALL 它会引发错误。
This errors out: (Warning: pg_query(): Query failed: ERROR: column "Field1" does not exist ... ORDER BY CASE "Field1" W...)
这个错误：（警告：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
This works:
这有效：
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
And this works as well:
这也有效：
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
and if I leave off the ORDER BY and just use the UNION or UNION ALL it works as well.
如果我不使用 ORDER BY 而只使用 UNION 或 UNION ALL 它也可以。
Any Ideas?
有任何想法吗？
回答by CristiC
Put everything in another SELECT:
将所有内容放在另一个 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
or, better, use the alias in ORDER BY, as it is passed at the end of the UNION:
或者，更好的是在 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
回答by Benoit
The first one does not work because you should do
第一个不起作用，因为你应该这样做
ORDER BY CASE field_1
The "Field1"is only available in a single subquery, and after you make the UNION with a common alias, you cannot refer to that column as "Field1"any more.
该"Field1"是只在一个子查询可用，您做出UNION具有共同别名之后，你不能指代列"Field1"了。
