`

报表统计sql语句（decode用法及在sql中巧妙组建map的key）

SQL 
阅读更多
/* Formatted on 2011/04/19 20:40 (Formatter Plus v4.8.6) */
SELECT   bill_type billtype, query_type querytype,
         materiel_type_id materieltypeid, z0000 z0000, z0731 z0731,
         z0733 z0733, z0732 z0732, z0734 z0734, z0737 z0737, z0730 z0730,
         z0736 z0736, z0744 z0744, z0746 z0746, z0745 z0745, z0738 z0738,
         z0739 z0739, z0735 z0735, z0743 z0743
    FROM (SELECT   '0' query_type, v.bill_type, v.materiel_type_id,
                   SUM (DECODE (DECODE (v.area_id, '0000', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0000,
                   SUM (DECODE (DECODE (v.area_id, '0731', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0731,
                   SUM (DECODE (DECODE (v.area_id, '0733', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0733,
                   SUM (DECODE (DECODE (v.area_id, '0732', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0732,
                   SUM (DECODE (DECODE (v.area_id, '0734', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0734,
                   SUM (DECODE (DECODE (v.area_id, '0737', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0737,
                   SUM (DECODE (DECODE (v.area_id, '0730', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0730,
                   SUM (DECODE (DECODE (v.area_id, '0736', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0736,
                   SUM (DECODE (DECODE (v.area_id, '0744', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0744,
                   SUM (DECODE (DECODE (v.area_id, '0746', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0746,
                   SUM (DECODE (DECODE (v.area_id, '0745', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0745,
                   SUM (DECODE (DECODE (v.area_id, '0738', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0738,
                   SUM (DECODE (DECODE (v.area_id, '0739', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0739,
                   SUM (DECODE (DECODE (v.area_id, '0735', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0735,
                   SUM (DECODE (DECODE (v.area_id, '0743', v.materiel_num),
                                '', 0,
                                v.materiel_num
                               )
                       ) z0743
              FROM v_inout_month_report v
             WHERE 1 = 1
               AND (   v.districtid IN (
                              SELECT man_obj_id
                                FROM td_sge_keeping_ref
                               WHERE keeping_id = 'csck1'
                                     AND manager_level = 1)
                    OR v.storehouse_id IN (
                              SELECT man_obj_id
                                FROM td_sge_keeping_ref
                               WHERE keeping_id = 'csck1'
                                     AND manager_level = 0)
                   )
          GROUP BY v.materiel_type_id, v.bill_type
          UNION
          SELECT   '1' query_type, bill_type, materiel_type_id,
                   SUM (DECODE (a.area_id, '0000', 1, 0)) z0000,
                   SUM (DECODE (a.area_id, '0731', 1, 0)) z0731,
                   SUM (DECODE (a.area_id, '0733', 1, 0)) z0733,
                   SUM (DECODE (a.area_id, '0732', 1, 0)) z0732,
                   SUM (DECODE (a.area_id, '0734', 1, 0)) z0734,
                   SUM (DECODE (a.area_id, '0737', 1, 0)) z0737,
                   SUM (DECODE (a.area_id, '0730', 1, 0)) z0730,
                   SUM (DECODE (a.area_id, '0736', 1, 0)) z0736,
                   SUM (DECODE (a.area_id, '0744', 1, 0)) z0744,
                   SUM (DECODE (a.area_id, '0746', 1, 0)) z0746,
                   SUM (DECODE (a.area_id, '0745', 1, 0)) z0745,
                   SUM (DECODE (a.area_id, '0738', 1, 0)) z0738,
                   SUM (DECODE (a.area_id, '0739', 1, 0)) z0739,
                   SUM (DECODE (a.area_id, '0735', 1, 0)) z0735,
                   SUM (DECODE (a.area_id, '0743', 1, 0)) z0743
              FROM (SELECT DISTINCT v.area_id, v.bill_type, v.bill_id,
                                    v.materiel_type_id
                               FROM v_inout_month_report v
                              WHERE 1 = 1
                                AND (   v.districtid IN (
                                           SELECT man_obj_id
                                             FROM td_sge_keeping_ref
                                            WHERE keeping_id = 'csck1'
                                              AND manager_level = 1)
                                     OR v.storehouse_id IN (
                                           SELECT man_obj_id
                                             FROM td_sge_keeping_ref
                                            WHERE keeping_id = 'csck1'
                                              AND manager_level = 0)
                                    )) a
          GROUP BY a.bill_type, materiel_type_id
          UNION
          SELECT   '2' query_type, bill_type, materiel_type_id,
                   SUM (DECODE (a.area_id, '0000', 1, 0)) z0000,
                   SUM (DECODE (a.area_id, '0731', 1, 0)) z0731,
                   SUM (DECODE (a.area_id, '0733', 1, 0)) z0733,
                   SUM (DECODE (a.area_id, '0732', 1, 0)) z0732,
                   SUM (DECODE (a.area_id, '0734', 1, 0)) z0734,
                   SUM (DECODE (a.area_id, '0737', 1, 0)) z0737,
                   SUM (DECODE (a.area_id, '0730', 1, 0)) z0730,
                   SUM (DECODE (a.area_id, '0736', 1, 0)) z0736,
                   SUM (DECODE (a.area_id, '0744', 1, 0)) z0744,
                   SUM (DECODE (a.area_id, '0746', 1, 0)) z0746,
                   SUM (DECODE (a.area_id, '0745', 1, 0)) z0745,
                   SUM (DECODE (a.area_id, '0738', 1, 0)) z0738,
                   SUM (DECODE (a.area_id, '0739', 1, 0)) z0739,
                   SUM (DECODE (a.area_id, '0735', 1, 0)) z0735,
                   SUM (DECODE (a.area_id, '0743', 1, 0)) z0743
              FROM (SELECT DISTINCT v.materiel_id, v.materiel_type_id,
                                    v.area_id, v.bill_type
                               FROM v_inout_month_report v
                              WHERE 1 = 1
                                AND (   v.districtid IN (
                                           SELECT man_obj_id
                                             FROM td_sge_keeping_ref
                                            WHERE keeping_id = 'csck1'
                                              AND manager_level = 1)
                                     OR v.storehouse_id IN (
                                           SELECT man_obj_id
                                             FROM td_sge_keeping_ref
                                            WHERE keeping_id = 'csck1'
                                              AND manager_level = 0)
                                    )) a
          GROUP BY materiel_type_id, bill_type)
ORDER BY bill_type, query_type, materiel_type_id
分享到：
| sql中时间相减(case when then的用法)
评论
发表评论

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

相关推荐

    SQL语句的DECODE和NVL

    在 SQL 语句中，DECODE 和 NVL 函数是两个非常重要和常用的函数，它们可以帮助我们实现复杂的逻辑判断和数据处理。本文将详细介绍 DECODE 和 NVL 函数的使用方法和实践应用。 一、DECODE 函数 DECODE 函数是 ...

    易语言常用SQL语句

    在SQL中，可以使用WHERE子句过滤数据，并使用ORDER BY子句对结果集进行排序。 **示例代码：** ```sql SELECT * FROM student_info WHERE NOT EXISTS ( SELECT * FROM student WHERE student_info.id = student.id ...

    SQL语句介绍及使用方法

    ### SQL语句介绍及使用方法 #### 知识点一：查询指定时间前的数据 在数据库管理过程中，经常需要查询某一特定时间之前的数据。例如，要查询一小时前的数据，可以使用如下的SQL语句： ```sql SELECT * FROM asoft ...

    asp精妙的SQL语句例子

    ### ASP中的精妙SQL语句...以上就是从给定的文件标题、描述、标签及部分内容中提炼出来的精妙SQL语句实例及其详细解析。这些SQL语句涵盖了从简单的数据更新到复杂的数据处理等多个方面，希望对学习和实际应用有所帮助。

    sql语句详细讲解及实例

    以下是对给定文件中SQL语句的详细讲解和实例分析： 1. **复制表结构：** ```sql SELECT * INTO b FROM a WHERE 1<>1 ``` 这个命令会创建一个新的表`b`，并从表`a`中复制所有的列定义，但不复制任何数据。`WHERE...

    SQL语句执行顺序说明

    ### SQL语句执行顺序说明 #### 一、SQL语句准备执行阶段 当SQL语句进入Oracle的库缓存后，为了确保其...通过遵循上述步骤和技巧，可以有效地提高SQL语句在Oracle数据库中的执行效率，从而提升应用程序的整体性能。

    SQL高手篇:精妙SQL语句介绍

    在SQL的世界里，掌握一些精妙的SQL语句可以极大地提升你的工作效率，使你在处理数据库时游刃有余。以下是一些高级SQL技巧的详细解释： 1. **复制表结构而不复制数据** 使用`SELECT * INTO 新表名 FROM 原表名 ...

    SQl常用语句大全

    在SQL（Structured Query Language）中，我们经常使用各种语句来操作数据库，包括查询、插入、更新、删除等。以下是一些常见的SQL语句及其详细解释： 1. **复制表结构**： ```sql SELECT * INTO b FROM a WHERE 1...

    DBA常用SQL语句

    此SQL语句用于获取当前用户在SQL*PLUS中的会话标识符和序列号。 **SQL语句**: ```sql SQL>select sid, serial#, status from v$session where audsid = userenv('sessionid'); ``` **解释**: - `v$session`: 包含...

    oracle消耗资源的sql查询语句记录

    上述SQL查询语句仅为一种基础方法，实际工作中还需要结合具体的业务场景和技术需求来进行综合分析与优化。此外，Oracle提供了多种高级工具和特性来支持性能监控与优化，建议根据实际情况灵活运用。

    精妙的SQL语句

    - **标题中的“精妙的SQL语句”** 暗示了这些SQL语句不仅实用，而且在某些方面有着巧妙的设计。 - **描述中的“对学习SQL有很大帮助”** 表明这些SQL语句对于学习者来说是非常有价值的。 **详细知识点说明：** 1. ...

    Web开发中常用的SQL语句

    在Web开发中，SQL（Structured Query Language）是用于管理和处理关系型数据库的重要工具。以下是一些常见的SQL语句及其用途： 1. **复制表结构**： ```sql SELECT * INTO b FROM a WHERE 1<>1 ``` 这条语句...

    SQL 语句完全优化

    - **使用V$SQLAREA视图**：通过查看V$SQLAREA视图中的统计信息（如EXECUTIONS、DISK_READS、BUFFER_GETS等），可以了解SQL语句的执行效率，进而找出性能瓶颈所在。 #### 11. 优化索引使用 - **合理设计索引**：对于...

    Oracle常用性能监控SQL语句

    ### Oracle常用性能监控SQL语句知识点详解 #### 1. 监控高磁盘读取的SQL区域 **SQL语句：** ```sql select * from sys.v_$sqlarea where disk_reads > 100; ``` - **知识点解读：** - `sys.v_$sqlarea` 视图提供了...

    数据库性能监控SQL帮助语句

    数据库性能监控SQL帮助语句 数据库性能监控是数据库管理员的重要任务之一。通过监控数据库的性能，可以快速发现问题，提高数据库的运行效率和稳定性。本文将为您提供九个实用的SQL帮助语句，帮助您监控数据库的性能...

    经典sql语句

    这条SQL语句用于删除表`info`中那些其`infid`在副表`info_bz`中不存在的记录。这里使用了`NOT EXISTS`子句来判断是否在副表中存在对应的记录，从而决定是否进行删除操作。 ### 7. 复杂的联表查询 **SQL语句示例**...

    写出高性能SQL语句的35条方法

    DECODE函数可以避免重复扫描相同记录或连接相同表，特别是在复杂的条件判断场景中，使用DECODE可以显著减少处理时间，提高查询性能。 #### 用WHERE子句替换HAVING子句 **方法十：优化HAVING使用** HAVING子句在...

    sql语句及使用的快速入门

    在本文中，我们将快速入门SQL语句的编写与应用，同时简单回顾SQL的发展历程及其前景。 1. SQL语言基础 SQL包含多种语句，用于执行数据库的各种操作。常见的SQL语句包括： - SELECT：用于查询表中的数据。 - INSERT...

    ORACLE DECODE函数在中国式报表统计查询中的组合条件实现

    ### ORACLE DECODE函数在中国式报表统计查询中的组合条件实现 #### 一、引言 在Oracle数据库中，`DECODE`函数是一种非常实用的功能，主要用于条件判断并返回不同的值。它经常被用来进行简单的条件分支处理，尤其...

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