运行 PostgreSQL(7.4 和 8.x),我认为这是有效的,但现在我遇到了错误。
我可以单独运行查询,它工作得很好,但如果我 UNION 或 UNION ALL 它会抛出错误。
出现错误:(警告:pg_query():查询失败:错误:列“Field1”不存在...按大小写“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 它也可以工作。
有任何想法吗?