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

Question

我需要创建下表架构的一般报告:

我需要为总报告创建下表:

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

• 位置:至少有一个露营地的卡车的位置。
• 卡车:至少有一个露营地的卡车数量(按位置)。
• TotalOfCampaings:按地点和卡车分类的露营总数。
• CampaingsWithCompleteStatus:已完成的事件总数，状态在事件表中。
• CampaingsWithInProcessStatus:处理中状态的事件总数，状态在事件表中。

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

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

我很感激帮助我解决这个问题，任何问题都可以在评论中发表!

Answer 1

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;