为什么在执行此查询时会出现此错误？

debugcn 发表于 Dev

The_Programmer92

我想显示当 drcr 2 然后金额是借方，当 drcr 1 然后金额是贷方，当 IsOpening 是 1 并且 drcr 是 2 则金额是 OpeningDebit 并且当 Isopening 是 1 并且 drcr 是 1 然后金额是 openCredit。

SELECT   
    GL.AccountId,Ca.Code as Code,
    CA.AccountName as AccountName,
    SUM(CASE GL.DrCr WHEN 2 THEN GL.Amount END) AS Debit,
    SUM(CASE GL.DrCr WHEN 1 THEN GL.Amount END) AS Credit,
    SUM(CASE GL.IsOpening WHEN 1 THEN Case GL.DrCr When 2 Then  GL.Amount END) AS OpeningDebit,
    SUM(CASE GL.IsOpening WHEN 1 THEN Case GL.DrCr When 1 Then  GL.Amount END) AS OpeningCredit
FROM 
    GeneralLedgerLine GL 
JOIN
    ClientAccount CA On GL.AccountId = CA.Id 
WHERE
    CA.ClientId = 1
GROUP BY 
    GL.AccountId, CA.Code, CA.AccountName

法米

您可以在下面尝试-您错过了ENDforouter CASE WHEN表达式

SELECT   GL.AccountId,Ca.Code as Code,CA.AccountName as AccountName,
         SUM(CASE GL.DrCr WHEN 2 THEN GL.Amount END) AS Debit,
         SUM(CASE GL.DrCr WHEN 1 THEN GL.Amount END) AS Credit,
         SUM(CASE GL.IsOpening WHEN 1 THEN Case GL.DrCr When 2 Then  GL.Amount END END) AS OprningDebit,
         SUM(CASE GL.IsOpening WHEN 1 THEN Case GL.DrCr When 1 Then  GL.Amount END END) AS OpeningCredit
FROM     GeneralLedgerLine GL Join ClientAccount CA On GL.AccountId = CA.Id Where CA.ClientId = 1
GROUP BY GL.AccountId,CA.Code,CA.AccountName

或者你可以简单地做如下 -

SELECT   GL.AccountId,Ca.Code as Code,CA.AccountName as AccountName,
         SUM(CASE GL.DrCr WHEN 2 THEN GL.Amount END) AS Debit,
         SUM(CASE GL.DrCr WHEN 1 THEN GL.Amount END) AS Credit,
         SUM(CASE GL.IsOpening WHEN 1 and GL.DrCr =2 Then  GL.Amount END) AS OprningDebit,
         SUM(CASE GL.IsOpening WHEN 1 and GL.DrCr=1 Then  GL.Amount END) AS OpeningCredit
FROM     GeneralLedgerLine GL Join ClientAccount CA On GL.AccountId = CA.Id Where CA.ClientId = 1
GROUP BY GL.AccountId,CA.Code,CA.AccountName

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。