有多个标准

Question

我需要从下表PLE

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条件已被用于活动'剪切'。这可能很简单，但我是SQL新手，因此需要帮助！

Answer 1

尝试这样：

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

Answer 2

使用PIVOT表操作：

SELECT * 
FROM 
(
    SELECT Activity, [Surface Area], project 
    FROM PLE 
) AS t 
PIVOT 
(
    sum([Surface Area]) 
    FOR Activity IN ([Shearing], 
        [Bending], 
        [Assembly], 
        [PC], 
        [Infill]) 
) AS p; 

• SQL Fiddle Demo

---

如果要动态地做到这一点为Activity每个号码必须使用动态SQL这样的：

DECLARE @cols AS NVARCHAR(MAX); 
DECLARE @query AS NVARCHAR(MAX); 


select @cols = STUFF((SELECT distinct ',' + 
         QUOTENAME(Activity) 
         FROM PLE 
         FOR XML PATH(''), TYPE 
        ).value('.', 'NVARCHAR(MAX)') 
         , 1, 1, ''); 

SELECT @query = 'SELECT * 
FROM 
(
    SELECT Activity, [Surface Area], project 
    FROM PLE 
) AS t 
PIVOT 
(
    sum([Surface Area]) 
    FOR Activity IN (' + @cols + ') 
) AS p'; 

execute(@query); 

看到这个：

• Updated SQL Fiddle Demo

Answer 3

SELECT DISTINCT Project, 
sum(if(Activity = 'Shearing', Project.[Surface Area],0) AS Shearing , 
sum(if(Activity = 'Bending', Project.[Surface Area],0) AS TotalBending, 
sum(if(Activity = 'Assembly', Project.[Surface Area],0) AS TotalAssembly 
FROM PLE 
WHERE ([Posting Date] BETWEEN @StartDate AND @EndDate) 

GROUP BY Project 

Answer 4

你想要的东西是“分区依据” SQL中的关键字。 更精确的总和（....）结束（按活动划分）。 这应该做的伎俩。