假设我有以下 PostgreSQL 查询:
SELECT (
SELECT SUM (t.customers)
FILTER (
WHERE id = 1
AND date
BETWEEN %(start)s AND %(stop)s
) +
SELECT SUM (t.employees)
FILTER (
WHERE id = 1
AND date
BETWEEN %(start)s AND %(stop)s
)
FROM table t;
我想复制结果,以便返回的表具有三列。我应该将子查询复制三次,并用逗号分隔吗?这似乎是一种混乱的方式,并且不是最佳的,因为查询将进行三次(?)。我宁愿eaters
为查询提供一个别名,并编写一个返回别名三次的新查询:
-- The SELECT above AS eaters and then:
SELECT (
SELECT * FROM eaters, SELECT * FROM eaters, SELECT * FROM eaters
);
我想这在性能方面也应该没问题。n
是否有其他一些简洁有效的方法来复制主查询中的结果时间?
我查询的学校餐厅数据分为三部分:总结果、不含饮料的结果和仅饮料的结果。就餐人数(= 顾客 + 员工)将显示在每个结果表中,并且所有三列始终相同。显然,无论考虑哪一部分,客户数量都是相同的。所以我需要相同的值三次。我当然可以用文本替换以下值,“与总数相同”等,这样就可以理解了。但我更愿意了解这一点并展示数字。
编辑:使用 CTE 我会提出这种查询:
WITH customers AS (
SELECT SUM (customers)
FROM table
WHERE id = 1
AND date
BETWEEN %(start)s AND %(stop)s
), employees AS (
SELECT SUM (employees)
FROM table
WHERE id = 1
AND date
BETWEEN %(start)s AND %(stop)s
);
SELECT (
(SELECT * FROM customers) + (SELECT * FROM employees)
) AS eaters;
SELECT (
SELECT * FROM eaters, SELECT * FROM eaters, SELECT * FROM eaters
);
我可能不明白这个问题,但是:
如果过滤器始终相同,最好将它们移至 where 子句:
小提琴