在一个select语句中有条件地对同一列进行多次求和?

I have a single table that shows employee deployments, for various types of deployment, in a given location for each month:

我有一个表，显示了每个月在给定位置的员工部署，用于不同类型的部署:

ID | Location_ID | Date | NumEmployees | DeploymentType_ID

As an example, a few records might be:

例如，一些记录可能是:

 1 | L1 | 12/2010 | 7 | 1 (=Permanent)
 2 | L1 | 12/2010 | 2 | 2 (=Temp)
 3 | L1 | 12/2010 | 1 | 3 (=Support)
 4 | L1 | 01/2011 | 4 | 1
 5 | L1 | 01/2011 | 2 | 2
 6 | L1 | 01/2011 | 1 | 3
 7 | L2 | 12/2010 | 6 | 1
 8 | L2 | 01/2011 | 6 | 1
 9 | L2 | 12/2010 | 3 | 2

What I need to do is sum the various types of people by date, such that the results look something like this:

我需要做的是按日期对不同类型的人进行求和，这样结果看起来就像这样:

Date    | Total Perm | Total Temp | Total Supp
12/2010 |     13     |     5      |      1
01/2011 |     10     |     2      |      1

Currently, I've created a separate query for each deployment type that looks like this:

目前，我为每个部署类型创建了一个单独的查询，如下所示:

SELECT Date, SUM(NumEmployees) AS "Total Permanent"
FROM tblDeployment
WHERE DeploymentType_ID=1
GROUP BY Date;

We'll call that query qSumPermDeployments. Then, I'm using a couple of joins to combine the queries:

我们将调用查询qsumpermdeploy。然后，我使用一些连接来合并查询:

SELECT qSumPermDeployments.Date, qSumPermDeployments.["Total Permanent"] AS "Permanent"
    qSumTempDeployments.["Total Temp"] AS "Temp"
    qSumSupportDeployments.["Total Support"] AS Support
FROM (qSumPermDeployments LEFT JOIN qSumTempDeployments 
    ON qSumPermDeployments.Date = qSumTempDeployments.Date) 
LEFT JOIN qSumSupportDeployments 
    ON qSumPermDeployments.Date = qSumSupportDeployments.Date;

Note that I'm currently constructing that final query under the assumption that a location will only have temp or support employees if they also have permanent employees. Thus, I can create the joins using the permanent employee results as the base table. Given all of the data I currently have, that assumption holds up, but ideally I'd like to move away from that assumption.

请注意，我目前正在构建这个最终查询，假设一个位置只有在拥有永久雇员的情况下才会有临时雇员或支持雇员。因此，我可以使用永久employee结果作为基表来创建连接。考虑到我目前的所有数据，这个假设成立，但理想情况下，我愿意放弃这个假设。

So finally, my question. Is there a way to simplify this down to a single query or is it best to separate it out into multiple queries - if for no other reason that readability.

最后,我的问题。是否有一种方法可以将其简化为单个查询，还是最好将其分离为多个查询——如果没有其他原因的话，就是可读性。

2 个解决方案

#1

SELECT Date, 
    SUM(case when DeploymentType_ID = 1 then NumEmployees else null end) AS "Total Permanent", 
    SUM(case when DeploymentType_ID = 2 then NumEmployees else null end) AS "Total Temp", 
    SUM(case when DeploymentType_ID = 3 then NumEmployees else null end) AS "Total Supp"
FROM tblDeployment
GROUP BY Date

#2

Try this:

试试这个:

SELECT
    Date,
    SUM(CASE WHEN DeploymentType_ID=1 THEN NumEmployees ELSE 0 END) AS "Total Permanent",
    SUM(CASE WHEN DeploymentType_ID=2 THEN NumEmployees ELSE 0 END) AS "Total Temporary",
    SUM(CASE WHEN DeploymentType_ID=3 THEN NumEmployees ELSE 0 END) AS "Total Support"
FROM tblDeployment
GROUP BY Date;