SQL学习笔记二

函数
    5.3 日期时间函数   
    在 MYSQL、MSSQLServer 和DB2 中可以用字符串来表示日期时间类型，数据库系统会自动在内部将它们转换为日期时间类型，比如“'2008-08-08'”、“2008-08-08 08:00:00”、“08:00:00” 、“2008-08-08 08:00:00.000000”等。
    在 Oracle 中以字符串表示的数据是不能自动转换为日期时间类型的，必须使用TO_DATE()函数来手动将字符串转换为日期时间类型的，比如TO_DATE('2008-08-08','YYYY-MM-DD HH24:MI:SS') 、TO_DATE('2008-08-08 08:00:00', 'YYYY-MM-DD HH24:MI:SS')、TO_DATE('08:00:00', 'YYYY-MM-DD HH24:MI:SS')等。
    5.3.3 取得当前日期时间
    MYSQL中提供了NOW()函数用于取得当前的日期时间，NOW()函数还有SYSDATE()、CURRENT_TIMESTAMP等别名。如下：
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP
如果想得到不包括时间部分的当前日期，则可以使用CURDATE()函数，CURDATE()函数还有CURRENT_DATE等别名。
如果想得到不包括日期部分的当前时间，则可以使用CURTIME()函数，CURTIME ()函数还有CURRENT_TIME等别名。
    5.3.3.2 MSQLServer
    MSSQLServer 中用于取得当前日期时间的函数为GETDATE()。如下：
SELECT GETDATE() as 当前日期时间。
SELECT CONVERT(VARCHAR(50) ,GETDATE( ), 101) as 当前日期
SELECT CONVERT(VARCHAR(50) ,GETDATE(), 108) as 当前时间
    5.3.3.3 Oracle
Oracle 中没有提供取得当前日期时间的函数，不过我们可以到系统表DUAL 中查询SYSTIMESTAMP的值来得到当前的时间戳。如下：
SELECT SYSTIMESTAMP FROM DUAL
或：
SELECT SYSDATE FROM DUAL
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL；
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL

    5.3.3.4 DB2
    DB2 中同样没有提供取得当前日期时间的函数，不过我们可以到系统表
SYSIBM.SYSDUMMY1中查询CURRENT TIMESTAMP的值来得到当前时间戳。如下：
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1
从系统表SYSIBM.SYSDUMMY1 中查询CURRENT TIME的值来得到当前日期值。如下：
SELECT CURRENT TIME
FROM SYSIBM.SYSDUMMY1

5.3.4 日期增减

5.3.4.1 MYSQL
MYSQL中提供了DATE_ADD()函数用于进行日期时间的加法运算，这个函数还有一个别名为ADDDATE()，DATE_ADD()函数的参数格式如下：
DATE_ADD (date,INTERVAL expr type)
其中参数date为待计算的日期；参数expr为待进行加法运算的增量，它可以是数值类型或者字符串类型，取决于type参数的取值；参数type则为进行加法运算的单位，type参数可选值以及对应的expr参数的格式如下表：








。。。

