oracle根据状态统计近7天的数据

最新推荐文章于 2024-07-14 08:45:00 发布

瞧风风

最新推荐文章于 2024-07-14 08:45:00 发布

阅读量2.1k

点赞数 2

分类专栏： oracle 文章标签： oracle 分类统计

本文链接：https://blog.youkuaiyun.com/weixin_41711593/article/details/83833652

版权

oracle 专栏收录该内容

4 篇文章

订阅专栏

---查询7天的数据

select
a.today as "day",
   nvl(b.general,0) as "general",
   nvl(b.larger,0) as "larger",
   nvl(b.great,0) as "great",
   nvl(b.most,0) as "most"
FROM
(SELECT
to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') today
FROM
DUAL connect BY LEVEL <= 7) a
LEFT JOIN
   (   select to_char(t.CREATE_TIME_,'YYYY-MM-dd') as day,
   SUM(CASE t.LEVEL_ WHEN '1' THEN 1 ELSE 0 END) AS general,
   SUM(CASE t.LEVEL_ WHEN '2' THEN 1 ELSE 0 END) AS larger,
   SUM(CASE t.LEVEL_ WHEN '3' THEN 1 ELSE 0 END) AS great,
   SUM(CASE t.LEVEL_ WHEN '4' THEN 1 ELSE 0 END) AS most
    from QUALITY_PROBLEM t
   WHERE t.project_id_ = '119993794937487360'
GROUP BY to_char(t.CREATE_TIME_,'YYYY-MM-dd')) b on a.today=b.day order by a.today