主页 / 问题 / 1043065
Accepted
Insider
Asked:2020-11-06 22:31:57 +0000 UTC2020-11-06 22:31:57 +0000 UTC

如果 N 等于 m，SQL 如何正确计算总和

  • 772

我正在尝试按以下顺序从数据库中计算金额：

如果[KW] = '99'，那么'KW 99: ' + cast(SUM(N_KH) as varchar)

如果[KW]<（小于）'97'则'Summe Laufend: ' + cast(SUM(N_KH) as varchar)

如果[KW]<= （介于）'1'然后'99''Summe Total: ' + cast(SUM(N_KH) as varchar)

试图这样计算

Select case 
         when KW = '99' 
            then 'KW 99: ' + cast(SUM(N_KH) as varchar) 
         when KW < '97'
            then 'Summe Laufend: ' + cast(SUM(N_KH) as varchar) 
        when KW >= '99'
            then 'Summe Total: ' + cast(SUM(N_KH) as varchar) 
        end as Woche
FROM [ATX_PLSNG].[dbo].[Terminliste]
Group by KW

结果，我得到了一张桌子Summe Laufend, NULL, KW，等等。 在此处输入图像描述

如何获得如下截图中的金额，即三个条目，仅此而已 在此处输入图像描述

sql

1 个回答

  1. Best Answer

    按列：

    SELECT SUM(CASE WHEN KW = 99 THEN N_KH ELSE 0 END) AS [KW 99]
      ,SUM(CASE WHEN KW < 97 THEN N_KH ELSE 0 END) AS [Summe Laufend]
      ,SUM(CASE WHEN KW BETWEEN 1 AND 99 THEN N_KH ELSE 0 END) AS [Summe Total]
  FROM [ATX_PLSNG].[dbo].[Terminliste]

    按行：

    SELECT [unpvt].[KW_Type] + ': ' + CAST([S] AS varchar(20))
  FROM (
           SELECT SUM(CASE WHEN KW = 99 THEN N_KH ELSE 0 END) AS [KW 99]
                 ,SUM(CASE WHEN KW < 97 THEN N_KH ELSE 0 END) AS [Summe Laufend]
                 ,SUM(CASE WHEN KW BETWEEN 1 AND 99 THEN N_KH ELSE 0 END) AS [Summe Total]
             FROM [ATX_PLSNG].[dbo].[Terminliste]
       ) AS [p]
       UNPIVOT
       (
           [S] FOR [KW_Type] IN ([KW 99], [Summe Laufend], [Summe Total])
       ) AS [unpvt];

    您可以在文档中阅读有关 UNPIVOT 的更多信息：https ://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

    • 2

相关问题