`
wang4674890
  • 浏览: 87857 次
  • 性别: Icon_minigender_2
  • 来自: 厦门
社区版块
存档分类
最新评论

group by 多行变成一行-- (sum (case end case 也可以完成同样功能))

oracle 
阅读更多
select *
  from (select mtab.*, rownum row_no
          from (select distinct decode(mss.equip_id,
                                       null,
                                       cosmos_config_pkg.GET_SiteName(mss.site_id),
                                       cosmos_config_pkg.GET_Equip_SerialName(mss.mdl_ver_id,
                                                                              mss.equip_id)) name,
                                null servPkgID,
                                null srevPkgName,
                                wi.work_item_id itemId,
                                wi.work_item_name itemName,
                                csp.serv_provider_name servProv,
                                decode(mss.trigger_typ_id,
                                       5,
                                       to_char(mss.start_dt, 'mm/dd/yyyy'),
                                       6,
                                       to_char(mss.start_dt, 'mm/dd/yyyy'),
                                       to_char(mss.start_value)) stDt,
                                decode(mss.trigger_typ_id,
                                       5,
                                       nvl(to_char(mss.end_dt, 'mm/dd/yyyy'),
                                           'EOT'),
                                       6,
                                       nvl(to_char(mss.end_dt, 'mm/dd/yyyy'),
                                           'EOT'),
                                       nvl(to_char(mss.end_value), 'EOT')) edDt,
                                decode(mss.trigger_typ_id,
                                       5,
                                       (select ef.freq_desc
                                          from cost_event_frequency ef
                                         where ef.freq_id = mss.freq_id),
                                       6,
                                       (select et.event_typ_name
                                          from cost_event_typ et
                                         where et.event_typ_id =
                                               mss.event_typ_id),
                                       1,
                                       'FFS',
                                       2,
                                       'AH',
                                       3,
                                       'AS',
                                       4,
                                       'FFH',
                                       '') periodicity,
                                decode(mss.trigger_typ_id,
                                       5,
                                       'NA',
                                       6,
                                       nvl(to_char(mss.event_occurence), 'ALL'),
                                       1,
                                       to_char(mss.freq_value),
                                       2,
                                       to_char(mss.freq_value),
                                       3,
                                       to_char(mss.freq_value),
                                       4,
                                       to_char(mss.freq_value),
                                       '') sequence,
                                nvl(cosmos_config_pkg.GET_Equip_SerialName(mss.mdl_ver_id,
                                                                           mss.ref_equip_id),
                                    'NA') refESN,
                                (select stt.trigger_typ_name
                                   from COST_SERV_TRIGGER_TYP stt
                                  where stt.trigger_typ_id =
                                        mss.trigger_typ_id) frequency,
                                null servType,
                                decode(mv.mdl_ver_typ_id, 1129, 'Y', 'N') crRenegInd,
                                decode(mv.mdl_ver_typ_id, 1127, 'Y', 'N') crStartInd,
                                decode(mv.mdl_ver_typ_id, 1130, 'Y', 'N') crModelInd,
                                decode(mv.mdl_ver_typ_id, 1150, 'Y', 'N') crActualInd,
                                decode(mv.mdl_ver_typ_id, 1087, 'Y', 'N') crOpDataInd,
                                decode(mv.mdl_ver_typ_id, 1151, 'Y', 'N') crFutureInd,
                                decode(mv.mdl_ver_typ_id, 1060, 'Y', 'N') crWorkInd,
                                decode(mv.mdl_ver_typ_id, 1090, 'Y', 'N') reTain1Ind,
                                decode(mv.mdl_ver_typ_id, 1091, 'Y', 'N') reTain2Ind,
                                decode(mv.mdl_ver_typ_id, 1094, 'Y', 'N') reTain3Ind,
                                decode(mv.mdl_ver_typ_id, 1095, 'Y', 'N') reTain4Ind
                  from cost_model_ver              mv,
                       cost_event_service          es,
                       cost_mdl_standalone_service mss,
                       cost_work_item              wi,
                       cost_work_item_ctlg         wic,
                       cost_work_item_category     cwic,
                       cost_service_provider       csp
                 where mv.mdl_id = 12991468
                   and es.mdl_ver_id = mv.mdl_ver_id
                   and mss.mdl_ver_id = mv.mdl_ver_id
                   and mss.mdl_standalone_serv_id = es.mdl_standalone_serv_id
                   and wi.work_item_id = mss.work_item_id
                   and wic.work_item_id = wi.work_item_id
                   and wic.ctlg_ver_id =
                       cosmos_config_pkg.GET_CATALOG_VERSION(mv.mdl_ver_id)
                   and cwic.work_item_category_id = wi.work_item_category_id
                   and csp.serv_provider_id = mss.serv_provider_id
                   and es.actualize_ind = 'P'
                   and mv.mdl_ver_typ_id in
                       (1060, 1087, 1127, 1129, 1130, 1150, 1151)
                   and mss.equip_id is null) mtab)
where row_no < = 100and row_no > 0





select distinct decode(mss.equip_id,
                       null,
                       cosmos_config_pkg.GET_SiteName(mss.site_id),
                       cosmos_config_pkg.GET_Equip_SerialName(mss.mdl_ver_id,
                                                              mss.equip_id)) name,
                null servPkgID,
                null srevPkgName,
                wi.work_item_id itemId,
                wi.work_item_name itemName,
                csp.serv_provider_name servProv,
                decode(mss.trigger_typ_id,
                       5,
                       to_char(mss.start_dt, 'mm/dd/yyyy'),
                       6,
                       to_char(mss.start_dt, 'mm/dd/yyyy'),
                       to_char(mss.start_value)) stDt,
                decode(mss.trigger_typ_id,
                       5,
                       nvl(to_char(mss.end_dt, 'mm/dd/yyyy'), 'EOT'),
                       6,
                       nvl(to_char(mss.end_dt, 'mm/dd/yyyy'), 'EOT'),
                       nvl(to_char(mss.end_value), 'EOT')) edDt,
                decode(mss.trigger_typ_id,
                       5,
                       (select ef.freq_desc
                          from cost_event_frequency ef
                         where ef.freq_id = mss.freq_id),
                       6,
                       (select et.event_typ_name
                          from cost_event_typ et
                         where et.event_typ_id = mss.event_typ_id),
                       1,
                       'FFS',
                       2,
                       'AH',
                       3,
                       'AS',
                       4,
                       'FFH',
                       '') periodicity,
                decode(mss.trigger_typ_id,
                       5,
                       'NA',
                       6,
                       nvl(to_char(mss.event_occurence), 'ALL'),
                       1,
                       to_char(mss.freq_value),
                       2,
                       to_char(mss.freq_value),
                       3,
                       to_char(mss.freq_value),
                       4,
                       to_char(mss.freq_value),
                       '') sequence,
                nvl(cosmos_config_pkg.GET_Equip_SerialName(mss.mdl_ver_id,
                                                           mss.ref_equip_id),
                    'NA') refESN,
                (select stt.trigger_typ_name
                   from COST_SERV_TRIGGER_TYP stt
                  where stt.trigger_typ_id = mss.trigger_typ_id) frequency,
                null servType,
                max(decode(mv.mdl_ver_typ_id, 1129, 'Y', 'N')) crRenegInd,
                max(decode(mv.mdl_ver_typ_id, 1127, 'Y', 'N')) crStartInd,
                max(decode(mv.mdl_ver_typ_id, 1130, 'Y', 'N')) crModelInd,
                max(decode(mv.mdl_ver_typ_id, 1150, 'Y', 'N')) crActualInd,
                max(decode(mv.mdl_ver_typ_id, 1087, 'Y', 'N')) crOpDataInd,
                max(decode(mv.mdl_ver_typ_id, 1151, 'Y', 'N')) crFutureInd,
                max(decode(mv.mdl_ver_typ_id, 1060, 'Y', 'N')) crWorkInd,
                max(decode(mv.mdl_ver_typ_id, 1090, 'Y', 'N')) reTain1Ind,
                max(decode(mv.mdl_ver_typ_id, 1091, 'Y', 'N')) reTain2Ind,
                max(decode(mv.mdl_ver_typ_id, 1094, 'Y', 'N')) reTain3Ind,
                max(decode(mv.mdl_ver_typ_id, 1095, 'Y', 'N')) reTain4Ind

--,mdl_ver_typ_id
  from cost_model_ver              mv,
       cost_event_service          es,
       cost_mdl_standalone_service mss,
       cost_work_item              wi,
       cost_work_item_ctlg         wic,
       cost_work_item_category     cwic,
       cost_service_provider       csp
where mv.mdl_id = 12991468
   and es.mdl_ver_id = mv.mdl_ver_id
   and mss.mdl_ver_id = mv.mdl_ver_id
   and mss.mdl_standalone_serv_id = es.mdl_standalone_serv_id
   and wi.work_item_id = mss.work_item_id
   and wic.work_item_id = wi.work_item_id
   and wic.ctlg_ver_id =
       cosmos_config_pkg.GET_CATALOG_VERSION(mv.mdl_ver_id)
   and cwic.work_item_category_id = wi.work_item_category_id
   and csp.serv_provider_id = mss.serv_provider_id
   and es.actualize_ind = 'P'
   and mv.mdl_ver_typ_id in (1060, 1087, 1127, 1129, 1130, 1150, 1151)
   and mss.equip_id is null
group by decode(mss.equip_id,
                 null,
                 cosmos_config_pkg.GET_SiteName(mss.site_id),
                 cosmos_config_pkg.GET_Equip_SerialName(mss.mdl_ver_id,
                                                        mss.equip_id)),
          null,
          null,
          wi.work_item_id,
          wi.work_item_name,
          csp.serv_provider_name,
          decode(mss.trigger_typ_id,
                 5,
                 to_char(mss.start_dt, 'mm/dd/yyyy'),
                 6,
                 to_char(mss.start_dt, 'mm/dd/yyyy'),
                 to_char(mss.start_value)),
          decode(mss.trigger_typ_id,
                 5,
                 nvl(to_char(mss.end_dt, 'mm/dd/yyyy'), 'EOT'),
                 6,
                 nvl(to_char(mss.end_dt, 'mm/dd/yyyy'), 'EOT'),
                 nvl(to_char(mss.end_value), 'EOT')),
          mss.trigger_typ_id,
         
          decode(mss.trigger_typ_id,
                 5,
                 'NA',
                 6,
                 nvl(to_char(mss.event_occurence), 'ALL'),
                 1,
                 to_char(mss.freq_value),
                 2,
                 to_char(mss.freq_value),
                 3,
                 to_char(mss.freq_value),
                 4,
                 to_char(mss.freq_value),
                 ''),
          nvl(cosmos_config_pkg.GET_Equip_SerialName(mss.mdl_ver_id,
                                                     mss.ref_equip_id),
              'NA'),
         
          mss.trigger_typ_id,
          null,
          mss.freq_id,
          mss.event_typ_id
分享到：
| 利用Oracle分析函数实现多行数据合并为一行
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    case when和sum case when 写法及拼接字段

    1 case when 写法 2 sum case when 用法 3 select 拼接字段 示例如下： when 2 then 'C' else 'D' end ) as '类型',count(*) as '数量' from table group by orderType

    mysql行转列（将同一列下的不同内容的几行数据，转换成几列显示）、列转行、行列汇总、合并显示

    在上面的示例中，我们使用了 SUM 函数和 IF 语句来生成汇总行，并使用 GROUP BY 语句来分组。 MySQL 合并显示是将多个查询结果合并到一个查询结果的操作。例如，我们可以使用 UNION ALL 语句来将多个查询结果合并到...

    mysql case when group by 实例详解

    mysql 中类似php switch case 的语句。 select xx字段, case 字段 when 条件1 then 值1  when 条件2 then 值2 ...group by isCheck 使用case when ： select sum(redpackmoney) as stota, (CASE i

    SQL语句将行转换成列

    select @sql = @sql + ',sum(case 地区 when '''+地区+''' then 数量 end) ['+地区+']' + ',sum(case 地区 when '''+地区+''' then 比率 end) [比率]' from (select distinct 地区 from 表) as a select @sql...

    MYSQL中有关SUM字段按条件统计使用IF函数(case)问题

    今天群里有人问了个问题是这样的： 然后有群友是这样回答的 代码如下: select name,sum(case when stype=4 then money*(-1) else money end ) as M from table group by name 我想了想，应该可以用IF函数 于是改了下...

    2012 SQL常用操作

    ORDER BY 100 * SUM(CASE WHEN isnull(score,0)&gt;=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）...

    精髓Oralcle讲课笔记

    59、select avg(sal) from emp where sal &gt;1200 group by deptno having avg(sal) &gt;1500 order by avg(sal) desc;--求出sal&gt;1200的平均值按照deptno分组，平均值要&gt;1500最后按照sal的倒序排列 60、select ename,...

    数据库的行转列（Pivot操作）:将某一列的值转换为多个列名，并将其对应的数值填充到这些列中

    SUM(CASE WHEN product = 'ProductA' THEN revenue ELSE 0 END) AS ProductA: 使用 CASE 语句将 product 列中的 ProductA 的 revenue 值聚合到新列 ProductA 中，如果不是 ProductA 则返回 0。 对 ProductB 和 ...

    交叉表 存储过程 实例

    AS '多煤体', SUM(CASE e.bmdwdm WHEN e.bmdwdm THEN e.kkk ELSE 0 END) AS '合计' FROM (SELECT bmdwdm, khmkdm, COUNT(bmdwdm) AS kkk FROM tblkaosheng AS d GROUP BY bmdwdm, khmkdm) AS e INNER JOIN tblbmdw ...

    MYSSQL_MSS_ORACLE经典SQL.pdf

    ORDER BY 100 * SUM(CASE WHEN isnull(score,0)&gt;=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 20、--查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML; （003），数据库...

    Oracle中的SUM用法讲解

    Oracle中的SUM条件查询 1、按照区域编码分组查询区域编码、IPTV_NBR不为空的数量、ACC_NBR不为空的数量、所有用户数量 SELECT AREA_CODE, SUM ( CASE WHEN IPTV_NBR IS NULL or IPTV_...GROUP BY AREA_CODE; 2、使用

    C#语法及存储过程.docx

    set @sum = @sum + @@error //如果有一天sql语句报错@@error将会记录为1 if@sum&lt;&gt; 0 //如果不为0，说明有SQL语句执行错误 begin rollback //将事务回滚到执行之前 end else begin commit //SQL语句执行...

    查询数据库状态

    from sys.database_files where type=0 group by type' go -------------------log size-------------------------------------- if exists (select * from tempdb.sys.all_objects where name like '#...

    SQLQuery5.sql

    from KuChun t1 left join (select SellID, sum(sellnum)as snum,ProductType from Sell group by SellID,ProductType)t2 on t1.ShopID=t2.SellID and t1.ProductType=t2.ProductType order by t1.ShopID ...

    SQL经典面试题及答案SQL经典面试题及答案

    在给定的部分内容中，我们可以看到一个经典的SQL面试题，即如何使用GROUP BY语句来统计分类结果。在这个问题中，我们需要统计每天的胜利和失败次数，可以使用以下SQL语句来实现： ```sql SELECT rq, SUM(CASE WHEN ...

    50个常用SQL语句,很好

    ORDER BY 100 * SUM(CASE WHEN isnull(score,0)&gt;=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）...

    超实用sql语句

    select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type 显示结果： type ...

    SQL行列转换 Pivot UnPivot

    其中，聚合函数可以是 SUM、AVG、MAX、MIN 等，value_column 是要聚合的列，pivot_column 是要旋转的列，column_list 是要旋转的列名列表。 例如，在上面的实例中，我们可以使用 Pivot 语法将课程信息旋转为列名： ...

    常见sql语句

    SELECT contract_no, AVG(act_receive_amt) FROM tamdk31 GROUP BY contract_no HAVING AVG(act_receive_amt)&gt;100000; 7. 复合性查询： 复合性查询用于对数据进行复杂的筛选操作。例如： SELECT * FROM ttpct04 ...

Magicbox
Global site tag (gtag.js) - Google Analytics