选择子查询(放在SELECT Clues中)的多个列

SELECT Multiple Columns FROM a subquery (placed in the SELECT Clues) Efficiently

来源: link 标签:sql-servercountsubquery 更新时间:

  问题

我有3个表:

  1. 表A
  2. 表B
  3. 表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 时,只能在选择列表中指定一个表达式。

如何从一个子查询获取多列?

执行上述查询的有效方法是什么?

  最佳答案

您可以将TableC上的查询移动到最外部选择,保持前两个子查询现在: 

 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,
    COUNT(*) AS ColumnName3,
    SUM(TPrice + COALESCE (PPrice, 0)) AS ColumnName4
FROM TableC
WHERE
    ClientId = 2 AND
    TDate >= CURRENT_TIMESTAMP - 30;

这有效,因为前两个子查询基本上被视为常量.单独在TableC上运行查询的其余部分允许我们选择多个集合.

  相同标签的其他问题

sql-servercountsubquery
上一个问题:
下一个问题: