SQL子查询案例

Question

SELECT 
  ac.ac_code,    
  (SELECT CASE
              WHEN SUM(L.l_valuedr) - SUM(L.l_valuecr) >= 0 THEN SUM(L.l_valuedr) - SUM(L.l_valuecr)
              ELSE 0
          END AS dr,
          CASE
              WHEN SUM(L.l_valuedr) - SUM(L.l_valuecr) < 0 THEN SUM(L.l_valuecr) - SUM(L.l_valuedr)
              ELSE 0
          END AS cr
   FROM Ledger AS L
   WHERE (l_date < '09/08/2014')
     AND (l_accode = ac.ac_code)
  ) AS bf,
  CASE
    WHEN SUM(Ledger_2.l_valuedr) - SUM(Ledger_2.l_valuecr) >= 0 THEN SUM(Ledger_2.l_valuedr) - SUM(Ledger_2.l_valuecr)
    ELSE 0
  END AS dr,
  CASE
    WHEN SUM(Ledger_2.l_valuedr) - SUM(Ledger_2.l_valuecr) < 0 THEN SUM(Ledger_2.l_valuecr) - SUM(Ledger_2.l_valuedr)
    ELSE 0
  END AS cr
FROM Ac_Accounts AS ac
INNER JOIN Ac_Costelements ON ac.ac_costelement = Ac_Costelements.ce_code
INNER JOIN Ledger AS Ledger_2 ON ac.ac_code = Ledger_2.l_accode
WHERE (Ledger_2.l_date >= '09/08/2014')
  AND (Ledger_2.l_date <= '09/15/2014')
  AND (Ledger_2.l_accode = ac.ac_code)
GROUP BY ac.ac_code

显示此错误：

Msg 116，16级，状态1，第5行 当子查询未引入EXISTS时，只能在select列表中指定一个表达式。

Answer 1

我认为您可以将查询简化如下：

WITH CTE AS
(   SELECT  ac.ac_code,
            Value1 = SUM(CASE WHEN l.l_date < '20140908' THEN l.l_valuedr - l.l_valuecr ELSE 0 END),
            Value2 = SUM(CASE WHEN l.l_date >= '20140908' THEN l.l_valuedr - l.l_valuecr ELSE 0 END)
    FROM    Ac_Accounts AS ac
            INNER JOIN Ac_Costelements AS c
                ON ac.ac_costelement = c.ce_code
            INNER JOIN Ledger AS l 
                ON ac.ac_code = l.l_accode
    WHERE   l.l_date <= '20140915'
    GROUP BY ac.ac_code
)
SELECT  ac_code,
        dr = CASE WHEN Value1 >= 0 THEN Value1 END,
        cr = CASE WHEN Value1 < 0 THEN Value1 * -1 END,
        dr = CASE WHEN Value1 >= 0 THEN Value2 END,
        cr = CASE WHEN Value1 < 0 THEN Value2 * -1 END
FROM    CTE;

与使用关联子查询来获取给定日期之前的金额以及在主查询中获取该日期之后的金额不同，您可以通过在SUM中使用CASE获取同一查询中的两个金额。

然后，通过将一些逻辑移到子查询中，您可以通过减少所完成的和数来简化查询。

Answer 2

与其做两个案例陈述，为什么不把它们放在一起呢？从这一点出发：

(SELECT CASE
              WHEN SUM(L.l_valuedr) - SUM(L.l_valuecr) >= 0 THEN SUM(L.l_valuedr) - SUM(L.l_valuecr)
              ELSE 0
          END AS dr,
          CASE
              WHEN SUM(L.l_valuedr) - SUM(L.l_valuecr) < 0 THEN SUM(L.l_valuecr) - SUM(L.l_valuedr)
              ELSE 0
          END AS cr
   FROM Ledger AS L
   WHERE (l_date < '09/08/2014')
     AND (l_accode = ac.ac_code)) AS bf

像这样的事情：

(SELECT CASE
         WHEN SUM(L.l_valuedr) - SUM(L.l_valuecr) >= 0 
         THEN SUM(L.l_valuedr) - SUM(L.l_valuecr)
         ELSE SUM(L.l_valuecr) - SUM(L.l_valuedr)
        END AS newcolumn
   FROM Ledger AS L
   WHERE (l_date < '09/08/2014')
     AND (l_accode = ac.ac_code)) AS bf