5.3.5 计算日期差额     5.3.5.1 MYSQL        MYSQL中使用DATEDIFF()函数用于计算两个日期之间的差额，其参数调用格式如下：DATEDIFF(date1,date2)函数将返回date1与date2之间的天数差额，如果date2在date1之后返回正值，否则返回负值。SELECT FRegDay,FBirthDay, DATEDIFF(FRegDay, FBirthDay) FROM T_PersonSELECT FRegDay,FBirthDay, DATEDIFF(FRegDay, FBirthDay)/7  FROM T_Person     5.3.5.2 MSSQLServer     MSSQLServer中同样提供了DATEDIFF()函数用于计算两个日期之间的差额，与MYSQL中的DATEDIFF()函数不同，它提供了一个额外的参数用于指定计算差额时使用的单位，其参数调用格式如下：DATEDIFF ( datepart , startdate , enddate )SELECT FRegDay,FBirthDay,DATEDIFF(WEEK, FBirthDay, FRegDay) FROM T_Person     5.3.5.3 Oracle    在Oracle中，可以在两个日期类型的数据之间使用减号运算符“-”，其计算结果为两个日期之间的天数差SELECT FRegDay,FBirthDay,FRegDay-FBirthDay FROM T_Person注意通过减号运算符“-”计算的两个日期之间的天数差是包含有小数部分的，小数部分表示不足一天的部分     5.3.5.4 DB2     DB2中提供了DAYS()函数，这个函数接受一个时间日期类型的参数，返回结果为从0001年1月1日到此日期的天数，SELECT FBirthDay,FRegDay, DAYS(FRegDay)-DAYS(FBirthDay) FROM T_PersonSELECT FBirthDay,FRegDay, (DAYS(FRegDay)-DAYS(FBirthDay))/7 FROM T_Person5.3.6 计算一个日期是星期几     5.3.6.1 MYSQL     MYSQL中提供了DAYNAME()函数用于计算一个日期是星期几，比如下面的SQL语句用于计算出生日期和注册日期各是星期几：SELECT FBirthDay,DAYNAME(FBirthDay),FRegDay,DAYNAME(FRegDay)FROM T_Person
注意MYSQL中DAYNAME()函数返回的是英文的日期表示法。     5.3.6.2 MSQLServer     MSQLServer中提供了DATENAME()函数，这个函数可以返回一个日期的特定部分，并且尽量用名称来表述这个特定部分，其参数格式如下：DATENAME(datepart,date)如果使用Weekday（或者使用别名dw）做为datepart参数调用DATENAME()函数就可以得到一个日期是星期几，比如下面的SQL语句用于计算出生日期和注册日期各是星期几：SELECT FBirthDay,DATENAME(Weekday,FBirthDay),FRegDay,DATENAME(DW, FRegDay)FROM T_Person     5.3.6.3 Oracle     Oracle中提供了TO_CHAR()函数用于将数据转换为字符串类型，当针对时间日期类型数据进行转换的时候，它接受两个参数，其参数格式如下：TO_CHAR(date,format)SELECT FBirthDay,TO_CHAR(FBirthDay, 'YYYY') as yyyy,TO_CHAR(FBirthDay, 'MM') as mm,TO_CHAR(FBirthDay, 'MON') as mon,TO_CHAR(FBirthDay, 'WW') as wwFROM T_PersonWW:日期属于当年的第几周DAY:日期属于周几，以名字的形式表示，比如星期五     5.3.6.4 DB2     DB2中提供了DAYNAME()函数用于计算一个日期是星期几，执行下面的SQL语句我们可以得到出生日期和注册日期各是星期几：SELECTFBirthDay,DAYNAME(FBirthDay) as birthwk,FRegDay,DAYNAME(FRegDay) as regwkFROM T_Person5.3.7 取得日期的指定部分     5.3.7.1 MYSQL     MYSQL中提供了一个DATE_FORMAT()函数用来将日期按照特定各是进行格式化，这个函数的参数格式如下：DATE_FORMAT(date,format)SELECTFBirthDay,DATE_FORMAT(FBirthDay,'%y-%M %D %W') AS bd,FRegDay,DATE_FORMAT(FRegDay,'%Y年%m月%e日') AS rdFROM T_PersonFBirthDay  bd  FRegDay  rd1981-03-22 00:00:00  81-March 22nd  Sunday  1998-05-01 00:00:00  1998年05 月1 日SELECTFBirthDay,DATE_FORMAT(FBirthDay,'%Y') AS y,DATE_FORMAT(FBirthDay,'%j') AS d,DATE_FORMAT(FBirthDay,'%U') AS uFROM T_Person
5.4.1 类型转换    使用类型转换函数不仅可以保证类型转换的正确性，而且可以提高数据处理的速度，因此应该使用显式转换，尽量避免使用隐式转换。         5.4.1.1 MYSQL     MYSQL中提供了CAST()函数和CONVERT()函数用于进行类型转换，CAST()是符合ANSI SQL99的函数，CONVERT() 是符合ODBC标准的函数，这两个函数只是参数的调用方式略有差异，其功能几乎相同。这两个函数的参数格式如下：CAST(expression AS type)CONVERT(expression,type)SELECTCAST('-30' AS SIGNED) as sig,CONVERT ('36', UNSIGNED INTEGER) as usig,CAST('2008-08-08' AS DATE) as d,CONVERT ('08:09:10', TIME) as t         5.4.1.2 MSSQLServerSELECTCAST('-30' AS INTEGER) as i,CONVERT(DECIMAL,'3.1415726') as d,CONVERT(DATETIME,'2008-08-08 08:09:10') as dt     5.4.1.3 Oracle     1) TO_CHAR()TO_CHAR()函数用来将时间日期类型或者数值类型的数据转换为字符串，其参数格式如下：TO_CHAR(expression,format)参数expression为待转换的表达式，参数format为转换后的字符串格式，参数format可以省略，如果省略参数format将会按照数据库系统内置的转换规则进行转换。参数format的可以采用的格式非常丰富，具体可以参考Oracle的联机文档。下面的SQL语句将出生日期和身高按照不同的格式转换为字符串类型：SELECT FBirthDay,TO_CHAR(FBirthDay,'YYYY-MM-DD') as c1,FWeight,TO_CHAR(FWeight,'L99D99MI') as c2,TO_CHAR(FWeight) as c3FROM T_Person     2) TO_DATE()TO_DATE()函数用来将字符串转换为时间类型，其参数格式如下：TO_DATE (expression,format)参数expression为待转换的表达式，参数format为转换格式，参数format可以省略，如果省略参数format将会按照数据库系统内置的转换规则进行转换。下面的SQL语句用于将字符串形式的数据按照特定的格式解析为日期类型：SELECTTO_DATE('2008-08-08 08:09:10', 'YYYY-MM-DD HH24:MI:SS') as dt1,TO_DATE('20080808 080910', 'YYYYMMDD HH24MISS') as dt2FROM DUAL     3) TO_NUMBER()TO_NUMBER()函数用来将字符串转换为数值类型，其参数格式如下：TO_NUMBER (expression,format)
  5.3.7.2 MSSQLServer    在5.3.6.2一节中我们介绍了DATENAME()函数，使用它就可以提取日期的任意部分，比如下面的SQL用于提取每个人员的出生年份、出生时是当年的第几天、出生时是当年的第几周：SELECTFBirthDay,DATENAME(year,FBirthDay) AS y,DATENAME(dayofyear,FBirthDay) AS d,DATENAME(week,FBirthDay) AS uFROM T_Person在MSSQLServer中还提供了一个DATEPART()函数，这个函数也可以用来返回一个日期的特定部分，其参数格式如下：DATEPART (datepart,date)SELECT FBirthDay, DATEPART(Dayofyear,FBirthDay),FRegDay, DATEPART(Year, FRegDay)FROM T_Person     5.3.7.3 Oracle     SELECTFBirthDay,TO_CHAR(FBirthDay,'YYYY') AS y,TO_CHAR(FBirthDay,'DDD') AS d,TO_CHAR(FBirthDay,'WW') AS uFROM T_Person     5.3.7.4 DB2    函数名 功能说明YEAR() 取参数的年份部分MONTH() 取参数的月份部分，返回值为整数MONTHNAME() 对于参数的月部分的月份，返回一个大小写混合的字符串（例如，January）。QUARTER() 取参数的季度数DAYOFYEAR() 返回参数中一年中的第几天，用范围在 1-366 的整数值表示。DAY() 取参数的日部分DAYNAME() 返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，Friday）。WEEK() 返回参数是一年中的第几周DAYOFWEEK() 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期日。HOUR() 取参数的小时部分MINUTE() 取参数的分钟部分SECOND() 取参数的秒钟部分MICROSECOND() 取参数的微秒部分SELECTFBirthDay,YEAR(FBirthDay),FRegDay,MONTHNAME(FRegDay),WEEK(FRegDay)FROM T_Person
