width用法使查询语句更清晰

最新推荐文章于 2025-07-06 14:14:23 发布

原创最新推荐文章于 2025-07-06 14:14:23 发布 · 872 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

18 篇文章

订阅专栏

本文介绍了一个SQL查询案例，用于统计指定时间段内不同区域车辆的工作总时长，并进一步分析了每地区的车辆总数、有工作时长的车辆数及工作时长大于两小时的车辆数。

WITH Temp AS                                                        ---With 1 Temp
(SELECT OOD_PROVINCE as Region,
         OnOff.VI_ID,
         SUM(OOD_WORKHOURS) as TotalWorkHours
    FROM ON_OFF_DAY201403 OnOff
   WHERE OOD_Day >= date '2014-3-28'
     AND OOD_Day <= date '2014-4-4'
     AND OOD_PROVINCE IS NOT NULL
     AND VI_OWN_TYPE = 3
   GROUP BY OOD_PROVINCE, OnOff.VI_ID
UNION ALL
SELECT OOD_PROVINCE as Region,
         OnOff.VI_ID,
         SUM(OOD_WORKHOURS) as TotalWorkHours
    FROM ON_OFF_DAY201404 OnOff
   WHERE OOD_Day >= date '2014-3-28'
     AND OOD_Day <= date '2014-4-4'
     AND OOD_PROVINCE IS NOT NULL
     AND VI_OWN_TYPE = 3
   GROUP BY OOD_PROVINCE, OnOff.VI_ID),


VehicleStats AS                                                         -- With 2 VehicleStats
(SELECT Region, Vehicle.VI_ID, SUM(TotalWorkHours) as TotalWorkHours
    FROM Temp                                                          ---With 1 Temp
    JOIN Vehicle_Info Vehicle
      ON Temp.VI_ID = Vehicle.VI_ID
   WHERE 1 = 1
   GROUP BY Region, Vehicle.VI_ID)


SELECT Region,
       COUNT(*) AS VehicleCount,
       SUM(CASE
             WHEN TotalWorkHours > 0 THEN
              1
             ELSE
              0
           END) AS HasWorkHourCount,
       SUM(CASE
             WHEN TotalWorkHours > 2 THEN
              1
             ELSE
              0
           END) AS AboveWorkHourCount
FROM VehicleStats                                                 -- With 2 VehicleStats
GROUP BY Region
ORDER BY AboveWorkHourCount DESC