简化sql中union all

最新推荐文章于 2024-04-26 18:29:39 发布

原创最新推荐文章于 2024-04-26 18:29:39 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

oracle问题专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一种使用SQL查询来统计不同类型的道路数量的方法。通过两次不同的SELECT操作，一次针对非特定类型的路段，另一次针对特定类型路段，并对其进行特殊计数处理。最终通过GROUP BY将结果按警区、所属单位、责任区及类型进行汇总。

SELECT   SUM (roadcount) AS roadcount, policezone, detachment, dutyzone, TYPE
    FROM (SELECT   COUNT (*) AS roadcount, policezone, detachment, dutyzone,
                   kind AS TYPE
              FROM sde.r_line
             WHERE roadtype != 3
          GROUP BY policezone, detachment, dutyzone, kind
          UNION ALL
          SELECT   COUNT (*) / 2 AS roadcount, policezone, detachment,
                   dutyzone, kind AS TYPE
              FROM sde.r_line
             WHERE roadtype = 3
          GROUP BY policezone, detachment, dutyzone, kind)
GROUP BY policezone, detachment, dutyzone, TYPE

简化sql
SELECT   SUM (roadcount) roadcount, policezone, detachment, dutyzone,
         kind TYPE
    FROM (SELECT DECODE (roadtype, 3, 0.5, 1) roadcount, policezone,
                 detachment, dutyzone, kind
            FROM sde.r_line)
-- WHERE roadtype != 3
GROUP BY policezone, detachment, dutyzone, kind