参数expression为待转换的表达式，参数format为转换格式，参数format可以省略，如果省略参数format将会按照数据库系统内置的转换规则进行转换。参数format的可以采用的格式非常丰富，具体可以参考Oracle的联机文档。下面的SQL语句用于将字符串形式的数据按照特定的格式解析为数值类型：SELECTTO_NUMBER('33.33') as n1,TO_NUMBER('100.00', '9G999D99') as n2FROM DUAL     4) HEXTORAW()、RAWTOHEX()HEXTORAW()用于将十六进制格式的数据转换为原始值，而RAWTOHEX()函数用来将原始值转换为十六进制格式的数据。例子如下：SELECT HEXTORAW('7D'),RAWTOHEX ('a'),HEXTORAW(RAWTOHEX('w'))FROM DUAL     5) TO_MULTI_BYTE()、TO_SINGLE_BYTE()TO_MULTI_BYTE()函数用于将字符串中的半角字符转换为全角字符，而TO_SINGLE_BYTE()函数则用来将字符串中的全角字符转换为半角字符。例子如下：SELECTTO_MULTI_BYTE('moring'),TO_SINGLE_BYTE('ｈｅｌｌｏ')FROM DUAL     5.4.1.4 DB2     DB2中没有提供专门进行显式类型转换的函数，取而代之的是借用了很多高级语言中的强制类型转换的概念，也就是使用目标类型名做为函数名来进行类型转换，比如要将expr转换为日期类型，那么使用DATE(expr)即可。这种实现机制非常方便，降低了学习难度。SELECT CHAR(FRegDay),INT('33'),DOUBLE('-3.1415926')FROM T_Person5.4.2 空值处理     5.4.2.1 COALESCE()函数    主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理，其参数格式如下：COALESCE ( expression,value1,value2……,valuen)COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。     5.4.2.2 COALESCE()函数的简化版COALESCE()函数可以用来完成几乎所有的空值处理，不过在很多数据库系统中都提供了它的简化版，这些简化版中只接受两个变量，其参数格式如下：MYSQL:IFNULL(expression,value)MSSQLServer:ISNULL(expression,value)Oracle:NVL(expression,value)


