运行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,"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,"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,"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,它也可以工作.
有任何想法吗？
解决方法
把所有东西放在另一个SELECT中
SELECT * FROM (
SELECT "Field1" AS field_1,"Field4" AS field_4
FROM "TableName"
WHERE condition
AND other_condition
UNION ALL
SELECT "Field1" AS field_1,"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,"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
