一个sql语句,包含有几乎所有标准查询语法

原创 2001年07月13日 13:47:00

W_KMR01_11 ---- KMM13BR COPYWRITE BY LD 2000/09/07 15:30

  SELECT DISTINCT SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ) AS BGT_TYPE,  
         ( CASE SUBSTRING( KMM102.KMM102_BGT_TYPE,3,2 ) WHEN "09" THEN "1" WHEN "10" THEN "2"
                                                        WHEN "11" THEN "3" ELSE "9" END ) AS BGT_CLASS,  
         SUBSTRING( KMM102.KMM102_BGT_TYPE,1,2 ) AS BGT_TEAM,  

         ( ISNULL( (SELECT SUBSTRING(KMC101.KMC101_NAME,CHARINDEX("-",KMC101.KMC101_NAME)+1,12) FROM KMC101 
                     WHERE ( KMC101.KMC101_TYPE = "BUDGETCODE" )
                       AND ( KMC101.KMC101_CODE = SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ) + "00" ) ),"" ) ) AS BGT_NAME,  

         SUM( ISNULL( KMM101.KMM101_BGT_JAN,0 ) ) AS BGT_AMT01,  
         SUM( ISNULL( KMM101.KMM101_BGT_FEB,0 ) ) AS BGT_AMT02,  
         SUM( ISNULL( KMM101.KMM101_BGT_MAR,0 ) ) AS BGT_AMT03,  
         SUM( ISNULL( KMM101.KMM101_BGT_APR,0 ) ) AS BGT_AMT04,  
         SUM( ISNULL( KMM101.KMM101_BGT_MAY,0 ) ) AS BGT_AMT05,  
         SUM( ISNULL( KMM101.KMM101_BGT_JUN,0 ) ) AS BGT_AMT06,  
         SUM( ISNULL( KMM101.KMM101_BGT_JUL,0 ) ) AS BGT_AMT07,  
         SUM( ISNULL( KMM101.KMM101_BGT_AUG,0 ) ) AS BGT_AMT08,  
         SUM( ISNULL( KMM101.KMM101_BGT_SEP,0 ) ) AS BGT_AMT09,  
         SUM( ISNULL( KMM101.KMM101_BGT_OCT,0 ) ) AS BGT_AMT10,  
         SUM( ISNULL( KMM101.KMM101_BGT_NOV,0 ) ) AS BGT_AMT11,  
         SUM( ISNULL( KMM101.KMM101_BGT_DEC,0 ) ) AS BGT_AMT12,

         ( SUM( ISNULL( KMM101.KMM101_BGT_JAN,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_FEB,0 ) ) +
           SUM( ISNULL( KMM101.KMM101_BGT_MAR,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_APR,0 ) ) +
           SUM( ISNULL( KMM101.KMM101_BGT_MAY,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_JUN,0 ) ) +
           SUM( ISNULL( KMM101.KMM101_BGT_JUL,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_AUG,0 ) ) +
           SUM( ISNULL( KMM101.KMM101_BGT_SEP,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_OCT,0 ) ) +
           SUM( ISNULL( KMM101.KMM101_BGT_NOV,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_DEC,0 ) ) ) AS AMT01, 
         ( "XXXXXXXX" ) AS AMT02, 

         SUM( ISNULL( ( CASE WHEN ( CONVERT(CHAR(8),KMM102.KMM102_CHK_DATE,112) < "YYYYMMDD" )
                             THEN KMM102.KMM102_CHK_AMT ELSE 0 END ),0 ) ) AS AMT09, 
         SUM( ISNULL( ( CASE WHEN ( ( CONVERT(CHAR(8),KMM102.KMM102_OVER_DATE,112) < "YYYYMMDD" )
                              AND KMM102.KMM102_OVER_STATUS <> "00" )
                             THEN KMM102.KMM102_OVER_AMT
                             WHEN ( ( CONVERT(CHAR(8),KMM102.KMM102_OVER_DATE,112) < "YYYYMMDD" )
                              AND KMM102.KMM102_OVER_STATUS = "00" )
                             THEN KMM102.KMM102_OVER_AMT ELSE 0 END ),0 ) ) AS AMT07, 
         SUM( ISNULL( ( SELECT CASE WHEN ( ( CONVERT(CHAR(8),KMM103.KMM103_PAY_DATE,112) < "YYYYMMDD" )
                                     AND KMM103.KMM103_PAY_STATUS <> "2" )
                                    THEN KMM103.KMM103_PAY_AMT ELSE 0 END
                                    FROM KMM103
                                   WHERE ( KMM103.KMM103_YY = KMM102.KMM102_YY ) 
                                     AND ( KMM103.KMM103_BGT_KIND = KMM102.KMM102_BGT_KIND ) 
                                     AND ( KMM103.KMM103_CASE_NO = KMM102.KMM102_CASE_NO ) ),0 ) ) AS AMT03,  
         SUM( ISNULL( ( SELECT CASE WHEN ( ( CONVERT(CHAR(8),KMM103.KMM103_PAY_DATE,112) < "YYYYMMDD" )
                                     AND KMM103.KMM103_PAY_STATUS <> "1" )
                                    THEN KMM103.KMM103_PAY_AMT ELSE 0 END
                                    FROM KMM103
                                   WHERE ( KMM103.KMM103_YY = KMM102.KMM102_YY ) 
                                     AND ( KMM103.KMM103_BGT_KIND = KMM102.KMM102_BGT_KIND ) 
                                     AND ( KMM103.KMM103_CASE_NO = KMM102.KMM102_CASE_NO ) ),0 ) ) AS AMT04  
    FROM KMM102,  
         KMM101 
   WHERE ( KMM102.KMM102_YY = "YYY" ) AND
         ( KMM102.KMM102_BGT_KIND = "1" ) AND
         ( KMM101.KMM101_YY =* KMM102.KMM102_YY ) AND
         ( KMM101.KMM101_BGT_KIND =* KMM102.KMM102_BGT_KIND ) AND
         ( KMM101.KMM101_BGT_TYPE =* KMM102.KMM102_BGT_TYPE )
