問題

我有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