sql - SQL Server 中几个表的一般报告

我需要创建下表模式的一般报告:



我需要为一般报告创建下表:

+----------+--------+------------------+-----------------------------+------------------------------+
| Location | Trucks | TotalOfCampaings | CampaingsWithCompleteStatus | CampaingsWithInProcessStatus |
+----------+--------+------------------+-----------------------------+------------------------------+
|          |        |                  |                             |                              |
+----------+--------+------------------+-----------------------------+------------------------------+
|          |        |                  |                             |                              |
+----------+--------+------------------+-----------------------------+------------------------------+

位置 :至少有一辆露营的卡车的位置。

卡车 :至少有一个露营地的卡车数量。

TotalOfCampaings :按位置和卡车划分的露营总数。

CampaingsWithCompleteStatus : 已完成的Campaings总数，状态在Campaing Table中。

CampaingsWithInProcessStatus :处于处理状态的Campaings总数，状态在Campaing Table中。

Campaing = 修理一辆或多辆卡车的命令。

我尝试使用内部连接，但无法得到我对一般报告的期望。

我将不胜感激帮助我，任何问题发表评论!

最佳答案

SELECT Truck.location as Location,
    COUNT(Truck.vin) as Trucks,
    COUNT(Campaing.campaing_id) as TotalOfCampaings,
    sum(case when Campaing.campaing_estatus = 'Complete' then 1 else 0 end) as CampaingsWithCompleteStatus,
    sum(case when Campaing.campaing_estatus = 'InProcess' then 1 else 0 end) as CampaingsWithInProcessStatus
    FROM CampaingControl
        INNER JOIN Truck ON CampaingControl.vin = Truck.vin
        INNER JOIN Campaing ON CampaingControl.campaing_id = Campaing.campaing_id
    GROUP BY Truck.location;

关于sql - SQL Server 中几个表的一般报告，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39157566/