GROUP BY SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ), 
         SUBSTRING( KMM102.KMM102_BGT_TYPE,3,2 ), 
         SUBSTRING( KMM102.KMM102_BGT_TYPE,1,2 )  
ORDER BY BGT_CLASS ASC,  
         BGT_TYPE DESC 
;

SQL标准语句

前言SQL-结构化查询语言(Structured Query Language),一种高级的非过程化编程语言,用于存取数据以及查询、更新和管理关系数据库系统。 现市面上流行的数据库产品都对SQL进行...
  • pigdreams
  • pigdreams
  • 2017年04月20日 22:34
  • 815

SQL语句快速查询存在某个值的所有存储过程

SQL语句快速查询存在某个值的所有存储过程
  • laijieyao
  • laijieyao
  • 2015年01月29日 09:13
  • 1926

数据库查询语句的左右连接和内连接

一般所说的左连接,外连接是指左外连接,右外连接 先说左外连接和右外连接:...
  • zlp5201
  • zlp5201
  • 2014年08月20日 23:38
  • 3451

标准Sql语句的写法

有次面试非得让我写3表连接查询的标准SQL语句,可是我不知道,我们一般都是直接写而不考虑是否遵循标准,能用即可的。所以检查了一下。具体的如下:(http://blog.chinaunix.net/u/...
  • longronglin
  • longronglin
  • 2008年04月04日 15:30
  • 8135

SQL 语句递归查询 With AS 查找所有子节点

http://www.cnblogs.com/lgx5/p/6419170.html   create table #EnterPrise (   Department n...
  • mengzhengjie
  • mengzhengjie
  • 2017年08月09日 11:29
  • 473

SQL入门(创建数据库语法)

--使用master数据库 use master  go --判断是否存在Bank_ATM if exists (select * from sysdatabases where name='...
  • NET_ZHAO
  • NET_ZHAO
  • 2014年05月14日 12:11
  • 1060

新人学习笔记-SQL的ANSI标准

SQL(STructured Query Language)是一种资料库查询和程式设计语言,用於存取资料以及查询、更新和管理关联式资料库系统。美国国家标準局(ANSI)与国际标準化组织(ISO)已经制...
  • News_chapter
  • News_chapter
  • 2017年10月16日 09:41
  • 148

SQL语句查询语句完整语法

数据库是mysql,使用的数据库表名称是my_student. 表的完整数据信息是: 完整语法是: Select [select选项] 字段列表[字段别名]/* from 数据源 [...
  • u011991249
  • u011991249
  • 2017年03月21日 21:28
  • 551

Sqlserver 查找数据库中含有某字段的所有表

--含有医院编号字段的所有表 select a.[name] 表名 from sysobjects a, (       select [id],count(*) b from sy...
  • zhouhaiqing0905
  • zhouhaiqing0905
  • 2014年01月25日 14:50
  • 2676

sql语句查询数据库里的所有表名,字段名

 一、sql语句查询数据库里的所有表名,字段名1.SQL Server Select SysObjects.Name as TableName, SysColumns.Name as ColumnsN...
  • wangping06
  • wangping06
  • 2009年07月07日 09:46
  • 7371
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:一个sql语句,包含有几乎所有标准查询语法
举报原因:
原因补充:

(最多只允许输入30个字)