sql - 使用多个条件多次选择“单个字段”

我需要在表PLE下方的每个 Activity 中生成一个Project明智的表面积

Activity    Posting Date    Surface Area  Project
---------------------------------------------------------------------
Shearing     01-04-2013     2.34          A
Bending      01-04-2013     2.34          A
Assembly     02-04-2013     2.34          B
PC           02-04-2013     5.34          B
Infill       05-04-2013     5.34          C

我正在尝试这样做。

SELECT DISTINCT Project,sum(Project.[Surface Area]) AS TotalShearing
FROM PLE
WHERE ([Posting Date] BETWEEN @StartDate AND @EndDate)
  AND (Activity = Shearing)
GROUP BY Project

现在，我想在TotalBending, TotalAssembly的右侧显示TotalShearing等。但是不知道如何获取它们，因为WHERE条件已经用于 Activity “剪切”。这可能是简单的任务，但是我是SQL的新手，因此需要帮助!

最佳答案

尝试这样的事情:

SELECT Project
    ,sum(CASE WHEN Activity = 'Shearing' THEN [Surface Area] ELSE 0 END) AS TotalShearing
    ,sum(CASE WHEN Activity = 'Bending' THEN [Surface Area] ELSE 0 END) AS TotalBending
    ,sum(CASE WHEN Activity = 'Assembly' THEN [Surface Area] ELSE 0 END) AS TotalAssembly
    ,sum(CASE WHEN Activity = 'PC' THEN [Surface Area] ELSE 0 END) AS TotalPC
    ,sum(CASE WHEN Activity = 'Infill' THEN [Surface Area] ELSE 0 END) AS TotalInfill
WHERE ([Posting Date] BETWEEN @StartDate AND @EndDate)
FROM PLE
GROUP BY Project

SQLFiddle DEMO

关于sql - 使用多个条件多次选择“单个字段”，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17922407/