5.4.2.3 NULLIF()函数    主流数据库都支持NULLIF()函数，这个函数的参数格式如下：NULLIF ( expression1 , expression2 )如果两个表达式不等价，则 NULLIF 返回第一个 expression1的值。如果两个表达式等价，则 NULLIF 返回第一个 expression1类型的空值.     5.4.3 CASE函数很多人都将CASE称作“流程控制函数”。简单的“等于”逻辑的判断SELECTFName,(CASE FLevelWHEN 1 THEN 'VIP客户'WHEN 2 THEN '高级客户'WHEN 3 THEN '普通客户'ELSE '客户类型错误'END) as FLevelNameFROM T_CustomerSELECTFName,FWeight,(CASEWHEN FWeight<40 THEN 'thin'WHEN FWeight>50 THEN 'fat'ELSE 'ok'END) as isnormalFROM T_Person



  第十章 高级话题10.4 自动增长字段     10.4.1 MYSQL 中的自动增长字段CREATE TABLE T_Person(FId INT PRIMARY KEY AUTO_INCREMENT,FName VARCHAR(20),FAge INT);     10.4.2 MSSQLServer 中的自动增长字段MSSQLServer 中设定一个字段为自动增长字段非只要在表定义中指定字段为IDENTITY即可，格式为IDENTITY(startvalue,step)，其中的startvalue参数值为起始数字，step 参数值为步长，即每次自动增长时增加的值。CREATE TABLE T_Person(FId INT PRIMARY KEY IDENTITY(100,3),FName VARCHAR(20),FAge INT);10.4.3 Oracle中的自动增长字段Oracle 中不像MYSQL 和MSSQLServer 中那样指定一个列为自动增长列的方式，不过在Oracle中可以通过SEQUENCE序列来实现自动增长字段。在Oracle中SEQUENCE 被称为序列，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。在使用SEQUENCE前需要首先定义一个SEQUENCE，定义SEQUENCE的语法如下：CREATE SEQUENCE sequence_nameINCREMENT BY stepSTART WITH startvalue;其中sequence_name 为序列的名字，每个序列都必须有唯一的名字；startvalue 参数值为起始数字，step 参数值为步长，即每次自动增长时增加的值。一旦定义了SEQUENCE，你就可以用CURRVAL来取得SEQUENCE的当前值，也可以通过NEXTVAL来增加SEQUENCE，然后返回 新的SEQUENCE值。比如：sequence_name.CURRVALsequence_name.NEXTVAL如果SEQUENCE不需要的话就可以将其删除：DROP SEQUENCE sequence_name;INCREMENT BY 1START WITH 1;CREATE TABLE T_Person(FId NUMBER (10) PRIMARY KEY,FName VARCHAR2(20),FAge NUMBER (10));INSERT INTO T_Person(FId,FName,FAge) VALUES(seq_PersonId.NEXTVAL,'Tom',18);INSERT INTO T_Person(FId,FName,FAge) VALUES(seq_PersonId.NEXTVAL,'Jim',81);使用SEQUENCE 实现自动增长字段的缺点是每次向表中插入记录的时候都要显式的到SEQUENCE中取得新的字段值，如果忘记了就会造成错误。为了解决这个问题，我们可以使用触发器来解决，创建一个T_Person表上的触发器：CREATE OR REPLACE TRIGGER trigger_personIdAutoIncBEFORE INSERT ON T_PersonFOR EACH ROWDECLAREBEGINSELECT seq_PersonId.NEXTVAL INTO:NEW.FID FROM DUAL;END trigger_personIdAutoInc;这个触发器在T_Person 中插入新记录之前触发，当触发器被触发后则从seq_PersonId中取道新的序列号然后设置给FID字段。执行下面的SQL语句向T_Person表中插入一些数据：INSERT INTO T_Person(FAge)VALUES('Wow',22);10.4.4 DB2中的自动增长字段DB2 中实现自动增长字段有两种方式：定义带有 IDENTITY 属性的列；使用SEQUENCE对象。。。。10.7 开窗函数     10.7.1 开窗函数简介与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。在ISO SQL规定了这样的函数为开窗函数，在Oracle中则被称为分析函数，而在DB2 中则被称为OLAP函数。比如我们想查询每个工资小于5000元的员工信息（城市以及年龄），并且在每行中都显示所有工资小于5000元的员工个数，尝试编写下面的SQL语句：SELECT FCITY , FAGE , COUNT(*)FROM T_PersonWHERE FSALARY<5000SELECT FCITY, FAGE, COUNT(*)FROM T_PersonWHERE FSALARY<5000GROUP BY FCITY , FAGE这个执行结果与我们想像的是完全不同的，这是因为GROUP BY子句对结果集进行了分组，所以聚合函数进行计算的对象不再是所有的结果集，而是每一个分组。可以通过子查询来解决这个问题，SQL如下：SELECT FCITY , FAGE ,(SELECT COUNT(* ) FROM T_PersonWHERE FSALARY<5000)FROM T_PersonWHERE FSALARY<5000如果使用开窗函数来实现同样的效果：SELECT FCITY , FAGE , COUNT(*) OVER()FROM T_PersonWHERE FSALARY<5000OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用OVER关键字来区分这两种用法。在上边的例子中，开窗函数COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。
