我有3个表:
- 表A
- 表B
- 表C
我需要在其中的一些列上执行几个聚合函数(COUNT和SUM).
我没有找到一种方法将它们连接在一起(即使是下面的ClientId列)所以我尝试了:
SELECT
(SELECT COUNT(*) FROM TableA
WHERE ClientId = 2 AND IsDisabled != 1 AND IsDeleted != 1) AS ColumnName1,
(SELECT COUNT(*) FROM TableB
WHERE ClientId = 2 AND DisabledWhen IS NULL) AS ColumnName2,
(SELECT COUNT(*) FROM TableC
WHERE ClientId = 2 AND TDate >= CURRENT_TIMESTAMP -30) AS ColumnName3,
(SELECT SUM(TPrice + COALESCE (PPrice,0)) FROM TableC
WHERE ClientId = 2 AND TDate >= CURRENT_TIMESTAMP -30) AS ColumnName4;
它正在工作,结果是:
+--------------+-------------+-------------+--------------+
| ColumnName1 | ColumnName2 | ColumnName3 | ColumnName4 |
+--------------+-------------+-------------+--------------+
| 202 | 86 | 25 | 4574.0000 |
+--------------+-------------+-------------+--------------+
但我想将最后两个子查询组合为一个子查询.
所以我试图这样做:
(SELECT COUNT(*) AS ColumnName3, SUM(TPrice + COALESCE (PPrice,0)) AS ColumnName4 FROM TableC
WHERE ClientId = 2 AND TDate >= CURRENT_TIMESTAMP -30);
但我收到此错误:
当子查询不引入 EXISTS 时,只能在选择列表中指定一个表达式。
如何从一个子查询获取多列?
执行上述查询的有效方法是什么?