请考虑以下查询:
SELECT DISTINCT ON (ser.id) *
FROM server ser
LEFT JOIN subscription sub ON ser.id = sub.server_id
WHERE (
COUNT(SELECT err.id FROM error err WHERE ser.id = err.id) > 0
OR SUM(SELECT pay.amount FROM payment pay WHERE ser.id = pay.id) > 0
);在这里,将返回正在订阅并有错误或付款的唯一服务器列表。
但是,我不返回所有的服务器列(*),而是返回服务器id、错误数和支付金额。例如,初始选择应该如下所示:
SELECT DISTINCT ON (ser.id) ser.id, countErrors, sumPayments选择ser.id是直接的,但是如何从聚合函数"count“和"sum”中选择countErrors和sumPayments (考虑到它们是WHERE子句中的条件)?
我想象着“哪里”的条件会是这样的:
COUNT(SELECT err.id FROM error err WHERE ser.id = err.id) AS countErrors > 0
OR SUM(SELECT pay.amount FROM payment pay WHERE ser.id = pay.id) AS sumPayments > 0有可能这样做吗?如果是的话,怎样才能做到呢?
测试数据如下所示:
server
+----+
| id |
+----+
| 1 |
+----+
| 2 |
+----+
| 3 |
+----+
| 4 |
+----+
subscription
+----+-----------+
| id | server_id |
+----+-----------+
| 1 | 1 |
+----+-----------+
| 2 | 2 |
+----+-----------+
| 3 | 2 |
+----+-----------+
| 4 | 3 |
+----+-----------+
| 5 | 3 |
+----+-----------+
error
+----+-----------+
| id | server_id |
+----+-----------+
| 1 | 1 |
+----+-----------+
| 3 | 4 |
+----+-----------+
payment
+----+-----------+--------+
| id | server_id | amount |
+----+-----------+--------+
| 1 | 1 | 200 |
+----+-----------+--------+
| 2 | 2 | 200 |
+----+-----------+--------+
| 3 | 2 | 100 |
+----+-----------+--------+从测试数据中获得的结果:
+-----------+-------------+-------------+
| server_id | countErrors | sumPayments |
+-----------+-------------+-------------+
| 1 | 1 | 200 |
+-----------+-------------+-------------+
| 2 | 0 | 300 |
+-----------+-------------+-------------+发布于 2018-02-15 10:39:18
除非我遗漏了什么,否则我只会按下面的方式编写您的查询。在单独的真正子查询中执行错误和支付的聚合,并将它们连接到一起。另外,subscription表有一个联接,但这只用于筛选没有订阅的服务器。最后,WHERE子句删除所有没有错误或付款的服务器。
SELECT
s.id AS server_id,
COALESCE(e.countErrors, 0) AS countErrors,
COALESCE(p.sumPayments, 0) AS sumPayments
FROM server s
INNER JOIN
(
SELECT DISTINCT server_id
FROM subscription
) su
ON s.id = su.server_id
LEFT JOIN
(
SELECT server_id, COUNT(*) AS countErrors
FROM error
GROUP BY server_id
) e
ON s.id = e.server_id
LEFT JOIN
(
SELECT server_id, SUM(amount) AS sumPayments
FROM payment
GROUP BY server_id
) p
ON s.id = p.server_id
WHERE
p.sumPayments > 0 OR
e.countErrors > 0
ORDER BY
s.id;

发布于 2018-02-15 10:05:39
这里的错误是将计数放在SELECT之外,它需要进入:
(SELECT COUNT(err.id) FROM error err WHERE ser.id = err.id) > 0
OR (SELECT SUM(pay.amount) FROM payment pay WHERE ser.id = pay.id) > 0https://stackoverflow.com/questions/48804383
复制相似问题