10.7.2 PARTITION BY 子句开窗函数的OVER关键字后括号中的可以使用PARTITION BY子句来定义行的分区来供进行聚合计算。与GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响.SELECT FName,FCITY , FAGE , FSalary,COUNT(*) OVER(PARTITION BY FCITY)FROM T_PersonCOUNT(*) OVER(PARTITION BY FCITY)表示对结果集按照FCITY进行分区，并且计算当前行所属的组的聚合计算结果。SELECT FName,FCITY, FAGE, FSalary,COUNT(*) OVER(PARTITION BY FCITY),COUNT(*) OVER(PARTITION BY FAGE)FROM T_Person在这个查询结果中，可以看到同一城市中的COUNT(*) OVER(PARTITION BY FCITY)计算结果相同，而且同龄人中的COUNT(*) OVER(PARTITION BY FAGE) 计算结果也相同。     10.7.2 ORDER BY子句,
MSSQLServer中是不支持开窗函数中的ORDER BY子句的。
开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，
而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。ORDER BY子句的语法为：
ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2
例1
SELECT FName, FSalary,
SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM T_Person;
这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序，然后计算从第一行（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的和，这样的计算结果就是按照工资进行排序的工资值的累积和。
例2
SELECT FName, FSalary,
SUM(FSalary) OVER(ORDER BY FSalary RANGE BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM T_Person;
执行完毕我们就能在输出结果中看到下面的执行结果：
FNAME FSALARY 3
John 1000 1000
Lily 2000 7000
Swing 2000 7000
Bill 2000 7000
这个SQL语句与例1中的SQL语句唯一不同的就是“ROWS”被替换成了“RANGE”。“ROWS”是按照行数进行范围定位的，而“RANGE”则是按照值范围进行定位的，这两个不同的定位方式主要用来处理并列排序的情况。比如Lily、Swing、Bill这三个人的工资都是2000元，如果按照“ROWS”进行范围定位，则计算从第一条到当前行的累积和，而如果如果按照“RANGE”进行范围定位，则仍然计算从第一条到当前行的累积和，不过由于等于2000元的工资有三个人，所以计算的累积和为从第一条到2000元工资的人员结，所以对Lily、Swing、Bill这三个人进行开窗函数聚合计算的时候得到的都是7000（“1000+2000+2000+2000”）。
SELECT FName, FSalary,
SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2
FOLLOWING)
FROM T_Person;
这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)”表示按照FSalary进行排序，然后计算从当前行前两行（2 PRECEDING）到当前行后两行（2 FOLLOWING）的工资和，注意对于第一条和第二条而言它们的“前两行”是不存在或者不完整的，因此计算的时候也是要按照前两行是不存在或者不完整进
行计算，同样对于最后两行数据而言它们的“后两行”也不存在或者不完整的，同样要进行类似的处理。
例4
SELECT FName, FSalary,
SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3
FOLLOWING)
FROM T_Person;
这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING)”表示按照FSalary进行排序，然后计算从当前行后一行（1 FOLLOWING）到后三行（3 FOLLOWING）的工资和。注意最后一行没有后续行，其计算结果为空值NULL而非0。
例5
SELECT FName, FSalary,
SUM(FSalary) OVER(ORDER BY FName RANGE BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW)
FROM T_Person;这里的开窗函数“SUM(FSalary) OVER(ORDER BY FName RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”表示按照FName进行排序，然后计算从第一行（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的工资和。
这个SQL语句可以简写为：
SELECT FName, FSalary,
SUM(FSalary) OVER(ORDER BY FName)
FROM T_Person;
例6
SELECT FName, FSalary,
COUNT(*) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW)
FROM T_Person;
这里的开窗函数“COUNT(*) OVER(ORDER BY FSalary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序，然后计算从第一行 （UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的人员的个数，这个可以看作是计算 人员的工资水平排名。
例7
SELECT FName, FSalary,FAge,
MAX(FSalary) OVER(ORDER BY FAge)
FROM T_Person;
这里的开窗函数“MAX(FSalary) OVER(ORDER BY FAge)”是“MAX(FSalary) OVER(ORDER BY FAge RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)” 的简化写法，它表示按照FSalary进行排序，然后计算从第一行（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的人员的最大工资值。
例8
SELECT FName, FSalary,FAge,
MAX(FSalary) OVER(PARTITION BY FAge ORDER BY FSalary)
FROM T_Person;
从这个例子可以看出PARTITION BY子句和ORDER BY可以共同使用，从而可以实现更加
复杂的功能。10.8 WITH子句与子查询     SELECT * FROM T_PersonWHERE FAge=(SELECT FAge FROM T_Person WHERE FName='TOM')OR FSalary=(SELECT FSalary FROM T_Person WHERE FName='TOM')    使用WITH子句来改造上面的SQL语句：WITH person_tom AS(SELECT * FROM T_PersonWHERE FName='TOM')SELECT * FROM T_PersonWHERE FAge=person_tom.FAgeOR FSalary=person_tom.FSalary可以看到WITH子句的格式为：WITH 别名 AS(子查询)定义好别名以后就可以在SQL语句中通过这个别名来引用子查询了，注意这个语句是一个SQL语句，而非存储过程，所以可以远程调用。

评论

2 楼 bigtree2006 2010-05-29  

bigtree2006 写道

WITH person_tom AS(SELECT * FROM T_PersonWHERE FName='TOM')SELECT * FROM T_PersonWHERE FAge=person_tom.FAgeOR FSalary=person_tom.FSalary

打错了，应该是
with person_tom as(select * from t_person where fname='Tom') select * from t_person  where fage=person_tom.fage o
r fsalary= person_tom.fsalary

1 楼 bigtree2006 2010-05-29  

WITH person_tom AS(SELECT * FROM T_PersonWHERE FName='TOM')SELECT * FROM T_PersonWHERE FAge=person_tom.FAgeOR FSalary=person_tom.FSalary
这个SQL语句我在db2 9.5和sql2005中执行都没通过
错误提示为
db2 => with person_tom as(select * from t_person where fname='Tom') select * from t_person  where fage=person_tom.fage or fsalary= person_tom.fsalary
SQL0104N  在 "ge=person_tom.FAgeOR" 后面找到异常标记
"Fsalary"。预期标记可能包括："OR"。  SQLSTATE=42601
db2 =>

sql2005中
WITH person_tom AS
(
SELECT * FROM T_Person
WHERE FName='TOM'
)
SELECT * FROM T_Person
WHERE FAge=person_tom.FAge
OR FSalary=person_tom.FSalary
消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "person_tom.FAge"。
消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "person_tom.FSalary"。
知道这是什么原因吗？