`
cuishen
  • 浏览: 296892 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

sql使用总结1.1版

阅读更多
做项目的时候遇到一个sql的问题,按表中某一字段的不同记录做不同的处理,要用到select case when语句的嵌套以及group by语句,想了一下午总算写出来了,现在把成果贴出来:

select CLLX_MX, lx, dw,
	--目标值
	case a.CLLX_MX when '期末库存' then max(case when a.nf=2008 and a.yf=5 then a.mbz end) else avg(case when a.nf=2008 then a.mbz end) end as mbz,
	--去年目标值
	case a.CLLX_MX when '期末库存' then max(case when a.nf=2007 and a.yf=5 then a.mbz end) else avg(case when a.nf=2007 then a.mbz end) end as qn_mbz,
	--本期实际值
	case a.CLLX_MX when '期末库存' then max(case when a.nf=2008 and a.yf=5 then a.SJZ end) else sum(case when a.nf = 2008 and a.yf <= 7 and a.yf >= 2 then a.SJZ end) end as SJZ,
	--去年同期实际值
	case a.CLLX_MX when '期末库存' then max(case when a.nf=2007 and a.yf=5 then a.SJZ end) else sum(case when a.nf = 2007 and a.yf <= 7 and a.yf >= 2 then a.SJZ end) end as QN_SJZ,
	--今年累计实际值
	case a.CLLX_MX when '期末库存' then 0 else sum(case when a.nf = 2008 and a.yf <= 8 then SJZ end) end as LJ_SJZ,
	--去年同期累计实际值
	case a.CLLX_MX when '期末库存' then 0 else sum(case when a.nf = 2007 and a.yf <= 8 then SJZ end) end as LJ_QN_SJZ
		
	from tableName a
	where tjlb = '合计'
	group by CLLX, CLLX_MX, xh, lx, dw order by xh


如上所示,case when语句是可以实现嵌套的,巧用case when不仅提高了效率而且可以避免用自己和自己join可能出现的错误,而对于group by子句的使用,要么select的字段要同样放进group by,要么该字段要放进聚合函数,否则会报错!

再贴几个项目里用到的sql
select xmmx, lx, dw, bz,
	--月计划
	max(case when a.nf=2008 and a.yf=5 then YJH end) as YJH,
	--本月实际
	max(case when a.nf=2008 and a.yf=5 then SJZ end) as SJZ,
	--今年累计实际值
	case when a.xmmx like '%率' or a.xmmx like '%平均人数' or a.xmmx like '%技术指标' or a.xmmx like '%主要产品产量' then max(case when a.nf=2008 and a.yf=5 then SJZ_LJ end) else sum(case when a.nf=2008 and a.yf <=5 then SJZ end) end as LJ_SJZ,
	--case when charindex('现行价', a.xmmx)>0 or charindex('1', a.xmmx)>0 then sum(case when a.nf=2008 and a.yf <=5 then SJZ end) else max(case when a.nf=2008 and a.yf=5 then SJZ_LJ end) end as LJ_SJZ,
	--去年同期累计实际值
	case when a.xmmx like '%率' or a.xmmx like '%平均人数' or a.xmmx like '%技术指标' or a.xmmx like '%主要产品产量' then max(case when a.nf=2007 and a.yf=5 then SJZ_LJ end) else sum(case when a.nf=2007 and a.yf <=5 then SJZ end) end as LJ_QN_SJZ

from tableName a
where  nf = 2008 or nf = 2007
group by xm, xmmx, xh, lx, dw, bz order by xh



select (case when z.xxmmx is null then z.yxmmx else z.xxmmx end) as xmmx, 
            (case when z.xlx is null then z.ylx else z.xlx end) as lx,
	   z.xmbz_zj, z.xmbz_kys, z.xqn_mbz_zj, z.xqn_mbz_kys,
	   z.xsjz_zj, z.xsjz_kys, z.xQN_SJZ_ZJ, z.xQN_SJZ_KYS, z.xLJ_SJZ_ZJ, z.xLJ_SJZ_KYS, z.xLJ_QN_SJZ_ZJ, z.xLJ_QN_SJZ_KYS,
	   z.ymbz_zj, z.ymbz_kys, z.yqn_mbz_zj, z.yqn_mbz_kys,
	   z.ysjz_zj, z.ysjz_kys, z.yQN_SJZ_ZJ, z.yQN_SJZ_KYS, z.yLJ_SJZ_ZJ, z.yLJ_SJZ_KYS, z.yLJ_QN_SJZ_ZJ, z.yLJ_QN_SJZ_KYS   
from (

select x.xmmx as xxmmx, y.xmmx as yxmmx, x.lx as xlx, y.lx as ylx, x.mbz_zj as xmbz_zj, x.mbz_kys as xmbz_kys, x.qn_mbz_zj as xqn_mbz_zj, x.qn_mbz_kys as xqn_mbz_kys,
	   x.sjz_zj as xsjz_zj, x.sjz_kys as xsjz_kys, x.QN_SJZ_ZJ as xQN_SJZ_ZJ, x.QN_SJZ_KYS as xQN_SJZ_KYS, x.LJ_SJZ_ZJ as xLJ_SJZ_ZJ,
	   x.LJ_SJZ_KYS as xLJ_SJZ_KYS, x.LJ_QN_SJZ_ZJ as xLJ_QN_SJZ_ZJ, x.LJ_QN_SJZ_KYS as xLJ_QN_SJZ_KYS,
	   y.mbz_zj as ymbz_zj, y.mbz_kys as ymbz_kys, y.qn_mbz_zj as yqn_mbz_zj, y.qn_mbz_kys as yqn_mbz_kys, y.sjz_zj as ysjz_zj,
	   y.sjz_kys as ysjz_kys, y.QN_SJZ_ZJ as yQN_SJZ_ZJ, y.QN_SJZ_KYS as yQN_SJZ_KYS, y.LJ_SJZ_ZJ as yLJ_SJZ_ZJ,
	   y.LJ_SJZ_KYS as yLJ_SJZ_KYS, y.LJ_QN_SJZ_ZJ as yLJ_QN_SJZ_ZJ, y.LJ_QN_SJZ_KYS as yLJ_QN_SJZ_KYS
from
	   (select a.xmmx as xmmx, a.lx as lx,
	   --目标值
	   avg(case when a.nf=2008 then mbz_zj end) as mbz_zj,
	   avg(case when a.nf=2008 then mbz_kys end) as mbz_kys,
	   --去年目标值
	   avg(case when a.nf=2007 then mbz_zj end) as qn_mbz_zj,
	   avg(case when a.nf=2007 then mbz_kys end) as qn_mbz_kys,
	   --本期实际值 
	   sum(case when a.nf = 2008 and a.yf <= 10 and a.yf >= 2 then SJZ_ZJ end) as SJZ_ZJ,
	   sum(case when a.nf = 2008 and a.yf <= 10 and a.yf >= 2 then SJZ_KYS end) as SJZ_KYS,
	   --去年同期实际值
	   sum(case when a.nf = 2007 and a.yf <= 10 and a.yf >= 2 then SJZ_ZJ end) as QN_SJZ_ZJ,
	   sum(case when a.nf = 2007 and a.yf <= 10 and a.yf >= 2 then SJZ_KYS end) as QN_SJZ_KYS,
	   --今年累计实际值
	   sum(case when a.nf = 2008 and a.yf <= 10 then SJZ_ZJ end) as LJ_SJZ_ZJ,
	   sum(case when a.nf = 2008 and a.yf <= 10 then SJZ_KYS end) as LJ_SJZ_KYS,
	   --去年同期累计实际值
	   sum(case when a.nf = 2007 and a.yf <= 10 then SJZ_ZJ end) as LJ_QN_SJZ_ZJ,
	   sum(case when a.nf = 2007 and a.yf <= 10 then SJZ_KYS end) as LJ_QN_SJZ_KYS
	   from tableName a
	   where a.tjlb = '项目数' and a.tjfw = '合计' and (a.nf = 2008 or a.nf = 2007)
	   group by a.xm, a.xmmx, a.xh, a.lx
	   order by a.xh) x
full outer join
	   (select b.xmmx as xmmx, b.lx as lx,
	   --目标值
	   avg(case when b.nf=2008 then mbz_zj end) as mbz_zj,
	   avg(case when b.nf=2008 then mbz_kys end) as mbz_kys,
	   --去年目标值
	   avg(case when b.nf=2007 then mbz_zj end) as qn_mbz_zj,
	   avg(case when b.nf=2007 then mbz_kys end) as qn_mbz_kys,
	   --本期实际值 
	   sum(case when b.nf = 2008 and b.yf <= 10 and b.yf >= 2 then SJZ_ZJ end) as SJZ_ZJ,
	   sum(case when b.nf = 2008 and b.yf <= 10 and b.yf >= 2 then SJZ_KYS end) as SJZ_KYS,
	   --去年同期实际值
	   sum(case when b.nf = 2007 and b.yf <= 10 and b.yf >= 2 then SJZ_ZJ end) as QN_SJZ_ZJ,
	   sum(case when b.nf = 2007 and b.yf <= 10 and b.yf >= 2 then SJZ_KYS end) as QN_SJZ_KYS,
	   --今年累计实际值
	   sum(case when b.nf = 2008 and b.yf <= 10 then SJZ_ZJ end) as LJ_SJZ_ZJ,
	   sum(case when b.nf = 2008 and b.yf <= 10 then SJZ_KYS end) as LJ_SJZ_KYS,
	   --去年同期累计实际值
	   sum(case when b.nf = 2007 and b.yf <= 10 then SJZ_ZJ end) as LJ_QN_SJZ_ZJ,
	   sum(case when b.nf = 2007 and b.yf <= 10 then SJZ_KYS end) as LJ_QN_SJZ_KYS
	   from tableName b
	   where b.tjlb = '预计用款' and b.tjfw = '合计' and (b.nf = 2008 or b.nf = 2007)
	   group by b.xm, b.xmmx, b.xh, b.lx
	   order by b.xh) y
on x.xmmx = y.xmmx
) as z

分享到:
评论
3 楼 cuishen 2009-06-09  
最近在用Oracle数据库做项目,刚写出一个很复杂的SQL,贴出来和大家分享:

    select
    c.busi_no, c.BANK_ID, c.IDTYPE_ID, c.LEFTPAY_MONTHS, c.ID_NO, c.ID_CTRY, c.UPDATE_DATE, c.BUSITYPE_ID, 
         c.BUSIDETTYPE_ID, c.LOCUS_ID, c.LOCUS_NAME, c.OPEN_DATE, c.TERM_DATE,
           c.CURRENCY_ID, c.CREDIT_AMT, c.SHARECREDIT_AMT, c.MAXDEBT_AMT, c.GUARANTTYPE_ID, c.PAYFREQ_ID, c.PAY_MONTHS, c.LASTPAY_DATE,
           c.CURROVER_TIMES, c.BREACH_TIMES, c.MAXOVER_TIMES, c.FIVE_STATUS, c.ACCTSTAT_ID, c.RETURN_STATUS,
           nvl(c.OVER_AMT, 0) as OVER_AMT, c.bill_date, nvl(c.OVER30CAP_AMT, 0) as OVER30CAP_AMT, nvl(c.OVER60CAP_AMT, 0) as OVER60CAP_AMT, 
           nvl(c.OVER90CAP_AMT, 0) as OVER90CAP_AMT, nvl(c.OVER180CAP_AMT, 0) as OVER180CAP_AMT, nvl(d.EXPPAY_AMT, 0) as EXPPAY_AMT, 
           nvl(d.REALPAY_AMT, 0) as REALPAY_AMT
    from
    (select a.busi_no, a.BANK_ID, a.IDTYPE_ID, a.LEFTPAY_MONTHS, a.ID_NO, a.ID_CTRY, a.UPDATE_DATE, a.BUSITYPE_ID, 
         a.BUSIDETTYPE_ID, a.LOCUS_ID, e.REMOTE_NAME as LOCUS_NAME, a.OPEN_DATE, a.TERM_DATE,
           a.CURRENCY_ID, a.CREDIT_AMT, a.SHARECREDIT_AMT, a.MAXDEBT_AMT, a.GUARANTTYPE_ID, a.PAYFREQ_ID, a.PAY_MONTHS, a.LASTPAY_DATE,
           a.CURROVER_TIMES, a.BREACH_TIMES, a.MAXOVER_TIMES, a.FIVE_STATUS, a.ACCTSTAT_ID, a.RETURN_STATUS,
           b.OVER_AMT, b.bill_date, b.OVER30CAP_AMT, b.OVER60CAP_AMT, b.OVER90CAP_AMT, b.OVER180CAP_AMT
      from (select busi_no, BANK_ID, IDTYPE_ID, ID_NO, ID_CTRY, UPDATE_DATE, BUSITYPE_ID, BUSIDETTYPE_ID, LOCUS_ID, OPEN_DATE, TERM_DATE,
           CURRENCY_ID, CREDIT_AMT, SHARECREDIT_AMT, MAXDEBT_AMT, GUARANTTYPE_ID, PAYFREQ_ID, PAY_MONTHS, LASTPAY_DATE,
           CURROVER_TIMES, BREACH_TIMES, MAXOVER_TIMES, FIVE_STATUS, ACCTSTAT_ID, RETURN_STATUS,
                  (case when ((TO_CHAR(TERM_DATE, 'YYYY') - TO_CHAR(DATA_DATE, 'YYYY')) * 12 +
                   (TO_CHAR(TERM_DATE, 'MM') - TO_CHAR(DATA_DATE, 'MM'))) > 0 then 
                   ((TO_CHAR(TERM_DATE, 'YYYY') - TO_CHAR(DATA_DATE, 'YYYY')) * 12 +
                   (TO_CHAR(TERM_DATE, 'MM') - TO_CHAR(DATA_DATE, 'MM'))) else 0 end)AS LEFTPAY_MONTHS
              from crd_acc_info
             where busi_no = '430700151201100'
               ) a    
      left join (select busi_no, max(BILL_DATE) as bill_date,
                        SUM(CASE
                              WHEN COALESCE(EXPCAP_AMT + EXPINT_AMT + EXPINTPEN_AMT,
                                            0) <= COALESCE(REALCAP_AMT + REALINT_AMT +
                                                           REALINTPEN_AMT,
                                                           0) THEN 0
                              ELSE
                               COALESCE(EXPCAP_AMT + EXPINT_AMT + EXPINTPEN_AMT -
                                        REALCAP_AMT - REALINT_AMT - REALINTPEN_AMT,
                                        0)
                            END) AS OVER_AMT,
                        SUM(CASE
                              WHEN TO_DATE(DATA_DATE) - BILL_DATE > 30 AND
                                   TO_DATE(DATA_DATE) - BILL_DATE <= 60 THEN
                               COALESCE(EXPCAP_AMT - REALCAP_AMT, 0)
                              ELSE 0
                            END) AS OVER30CAP_AMT,
                        SUM(CASE
                              WHEN TO_DATE(DATA_DATE) - BILL_DATE > 60 AND
                                   TO_DATE(DATA_DATE) - BILL_DATE <= 90 THEN
                               COALESCE(EXPCAP_AMT - REALCAP_AMT, 0)
                              ELSE 0
                            END) AS OVER60CAP_AMT,
                        SUM(CASE
                              WHEN TO_DATE(DATA_DATE) - BILL_DATE > 90 AND
                                   TO_DATE(DATA_DATE) - BILL_DATE <= 180 THEN
                               COALESCE(EXPCAP_AMT - REALCAP_AMT, 0)
                              ELSE 0
                            END) AS OVER90CAP_AMT,
                        SUM(CASE
                              WHEN TO_DATE(DATA_DATE) - BILL_DATE > 180 THEN
                               COALESCE(EXPCAP_AMT - REALCAP_AMT, 0)
                              ELSE 0
                            END) AS OVER180CAP_AMT
                 
                   from crd_ret_info
                  where busi_no = '430700151201100'
                  group by busi_no) b on a.busi_no = b.busi_no
                  left join (select remote_code, REMOTE_NAME from stand_code where table_id = 'LOCUS')e
                  on a.LOCUS_ID = e.remote_code
                  ) c
      left join 
      (select busi_no, (nvl(k.expcap_amt,0) + nvl(k.expint_amt,0) + nvl(k.expintpen_amt,0)) as EXPPAY_AMT, 
              (nvl(k.realcap_amt,0) + nvl(k.realint_amt,0) + nvl(k.realintpen_amt,0)) as REALPAY_AMT 
              from crd_ret_info k
              where busi_no = '430700151201100' and BILL_DATE = (select max(BILL_DATE)as BILL_DATE 
                                              from crd_ret_info 
                                              where busi_no = '430700151201100')) d
      on c.busi_no = d.busi_no  
2 楼 cuishen 2008-12-09  
duronshi 写道
个人感觉以上写的SQL执行效率不高,执行时间应该不是很理想


以上sql是针对DB2数据库的,DB2数据库反而倾向于让开发者写复杂的join语句和查询语句,因为它对复杂的查询做了特别的优化,以上sql代码都是取自项目代码,在生产环境运行正常,而且速度也是不慢的!
1 楼 duronshi 2008-09-10  
个人感觉以上写的SQL执行效率不高,执行时间应该不是很理想

相关推荐

    sql2005的sqljdbc_1.1.jar包(文件)

    总结,sqljdbc_1.1.jar是SQL Server 2005与Java应用之间的重要桥梁,提供了高效、安全的JDBC接口。了解并熟练掌握其使用方法,对于开发Java应用程序与SQL Server 2005数据库之间的交互至关重要。

    GlobalEC_Enterprise_SQL_1.1.rar

    总结起来,"GlobalEC_Enterprise_SQL_1.1.rar"是一个基于JSP技术的电子商务企业SQL系统,涵盖了数据库管理、服务器端动态内容生成以及可能的课程设计实践。用户需要熟悉JSP和SQL基础,才能有效利用这个系统。通过...

    PyPI 官网下载 | smartnoise-sql-0.2.1.1.tar.gz

    总结来说,“smartnoise-sql-0.2.1.1.tar.gz”是PyPI上一个专注于智能噪声SQL处理的Python库。它提供了一种安全的方式来处理和分析敏感数据,保护用户隐私,同时保持数据的实用价值。对于需要处理大量敏感数据的...

    SQL2005配置总结[文].pdf

    - **安装**: 解压下载的文件(sqljdbc_1.1.1501.101_chs.exe),将解压后的sqljdbc_1.1文件夹复制到系统指定的目录下,例如`%ProgramFiles%\sqljdbc_1.1`。 ##### 2.3 配置Classpath 由于JDBC驱动程序并不包含在Java ...

    T-SQL 基本语句归纳总结(SQL server 2008)

    在 SQL Server 2008 中,T-SQL(Transact-SQL)是一种使用广泛的数据库语言,它提供了丰富的语法和功能来管理和操作数据库。下面是 T-SQL 基本语句的归纳总结: 数据库及数据库对象的创建与修改 1.1. 数据库的创建...

    ASP源码—Tsys信息发布系统 Tsys V1.1 SqlServer 正式版.zip

    Tsys V1.1使用的数据库管理系统是SQL Server,这是微软公司开发的一款强大的关系型数据库系统。SQL Server支持多种数据库语言,包括Transact-SQL(T-SQL),能够处理大规模的数据存储和复杂的查询操作。与ASP结合...

    SQL语法入门教程手册v1.1

    ### SQL语法入门教程手册v1.1 知识点总结 #### 一、SQL简介与基础知识 - **SQL概述**:SQL(Structured Query Language)是一种专门用于管理关系型数据库的标准语言,广泛应用于数据库的创建、删除、数据检索与...

    SQL语句实现跨Sql server数据库操作实例

    本文将通过具体的例子来展示如何使用SQL语句在不同的SQL Server实例之间执行查询和插入操作。 #### 实例一:跨SQL Server实例查询数据 首先,我们来看一下如何在一个SQL Server实例中查询另一个SQL Server实例的...

    基于ASP的Tsys信息发布系统 Tsys V1.1 SqlServer 正式版.zip

    总结来说,"基于ASP的Tsys信息发布系统 Tsys V1.1 SqlServer 正式版.zip"是一个使用ASP编程语言,结合SQL Server数据库构建的信息管理系统,具备内容管理、用户交互等多种功能。通过解压并分析文件,可以深入研究其...

    T-SQl查询总结.doc

    《SQL Server 2005 T-SQL查询总结》 SQL Server 2005作为微软的一款重要数据库管理系统,引入了许多新特性和增强功能,尤其是其T-SQL(Transact-SQL)语言,使得数据库管理和数据操作更为高效。本文将对SQL Server ...

    jstl.jar_standard.jar 1.1版本

    总结来说,JSTL.jar和Standard.jar 1.1版本是Java Web开发的重要工具,它们提供了丰富的服务器端标签,极大地提高了JSP的可读性和维护性。通过合理使用JSTL,开发者可以避免在JSP页面中混杂过多的Java代码,使代码...

    JavaJSP中使用JDBC连接SQL Server 2005

    - 访问微软官方网站或使用直接链接下载`Microsoft SQL Server 2005 JDBC Driver 1.1`。 - 解压缩下载的`sqljdbc_1.1.1501.101_chs.exe`,并将`sqljdbc_1.1`目录复制到`%ProgramFiles%`(通常位于C:\Program Files...

    收获不止SQL优化

    13.1.5 使用10046 trace跟踪PL/SQL 368 13.2 PL/SQL优化其他相关扩展 369 13.2.1 编译无法成功 369 13.2.2 通用脚本分享 370 13.3 本章习题、总结与延伸 380 第14章 动手,高级写法应用让SQL飞 381 14.1 具体...

    NUCMS V1.1 前台SQL注入1

    ### NUCMS V1.1 前台SQL注入漏洞详解 #### 一、漏洞背景与概述 **NuCMS**是一款国内优秀的开源内容管理系统(Content Management System, CMS),它基于PHP和MySQL开发,采用了轻量级架构及多应用化的开发模式。...

    JAVA与SQLServer数据库连接总结.doc

    本文将详细介绍JAVA与SQLServer数据库连接的总结,包括使用JDBC-ODBC桥方式和JDBC直接连接SQLServer数据库两种方法。 一、使用JDBC-ODBC桥方式连接SQLServer数据库 使用JDBC-ODBC桥方式连接SQLServer数据库需要...

    SQL语句经验总结

    ### SQL语句经验总结 #### 一、概述 在日常工作中,SQL作为数据库查询与管理的基础工具,其重要性不言而喻。SQL不仅能够帮助我们高效地管理和操作数据库,而且还能确保数据的一致性和安全性。本文将针对SQL中最...

    jstl1.1jar包

    JSTL1.1版是JSTL的一个稳定版本,相较于早期版本,它在功能和性能上都有所提升,并且兼容Tomcat5.x服务器。 **JSTL核心标签库** JSTL的核心标签库(Core Tag Library)包含了用于控制流程、数据操作等基本功能的...

    windows SQL Server 2017 标准版

    - **性能监控**:使用SQL Server Profiler、动态管理视图(DMV)等工具监控数据库性能。 - **备份与恢复**:定期备份数据,确保在发生故障时能够快速恢复。 ### 五、总结 综上所述,Windows SQL Server 2017 标准版...

    Sql Server 游标的使用 事务的使用

    ### SQL Server 游标的使用与事务管理 #### 一、游标的使用方法 在 SQL Server 中,游标是一种用于处理结果集中的数据行的一种机制。它允许开发人员逐行访问查询的结果,这对于需要对每一条记录进行特定操作的场景...

Global site tag (gtag.js) - Google Analytics