`
wang4674890
  • 浏览: 89001 次
  • 性别: 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 写法及拼接字段

    ### Case When 用法 在 SQL 查询语言中，`CASE WHEN...结合字符串拼接功能，这些技术可以帮助我们在复杂的数据处理任务中更加高效地完成工作。在实际应用中，根据具体的业务需求选择合适的 SQL 构造方法是非常重要的。

    oracle case语句的介绍

    通过以上案例，我们可以看到 CASE 语句在 Oracle 中的强大功能和灵活性。无论是数据转换还是复杂的统计需求，CASE 语句都能提供有效的解决方案。在实际工作中，合理运用 CASE 语句能够极大地提高 SQL 查询的能力和...

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

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

    详细解析 mysql 中的 case when 语句的使用

    - CASE语句可以根据一个表达式的值与一系列的比较值进行匹配，当找到匹配的值时，返回对应的值。 - 如果没有找到匹配的值，且存在ELSE子句，则返回ELSE子句中的值；若不存在ELSE子句，则返回NULL。 3. **应用场景...

    mysql case when group by 实例详解

    在`GROUP BY`语句中，`CASE WHEN`可以与聚合函数结合使用，对分组后的数据进行进一步处理。如示例所示，计算红包金额(`redpackmoney`)的总和，并根据`isCheck`字段的值进行分类： ```sql SELECT SUM(redpackmoney...

    sql经典面试题 大家一起学习

    我们可以使用`GROUP BY`语句来按照日期（rq）进行分组，然后通过`CASE`表达式计算胜和负的计数。这里有三种不同的解决方案： - 方法1： ```sql SELECT rq, SUM(CASE WHEN shengfu = '胜' THEN 1 ELSE 0 END) AS ...

    mssql CASE,GROUP BY用法

    接下来是 `GROUP BY` 语句，它的主要作用是将数据按照一个或多个列进行分组，以便可以对每个分组应用聚合函数，如 `SUM`、`AVG`、`COUNT` 等。在上述代码中，`GROUP BY` 被用来根据年份（`convert(char(4),Years,120...

    group by用法.doc

    可以通过`CASE`语句来处理，如`SELECT CASE WHEN (GROUPING(SupplierID) = 1) THEN '-1' ELSE SupplierID END AS SupplierID, SUM(UnitPrice) AS QtySum FROM Products GROUP BY SupplierID WITH CUBE`，这样可以为...

    在SQL语言中用Group By子句实现除法.pdf

    在数据库查询语言SQL中，Group By子句是一种常用的聚合查询工具，它可以将数据集分成不同的组，对每组数据执行聚合函数操作，比如计算每个组的平均值、总和、最大值、最小值等。当与Select语句结合使用时，Group By...

    sql中的case用法

    这两种格式可以实现相同的功能，但是它们的写法和功能方面有一些限制。 简单 Case 函数的写法相对比较简洁，例如： ``` CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END ``` 这种写法可以根据...

    SQL中Case语句用法讨论_BJ

    在SQL查询语言中，`Case`语句是一个非常强大的功能，用于在查询中执行条件判断，它可以根据不同的条件返回不同的结果，这使得SQL能够处理更加复杂的数据筛选和转换需求。`Case`语句有两种基本形式：简单`Case`表达式...

    SQL语句将行转换成列

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

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

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

    SQL经典面试题及答案 CASE

    最后一题涉及到成绩等级的分类，同样使用CASE语句来实现。 ```sql SELECT (CASE WHEN 语文 &gt;= 80 THEN '优秀' WHEN 语文 &gt;= 60 THEN '及格' ELSE '不及格' END) AS '语文', (CASE WHEN 数学 &gt;= 80 THEN '优秀'...

    T-SQL_经典行专列、列转行_分页及存储过程

    在数据库处理中，行转列是一种常见的数据转换需求，特别是当需要将多行记录中的某一列数据按照特定条件聚合到一行中的不同列时。 **实例背景**：基于SQL Server 2005环境下的一个示例，我们将通过具体的步骤展示...

    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函数 于是改了下...

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