[sql-函数参考手册]

kuaijishi

贡献于2017-02-13

字数:252394 关键词: SQL 手册 Go ini

SQL 函数参考手册 Oracle、SQL Server、MySQL 南無阿弥陀佛 2 数据库系统自带函数 ..........................................................................................................................................................7 字符函数......................................................................................................................................................................7 由字符获取 ASCII 码 ASCII...............................................................................................................................7 格式化为 ASCII 字符串信息 ASCIISTR(Oracle Only) ..................................................................................7 由字符获取 UNICODE 码 .................................................................................................................................8 返回字符集 CHARSET (MySQL Only)..............................................................................................................8 由 ASCII 码获取字符 CHR / CHAR..................................................................................................................8 由 UNICODE 码获取字符 NCHAR...................................................................................................................9 字符串连接 CONCAT || / +.................................................................................................................................9 单词首字母大写 INITCAP (Oracle Only)........................................................................................................10 字符替换 INSERT (MySQL Only) ...................................................................................................................10 字符串搜索 INSTR / CHARINDEX / LOCATE.............................................................................................. 11 获取字符串左边开始指定个数的字符 LEFT.................................................................................................12 字符串长度 LENGTH / LEN............................................................................................................................13 加载文件 LOAD_FILE (MySQL Only)......................................................................................................14 字符转换为小写 LOWER / LCASE.................................................................................................................14 左边字符填充 LPAD ........................................................................................................................................15 去除左边空格与字符 LTRIM...........................................................................................................................16 MAKE_SET(MySQL Only)...............................................................................................................................17 检索指定模式首次出现位置 PATINDEX (SQL Server Only) ........................................................................17 正则表达式检索 REGEXP_LIKE ( Oracle Only )...........................................................................................18 正则表达式字符截取 REGEXP_SUBSTR ( Oracle Only ).............................................................................19 正则表达式字符位置检索 REGEXP_INSTR ( Oracle Only ) ........................................................................20 正则表达式字符替换 REGEXP_REPLACE ( Oracle Only ) ..........................................................................22 字符替换 REPLACE.........................................................................................................................................23 增加标示符定义 QUOTENAME/QUOTE........................................................................................................24 重复字符串 REPLICATE / REPEAT................................................................................................................24 字符反转 REVERSE.........................................................................................................................................25 获取字符串右边开始指定个数的字符 RIGHT ..............................................................................................26 右边字符填充 RPAD........................................................................................................................................26 去除右边空格与字符 RTRIM...........................................................................................................................27 取得发音 SOUNDEX .......................................................................................................................................28 取得发音的不同 DIFFERENCE ......................................................................................................................29 返回由重复的空格组成的字符串 SPACE.......................................................................................................29 小数转换为字符串 STR (SQL Server Only).....................................................................................................30 逐字符比较字符串大小 STRCMP (MySQL Only) ..........................................................................................30 删除指定长度的字符并在指定的起始点插入另一组字符 STUFF(SQL Server Only) ................................31 返回字符串一部分 SUBSTR / SUBSTRING ..................................................................................................33 SUBSTRING_INDEX (MySQL Only) ..............................................................................................................33 TRANSLATE.....................................................................................................................................................34 删除前后空格或其它字符 TRIM.....................................................................................................................38 转换为大写 UPPER..........................................................................................................................................38 数学函数....................................................................................................................................................................39 绝对值 ABS.......................................................................................................................................................39 反余弦 ACOS....................................................................................................................................................40 南無阿弥陀佛 3 反正弦 ASIN.....................................................................................................................................................40 反正切 ATAN....................................................................................................................................................41 反正切 ATAN2 / ATN2 .....................................................................................................................................41 十进制转二进制 BIN (MySQL Only)..............................................................................................................42 二进制转十进制 BIN_TO_NUM (Oracle Only)...............................................................................................42 按位与操作 BITAND (Oracle Only) ................................................................................................................43 向上取整 CEIL / CEILING...............................................................................................................................43 进制转换 CONV (MySQL Only)......................................................................................................................43 余弦 COS...........................................................................................................................................................44 双曲余弦 COSH (Oracle Only) .........................................................................................................................45 三角余切 COT (SQL Server Only)....................................................................................................................45 CRC32 (MySQL Only).......................................................................................................................................45 度数为单位的角度 DEGREES ........................................................................................................................46 指数值 EXP........................................................................................................................................................46 向下取整 FLOOR .............................................................................................................................................47 保留小数位数 FORMAT (MySQL Only).........................................................................................................47 转 16 进制 HEX (MySQL Only)................................................................................................................48 INTERVAL.........................................................................................................................................................48 自然对数 LN / LOG..........................................................................................................................................48 对数 LOG..........................................................................................................................................................49 以 2 为底的对数 LOG2....................................................................................................................................49 以 10 为底的对数 LOG10................................................................................................................................50 余数 MOD / %...................................................................................................................................................50 PI 的常量值 PI..................................................................................................................................................51 乘指定次方 POWER.........................................................................................................................................52 度数值返回弧度值 RADIANS..........................................................................................................................52 随机数 RAND...................................................................................................................................................53 四舍五入 ROUND............................................................................................................................................54 表达式的正 (+1)、零 (0) 或负 (-1) 号 SIGN..............................................................................................55 正弦 SIN............................................................................................................................................................55 双曲正弦 SINH (Oracle Only)...........................................................................................................................56 平方 SQUARE (SQL Server Only)....................................................................................................................56 平方根 SQRT.....................................................................................................................................................56 正切 TAN...........................................................................................................................................................57 双曲正切 TANH (Oracle Only) .........................................................................................................................57 截尾 TRUNC / ROUND.....................................................................................................................................57 数字格式化显示 ................................................................................................................................................58 日期函数....................................................................................................................................................................62 取得数据库系统时间 SYSDATE / GETDATE() .............................................................................................62 日期增减计算 ....................................................................................................................................................65 日期组成部分的取得 ........................................................................................................................................68 两个日期之间的差值 ........................................................................................................................................73 日期的截尾 ........................................................................................................................................................75 日期的名称 DATENAME ................................................................................................................................78 日期/时间的格式化[日期转换为字符串].........................................................................................................79 日期/时间的格式化[字符串转换为日期].........................................................................................................83 南無阿弥陀佛 4 时间/秒转换.......................................................................................................................................................84 新建日期/时间...................................................................................................................................................84 时区相关 ............................................................................................................................................................85 一般比较函数 ............................................................................................................................................................87 取得最大值 GREATEST...................................................................................................................................87 取得最小值 LEAST..........................................................................................................................................88 数据字典类函数 ........................................................................................................................................................89 返回列名 COL_NAME.....................................................................................................................................89 列属性 COLUMNPROPERTY.........................................................................................................................89 数据库选项设置 DATABASEPROPERTYEX.................................................................................................90 数据库中的主体的 ID 号 DATABASE_PRINCIPAL_ID .............................................................................90 取得数据库标识号 DB_ID(SQL Server Only) ................................................................................................91 取得数据库名 DB_NAME(SQL Server Only)..................................................................................................91 取得文件标识号 FILE_IDEX ...........................................................................................................................91 取得逻辑文件名 FILE_NAME .........................................................................................................................91 取得文件组标识号 FILEGROUP_ID ...............................................................................................................92 取得文件组名 FILEGROUP_NAME................................................................................................................92 读取文件属性 FILEPROPERTY......................................................................................................................92 取得工作站标识号 HOST_ID (SQL Server Only) ..........................................................................................92 取得工作站名称 HOST_NAME(SQL Server Only)........................................................................................93 返回定义的源文本 OBJECT_DEFINITION ...................................................................................................93 返回对象标识 OBJECT_ID............................................................................................................................94 返回对象名称 OBJECT_NAME.......................................................................................................................94 返回对象构架名 OBJECT_SCHEMA_NAME................................................................................................94 对象的有关信息 OBJECTPROPERTY............................................................................................................95 对象的有关信息 OBJECTPROPERTYEX ......................................................................................................95 获取当前用户可用权限 PERMISSIONS.........................................................................................................95 获取架构名称 SCHEMA_ID............................................................................................................................96 获取架构名称 SCHEMA_NAME....................................................................................................................96 获取服务器实例的属性信息 SERVERPROPERTY .......................................................................................96 返回会话的 SET 选项设置 SESSIONPROPERTY .......................................................................................97 返回上次更新指定索引的统计信息的日期 STATS_DATE...........................................................................97 返回用户登录名的安全标识号 (SID) SUSER_SID(SQL Server Only) ......................................................97 从用户的安全标识号 (SID) 返回登录标识名 SUSER_SNAME(SQL Server Only)...................................98 取得用户 ID UID / USER_ID............................................................................................................................98 取得用户名 USER_NAME ..............................................................................................................................99 获取数据类型名称的编号 TYPE_ID ..............................................................................................................99 获取指定类型 ID 的未限定的类型名称 TYPE_NAME...............................................................................99 返回有关数据类型的信息 TYPEPROPERTY ................................................................................................99 其他函数..................................................................................................................................................................101 取得客户端应用程序名字 APP_NAME .......................................................................................................101 数据校验 BINARY_CHECKSUM .................................................................................................................101 类型转换 CAST..............................................................................................................................................102 字符串转换为行编号 CHARTOROWID (Oracle Only)................................................................................105 数据校验 CHECKSUM ..................................................................................................................................105 数据校验 CHECKSUM_AGG........................................................................................................................107 南無阿弥陀佛 5 返回列的定义的长度 COL_LENGTH...........................................................................................................108 条件判断函数 DECODE (Oracle Only).........................................................................................................109 非空判断 NVL / ISNULL............................................................................................................................... 110 非空判断 NVL2 .............................................................................................................................................. 111 取得第一个非空数据 COALESCE................................................................................................................ 112 取得字段所占用字节数 VSIZE / DATALENGTH........................................................................................ 113 判断是否是日期 ISDATE (SQL Server Only) ............................................................................................... 113 判断是否是数字 ISNUMERIC(SQL Server Only) ......................................................................................... 114 取得 GUID 号 SYS_GUID / NEWID.......................................................................................................... 115 如果两个指定的表达式相等,则返回空值 NULLIF .................................................................................. 116 取指定位置的数据 ELT (MySQL Only)........................................................................................................ 117 取数据的位置 FIELD (MySQL Only)............................................................................................................. 117 FIND_IN_SET (MySQL Only) ........................................................................................................................ 118 PARSENAME(SQL Server Only)............................................................................................................... 118 统计分析函数 .......................................................................................................................................................... 119 测试表/数据..................................................................................................................................................... 119 ROW_NUMBER 顺序编号............................................................................................................................120 RANK 排名不连续.........................................................................................................................................123 DENSE_RANK 排名连续..............................................................................................................................126 NTILE 分组.....................................................................................................................................................128 计算一行在组中的相对位置 CUME_DIST..................................................................................................131 PERCENT_RANK ...........................................................................................................................................132 计算百分比 RATIO_TO_REPORT ................................................................................................................133 窗口函数 (Oracle 特有)....................................................................................................................................134 逐步累计 ..........................................................................................................................................................135 移动平均 1 .......................................................................................................................................................136 移动平均 2 .......................................................................................................................................................136 移动平均 3 .......................................................................................................................................................137 FIRST_VALUE and LAST_VALUE................................................................................................................138 LAG/LEAD......................................................................................................................................................139 FIRST/LAST....................................................................................................................................................140 WITHIN GROUP.............................................................................................................................................141 PERCENTILE_CONT | PERCENTILE_DISC................................................................................................142 中位数 Median................................................................................................................................................144 MODEL 查询函数(Oracle 特有).............................................................................................................................145 CURRENTV() 取当前行................................................................................................................................148 IS PRESENT / PRESENTV / PRESENTNNV ................................................................................................151 IGNORE NAV 选项 ........................................................................................................................................154 RULES UPDATE 选项 ....................................................................................................................................155 REFERENCE 语句 与 CV 函数....................................................................................................................157 Iterative 方法...................................................................................................................................................158 AUTOMATIC ORDER ....................................................................................................................................160 排序规则 ..........................................................................................................................................................161 安全函数..................................................................................................................................................................163 是否可访问指定数据库 HAS_DBACCESS(SQL Server Only)....................................................................163 用户是否是 Microsoft? Windows NT? 组或 Microsoft SQL Server? 角色的成员 IS_MEMBER (SQL 南無阿弥陀佛 6 Server Only)......................................................................................................................................................163 指明当前的用户登录是否是指定的服务器角色的成员 IS_SRVROLEMEMBER (SQL Server Only)..164 取得连接到 SQL Server 实例的登录名 ORIGINAL_LOGIN SYSTEM_USER .......................................164 取得用户名 USER..........................................................................................................................................165 聚集函数..................................................................................................................................................................166 平均值 AVG.....................................................................................................................................................166 相关系数 CORR(Oracle)...........................................................................................................................167 行的计数 COUNT............................................................................................................................................168 COUNT_BIG....................................................................................................................................................169 总体协方差 COVAR_POP..............................................................................................................................169 样本协方差 COVAR_SAMP..........................................................................................................................170 最大值 MAX....................................................................................................................................................171 中位数 MEDIAN ............................................................................................................................................172 最小值 MIN......................................................................................................................................................172 标准偏差 STDDEV / STDEV.........................................................................................................................173 总体标准方差 STDDEV_POP/样本标准方差 STDDEV_SAMP................................................................175 填充统计标准偏差 STDEVP (SQL Server Only) ...........................................................................................175 合计值 SUM....................................................................................................................................................175 COMPUTE SUM (SQL Server) .......................................................................................................................177 方差 VARIANCE / VAR ..................................................................................................................................178 总体方差 VAR_POP/样本方差 VAR_SAMP ...............................................................................................179 填充的统计方差 VARP (SQL Server Only)...................................................................................................180 ROLLUP 与 CUBE 查询 ......................................................................................................................................181 ROLLUP-简单用法 .........................................................................................................................................181 ROLLUP-GROUPING.....................................................................................................................................183 ROLLUP-调整 NULL 显示.............................................................................................................................185 CUBE-简单用法 ..............................................................................................................................................187 CUBE-GROUPING..........................................................................................................................................189 附录..................................................................................................................................................................................191 SALE_REPORT 测试表 ..........................................................................................................................................191 SALE_REPORT 测试数据 ......................................................................................................................................191 南無阿弥陀佛 7 数据库系统自带函数 字符函数 由字符获取 ASCII 码 ASCII Oracle SQL> SELECT ASCII('D'), ASCII('Dave') FROM dual; ASCII('D') ASCII('DAVE') ---------- ------------- 68 68 SQL Server 1> SELECT ASCII('D'), ASCII('Dave') 2> go ----------- ----------- 68 68 (1 行受影响) MySQL mysql> SELECT ASCII('D'), ASCII('Dave'); +------------+---------------+ | ASCII('D') | ASCII('Dave') | +------------+---------------+ | 68 | 68 | +------------+---------------+ 1 row in set (0.00 sec) 格式化为 ASCII 字符串信息 ASCIISTR(Oracle Only) Oracle SQL> SELECT 2 ASCIISTR('Hello 你好!') AS A 3 FROM 4 dual; A --------------------- Hello \4F60\597D\FF01 SQL> 南無阿弥陀佛 8 由字符获取 UNICODE 码 SQL Server --按照 Unicode 标准的定义,返回输入表达式的第一个字符的整数值。 1> SELECT UNICODE(N'佛'), NCHAR(UNICODE(N'佛')) 2> go ----------- - 20315 佛 (1 行受影响) 返回字符集 CHARSET (MySQL Only) MySQL mysql> SELECT CHARSET ('oRacle SQL sERVER') A -> // +------+ | A | +------+ | utf8 | +------+ 1 row in set (0.00 sec) 由 ASCII 码获取字符 CHR / CHAR Oracle SQL> select CHR(68) from dual; CH -- D SQL Server 1> select CHAR(68) 2> go - D (1 行受影响) 南無阿弥陀佛 9 MySQL mysql> select CHAR(68); +----------+ | CHAR(68) | +----------+ | D | +----------+ 1 row in set (0.00 sec) mysql> SELECT CHAR(77,121,83,81,'76') S; +-------+ | S | +-------+ | MySQL | +-------+ 1 row in set (0.00 sec) 由 UNICODE 码获取字符 NCHAR SQL Server --按照 Unicode 标准的定义,返回输入表达式的第一个字符的整数值。 1> SELECT UNICODE(N'佛'), NCHAR(UNICODE(N'佛')) 2> go ----------- - 20315 佛 (1 行受影响) 字符串连接 CONCAT || / + Oracle SQL> SELECT 2 CONCAT('Good ', 'Morning!') A, 3 'Good ' || 'Morning!' B 4 FROM 5 dual; A B -------------------------- -------------------------- Good Morning! Good Morning! SQL Server 1> SELECT 'Good ' + 'Morning!' B 2> go 南無阿弥陀佛 10 B ------------- Good Morning! (1 行受影响) MySQL CONCAT_WS 第一个参数为分隔 符号 mysql> select CONCAT('Good ', 'Morning!') A -> // +---------------+ | A | +---------------+ | Good Morning! | +---------------+ 1 row in set (0.02 sec) mysql> SELECT CONCAT_WS(',', -> 'First name', -> 'Second name', -> 'Last Name') A; +----------------------------------+ | A | +----------------------------------+ | First name,Second name,Last Name | +----------------------------------+ 1 row in set (0.00 sec) 单词首字母大写 INITCAP (Oracle Only) Oracle SQL> SELECT INITCAP('oracle SQL server') A 2 FROM 3 dual; A ---------------------------------- Oracle Sql Server SQL Server 字符替换 INSERT (MySQL Only) MySQL INSERT(str,pos,len,newstr) 南無阿弥陀佛 11 返回字符串 str, 其子字符串起始于 pos 位置和长期被字符串 newstr 取代的 len 字符。 如果 pos 超过字符串长度,则返回值为原始字符串。 假如 len 的 长度大于其它字符串的长度,则从位置 pos 开始替换。若任何一个参数为 null, 则返回值为 NULL。 mysql> SELECT INSERT('ABCDEFG', 3, 4, 'What') A; +---------+ | A | +---------+ | ABWhatG | +---------+ 1 row in set (0.00 sec) mysql> SELECT INSERT('ABCDEFG', -1, 4, 'What') A; +---------+ | A | +---------+ | ABCDEFG | +---------+ 1 row in set (0.00 sec) mysql> SELECT INSERT('ABCDEFG', 3, 100, 'What') A; +--------+ | A | +--------+ | ABWhat | +--------+ 1 row in set (0.00 sec) 字符串搜索 INSTR / CHARINDEX / LOCATE Oracle 4 参数: 被检索字符串 检索的字符串 哪里开始(默认 1) 找第几个(默认 1) 从哪里开始为负数 的话,表示从后面 向前找。 SQL> SELECT 2 INSTR('easy come, easy go, so easy!', 'easy') A, 3 INSTR('easy come, easy go, so easy!', 'easy', 3) B, 4 INSTR('easy come, easy go, so easy!', 'easy', 3, 2) C, 5 INSTR('easy come, easy go, so easy!', 'easy', -1) D, 6 INSTR('easy come, easy go, so easy!', 'easy', -1, 2) E 7 FROM 8 dual; A B C D E ---------- ---------- ---------- ---------- ---------- 南無阿弥陀佛 12 1 12 24 24 12 SQL Server 3 参数 检索字符串 被检索字符串 哪里开始(默认 1) 从哪里开始为负数 的话 = 1 1> SELECT 2> CHARINDEX('easy', 'easy come, easy go, so easy!') A, 3> CHARINDEX('easy', 'easy come, easy go, so easy!', 3) B, 4> CHARINDEX('easy', 'easy come, easy go, so easy!', -1) C 5> go A B C ----------- ----------- ----------- 1 12 1 MySQL MySQL INSTR 只 支持从第一个字符 开始找第一个 LOCATE 支 持从 指定位置开始找 从哪里开始为负数 的话 = 找不到 mysql> SELECT -> INSTR('easy come, easy go, so easy!', 'easy') A -> // +---+ | A | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> SELECT -> LOCATE ('easy', 'easy come, easy go, so easy!', 2) A, -> LOCATE ('easy', 'easy come, easy go, so easy!', -1) B; +----+---+ | A | B | +----+---+ | 12 | 0 | +----+---+ 1 row in set (0.00 sec) 获取字符串左边开始指定个数的字符 LEFT Oracle 没有这个函数 但是可以通过 SUBSTR 实现 SQL> SELECT 2 SUBSTR('abcdefg', 1, 3) 3 FROM 4 dual; SUB --- abc 南無阿弥陀佛 13 SQL Server 1> SELECT LEFT('abcdefg', 3) 2> go ------ abc (1 行受影响) MySQL mysql> SELECT LEFT('abcdefg', 3) -> // +--------------------+ | LEFT('abcdefg', 3) | +--------------------+ | abc | +--------------------+ 1 row in set (0.00 sec) 字符串长度 LENGTH / LEN Oracle SQL> SELECT LENGTH('oracle SQL server') A 2 FROM 3 dual; A ---------- 17 SQL Server 1> SELECT LEN('oracle SQL server') A 2> go A ----------- 17 (1 行受影响) MySQL mysql> SELECT LENGTH('oracle SQL server') A -> // +----+ | A | +----+ | 17 | +----+ 1 row in set (0.00 sec) 南無阿弥陀佛 14 加载文件 LOAD_FILE (MySQL Only) MySQL LOAD_FILE(file_name) 读取文件并将这一文件按照字符串的格式返回。 文件的位置必须在服务器上, 你必须为文件制定路径全名,而且你还必须拥有 FILE 特许权。文件必须可读 取,文件容量必须小于 max_allowed_packet 字节。 若文件不存在,或因不满足上述条件而不能被读取, 则函数返回值为 NULL。 mysql> UPDATE tbl_name SET blob_column=LOAD_FILE('/tmp/picture') WHERE id=1; 字符转换为小写 LOWER / LCASE Oracle SQL> SELECT LOWER('oRacle SQL sERVER') A 2 FROM 3 dual; A ---------------------------------- oracle sql server SQL Server 1> SELECT LOWER('oRacle SQL sERVER') A 2> go A ---------------------------------- oracle sql server (1 行受影响) MySQL mysql> SELECT LCASE ('oRacle SQL sERVER') A -> // +-------------------+ | A | +-------------------+ | oracle sql server | +-------------------+ 1 row in set (0.00 sec) 南無阿弥陀佛 15 左边字符填充 LPAD Oracle 第 3 个参数不写, 默认为空格 SQL> SELECT LPAD('Ora', 8) A, 2 LPAD('Ora', 8, '*') B, 3 LPAD('Ora', 8, 'xyz') C 4 FROM 5 dual; A B C ---------------- ---------------- ---------------- Ora *****Ora xyzxyOra SQL Server 没有这个函数 但是 单个字符的,可以通过 REPLICATE 与 RIGHT 的组合来实现 多个字符的,处理起来,略为复杂。 1> SELECT 2> RIGHT( REPLICATE(' ', 8) + 'Ora', 8) AS A, 3> RIGHT( REPLICATE('*', 8) + 'Ora', 8) AS B, 4> LEFT( REPLICATE('xyz', 8), 8 - Len('Ora')) 5> + 'Ora' AS C 6> go A B C ----------- ----------- ------------- Ora *****Ora xyzxyOra (1 行受影响) 1> CREATE FUNCTION LPAD( 2> @BaseStr VARCHAR(10), 3> @Size INT, 4> @PadChar VARCHAR(10) = ' ' 5> ) 6> RETURNS VARCHAR(100) 7> AS 8> BEGIN 9> DECLARE @returnVal VARCHAR(100); 10> IF LEN(@PadChar) = 0 11> SET @returnVal = 12> RIGHT(REPLICATE(' ', @Size) + @BaseStr, @Size); 13> ELSE IF LEN(@PadChar) = 1 14> SET @returnVal = 15> RIGHT(REPLICATE(@PadChar, @Size) + @BaseStr, @Size); 16> ELSE 南無阿弥陀佛 16 17> SET @returnVal = 18> LEFT( 19> REPLICATE(@PadChar, @Size / LEN(@PadChar)), 20> @Size - LEN(@BaseStr)) + @BaseStr; 21> RETURN @returnVal; 22> END 23> go 1> 2> SELECT 3> dbo.LPAD('Ora', 8, ' ') A, 4> dbo.LPAD('Ora', 8, '*') B, 5> dbo.LPAD('Ora', 8, 'xyz') C 6> go A B C Ora *****Ora xyzxyOra MySQL 必须写满 3 个参数 mysql> SELECT -> LPAD('Ora', 8, '*') B, -> LPAD('Ora', 8, 'xyz') C -> // +----------+----------+ | B | C | +----------+----------+ | *****Ora | xyzxyOra | +----------+----------+ 1 row in set (0.00 sec) 去除左边空格与字符 LTRIM Oracle 可以去除空格 或者指定字符 或者指定字符集合 SQL> SELECT LTRIM(' Ora ') A, 2 LTRIM('***Ora***', '*') B, 3 LTRIM('zyxOraxyz', 'xyz') C 4 FROM 5 dual; A B C ------------ ------------ ------------ Ora Ora*** Oraxyz SQL Server 仅仅能去除空格 1> SELECT LTRIM(' SQL ') A 2> go A --------- SQL 南無阿弥陀佛 17 (1 行受影响) MySQL mysql> SELECT LTRIM(' SQL ') A // +--------+ | A | +--------+ | SQL | +--------+ 1 row in set (0.00 sec) MAKE_SET(MySQL Only) MySQL MAKE_SET(bits,str1,str2,...) 返回一个设定值 (一个包含被¡,¡ 号分开的字字符串的字符串) ,由在 bits 组 中具有相应的比特的字符串组成。str1 对应比特 0, str2 对应比特 1,以此类推。 str1, str2, ...中的 NULL 值不会被添加到结果中。 mysql> SELECT MAKE_SET(1,'a','b','c'); -> 'a' mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world'); -> 'hello,world' mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world'); -> 'hello' mysql> SELECT MAKE_SET(0,'a','b','c'); -> '' 检索指定模式首次出现位置 PATINDEX (SQL Server Only) Oracle SQL Server 第一个参数必须以 %开头与结尾 1> SELECT 2> PATINDEX('%come%go%', 'easy come, easy go, so easy!') A 3> go A ----------- 6 (1 行受影响) 可以通过 PATINDEX(¡%[^a-zA-Z]%¡, 指定字符串) = 0 的方式,来创建 Check 南無阿弥陀佛 18 约束,限制指定字段,只允许存储英文字母。 正则表达式检索 REGEXP_LIKE ( Oracle Only ) Oracle 3 个参数 第一个是输入的字 符串 第二个是正则表达 式 第 三 个 是 取 值 范 围: i:大小写不敏感; c:大小写敏感; n:点号 . 不匹配 换行符号; m:多行模式; x:扩展模式,忽略 正则表达式中的空 白字符。 全部测试数据 SQL> SELECT * FROM test_reg_like; A ---------------------------------------- ABC A12 12a12 匹配字母 A 的 SQL> SELECT 2 * 3 FROM 4 test_reg_like 5 WHERE 6 REGEXP_LIKE(a, 'A'); A ---------------------------------------- ABC A12 匹配字母 A 的 (大小写不敏感) SQL> SELECT 2 * 3 FROM 4 test_reg_like 5 WHERE 6 REGEXP_LIKE(a, 'A', 'i'); A ---------------------------------------- ABC A12 12a12 匹配字母 A 后面跟 1 个或多个数字的 (大小写不敏感) SQL> SELECT 2 * 3 FROM 4 test_reg_like 南無阿弥陀佛 19 5 WHERE 6 REGEXP_LIKE(a, 'A\d+', 'i'); A ---------------------------------------- A12 12a12 匹配字母 A 开头,数字结尾的 SQL> SELECT 2 * 3 FROM 4 test_reg_like 5 WHERE 6 REGEXP_LIKE(a, '^A.+\d$'); A ---------------------------------------- A12 正则表达式字符截取 REGEXP_SUBSTR ( Oracle Only ) Oracle 5 个参数 第一个是输入的字 符串 第二个是正则表达 式 第三个是标识从第 几个字符开始正则 表达式匹配。(默认 为 1) 第四个是标识第几 个匹配组。(默认为 1) 第五个是是取值范 围: i:大小写不敏感; c:大小写敏感; n:点号 . 不匹配 换行符号; m:多行模式; x:扩展模式,忽略 正则表达式中的空 全部测试数据 SQL> select * from test_reg_substr; A ----------------------------------- ABC123XYZ ABC123XYZ456 Edward 检索中间的数字 SQL> SELECT 2 REGEXP_SUBSTR(a,'[0-9]+') 3 FROM 4 test_reg_substr 5 WHERE 6 REGEXP_LIKE(a, '[0-9]+'); REGEXP_SUBSTR(A,'[0-9]+') --------------------------------- 123 123 检索中间的数字(从第一个字母开始匹配,找第 2 个匹配项目) SQL> SELECT 2 NVL(REGEXP_SUBSTR(a,'[0-9]+', 1, 2), '-') AS a 南無阿弥陀佛 20 白字符。 3 FROM 4 test_reg_substr 5 WHERE 6 REGEXP_LIKE(a, '[0-9]+'); A ------------------------------------------------------ - 456 取得¡ 字符集合¡ SQL> SELECT 2 REGEXP_SUBSTR(a, '\w+') 3 FROM 4 test_reg_substr 5 WHERE 6 REGEXP_LIKE(a, '\w+'); REGEXP_SUBSTR(A,'\W+') ------------------------------- ABC123XYZ ABC123XYZ456 Name 取得¡ 字符集合¡ (从第一个字母开始匹配,找第 2 个匹配项目) SQL> SELECT 2 NVL(REGEXP_SUBSTR(a, '\w+', 1, 2), '-') AS a 3 FROM 4 test_reg_substr 5 WHERE 6 REGEXP_LIKE(a, '\w+'); A --------------------------------------------------- - - Edward 正则表达式字符位置检索 REGEXP_INSTR ( Oracle Only ) Oracle 6 个参数 第一个是输入的字 全部测试数据 SQL> select * from test_reg_substr; A ----------------------------------- 南無阿弥陀佛 21 符串 第二个是正则表达 式 第三个是标识从第 几个字符开始正则 表达式匹配。(默认 为 1) 第四个是标识第几 个匹配组。(默认为 1) 第五个是指定返回 值的类型,如果该 参数为 0,则返回 值为匹配位置的第 一个字符,如果该 值为非 0 则返回匹 配值的最后一个位 置。 第六个是是取值范 围: i:大小写不敏感; c:大小写敏感; n:点号 . 不匹配 换行符号; m:多行模式; x:扩展模式,忽略 正则表达式中的空 白字符。 ABC123XYZ ABC123XYZ456 Edward 找数字 SQL> SELECT 2 REGEXP_INSTR (a,'[0-9]+') AS A 3 FROM 4 test_reg_substr; A ---------- 4 4 0 找数字(从第一个字母开始匹配,找第 1 个匹配项目 的最后一个字符的位置) SQL> SELECT 2 REGEXP_INSTR (a,'[0-9]+', 1, 1, 1) AS A 3 FROM 4 test_reg_substr; A ---------- 7 7 0 找数字(从第一个字母开始匹配,找第 2 个匹配项目) SQL> SELECT 2 REGEXP_INSTR (a,'[0-9]+', 1, 2) AS A 3 FROM 4 test_reg_substr; A ---------- 0 10 0 找数字(从第一个字母开始匹配,找第 2 个匹配项目 的最后一个字符的位置) SQL> SELECT 2 REGEXP_INSTR (a,'[0-9]+', 1, 2, 1) AS A 3 FROM 4 test_reg_substr; A ---------- 0 13 南無阿弥陀佛 22 0 正则表达式字符替换 REGEXP_REPLACE ( Oracle Only ) Oracle 6 个参数 第一个是输入的字 符串 第二个是正则表达 式 第三个是替换的字 符 第四个是标识从第 几个字符开始正则 表达式匹配。(默认 为 1) 第五个是标识第几 个匹配组。(默认为 全部都替换掉) 第六个是是取值范 围: i:大小写不敏感; c:大小写敏感; n:点号 . 不匹配 换行符号; m:多行模式; x:扩展模式,忽略 正则表达式中的空 白字符。 全部测试数据 SQL> select * from test_reg_substr; A ----------------------------------- ABC123XYZ ABC123XYZ456 Edward 替换数字 SQL> SELECT 2 REGEXP_REPLACE (a,'[0-9]+', 'QQQ') AS A 3 FROM 4 test_reg_substr; A ----------------------------------------------- ABCQQQXYZ ABCQQQXYZQQQ Edward 替换数字(从第一个字母开始匹配,替换第 1 个匹配项目) SQL> SELECT 2 REGEXP_REPLACE (a,'[0-9]+', 'QQQ', 1, 1) AS A 3 FROM 4 test_reg_substr; A ------------------------------------------------------ ABCQQQXYZ ABCQQQXYZ456 Edward 替换数字(从第一个字母开始匹配,替换第 2 个匹配项目) SQL> SELECT 2 REGEXP_REPLACE (a,'[0-9]+', 'Q', 1, 2) AS A 3 FROM 4 test_reg_substr; A --------------------------------------------------- 南無阿弥陀佛 23 ABC123XYZ ABC123XYZQ Edward 替换第二个单词 SQL> SELECT 2 REGEXP_REPLACE (a,'\w+', 'Kimi', 1, 2) AS A 3 FROM 4 test_reg_substr; A --------------------------------------------------- ABC123XYZ ABC123XYZ456 Kimi 字符替换 REPLACE Oracle 3 参数: 源字符串 检索字符串 替换字符串 参数 3 默认为¡¡ SQL> SELECT 2 REPLACE('easy come, easy go, so easy!', 'easy') A, 3 REPLACE('easy come, easy go, so easy!', 'easy', 'not easy') B 4 FROM 5 dual; A -------------------------------- B -------------------------------------------------------------------------------- come, go, so ! not easy come, not easy go, so not easy! SQL Server 1> SELECT REPLACE('easy come, easy go!', 'easy', '!') 2> go ! come, ! go! MySQL mysql> SELECT REPLACE('easy come, easy go!', 'easy', '!') -> // +---------------------------------------------+ | REPLACE('easy come, easy go!', 'easy', '!') | +---------------------------------------------+ | ! come, ! go! | +---------------------------------------------+ 1 row in set (0.00 sec) 南無阿弥陀佛 24 增加标示符定义 QUOTENAME/QUOTE Oracle SQL Server 1> 2> SELECT QUOTENAME('数据库[]表字段') 3> go -------------------------------------- [数据库[]]表字段] (1 行受影响) MySQL QUOTE(str) 引证一个字符串,由此产生一个在 SQL 语句中可用作完全转义数据值的结果。 返回的字符串由单引号标注,每例都带有单引号 (¡'¡)、 反斜线符号 (¡\¡)、 ASCII NUL 以及前面有反斜线符号的 Control-Z 。如果自变量的值为 NULL, 则 返回不带单引号的单词 ¡NULL¡。 mysql> SELECT QUOTE('Don\'t!'); -> 'Don\'t!' mysql> SELECT QUOTE(NULL); -> NULL 重复字符串 REPLICATE / REPEAT Oracle 没有这个函数 单个字符可用 LPAD / RPAD 替代处理 多个字符处理起来,略为复杂 SQL> SELECT 2 TRIM( LPAD(' ', 11, 'A') ) AS A, 3 TRIM( RPAD(' ', 11, 'B') ) AS B, 4 SUBSTR( 5 TRIM( 6 LPAD(' ', 11 * LENGTH('Sql'), 'Sql')), 7 1, 10 * LENGTH('Sql')) AS C 8 FROM 9 dual; A B C ---------- ---------- ------------------------------ AAAAAAAAAA BBBBBBBBBB SqlSqlSqlSqlSqlSqlSqlSqlSqlSql 南無阿弥陀佛 25 SQL Server 1> SELECT REPLICATE ('Sql', 10) A 2> go A ------------------------------ SqlSqlSqlSqlSqlSqlSqlSqlSqlSql (1 行受影响) MySQL REPEAT(str,count) 返回一个由重复的字符串 str 组成的字符串,字符串 str 的数目等于 count 。 若 count <= 0,则返回一个空字符串。若 str 或 count 为 NULL,则返回 NULL 。 mysql> SELECT REPEAT('MySQL', 3); -> 'MySQLMySQLMySQL' mysql> SELECT REPEAT ('ABC', 3) // +-------------------+ | REPEAT ('ABC', 3) | +-------------------+ | ABCABCABC | +-------------------+ 1 row in set (0.00 sec) 字符反转 REVERSE Oracle 没有这个函数 SQL Server 1> SELECT REVERSE('Sql Server .vs. Oracle' ) A 2> go A ---------------------- elcarO .sv. revreS lqS (1 行受影响) MySQL REVERSE(str) 返回字符串 str ,顺序和字符顺序相反。 mysql> SELECT REVERSE('abc'); -> 'cba' 南無阿弥陀佛 26 获取字符串右边开始指定个数的字符 RIGHT Oracle 没有这个函数 但是可以通过 SUBSTR 实现 SQL> SELECT 2 SUBSTR('abcdefg', LENGTH('abcdefg') + 1 - 3) 3 FROM 4 dual; SUB --- efg SQL Server 1> SELECT RIGHT('abcdefg', 3) 2> go ------ efg (1 行受影响) MySQL mysql> SELECT RIGHT('abcdefg', 3) // +---------------------+ | RIGHT('abcdefg', 3) | +---------------------+ | efg | +---------------------+ 1 row in set (0.00 sec) 右边字符填充 RPAD Oracle SQL> SELECT RPAD('Ora', 8) A, 2 RPAD('Ora', 8, '*') B, 3 RPAD('Ora', 8, 'xyz') C 4 FROM 5 dual; A B C ---------------- ---------------- ---------------- Ora Ora***** Oraxyzxy SQL Server 没有这个函数 但是 单个字符的,可以通过 REPLICATE 与 LEFT 的组合来实现 南無阿弥陀佛 27 多个字符的,处理起来,略为复杂。 1> SELECT 2> LEFT('Ora' + REPLICATE(' ', 8), 8) AS A, 3> LEFT('Ora' + REPLICATE('*', 8), 8) AS B, 4> 'Ora' 5> + LEFT(REPLICATE('xyz', 8), 8 - Len('Ora')) 6> AS C 7> go A B C ----------- ----------- ------------- Ora Ora***** Oraxyzxy (1 行受影响) MySQL mysql> SELECT -> RPAD('Ora', 8, '*') B, -> RPAD('Ora', 8, 'xyz') C -> // +----------+----------+ | B | C | +----------+----------+ | Ora***** | Oraxyzxy | +----------+----------+ 1 row in set (0.00 sec) 去除右边空格与字符 RTRIM Oracle 可以去除空格 或者指定字符 或者指定字符集合 SQL> SELECT RTRIM(' Ora ') A, 2 RTRIM('***Ora***', '*') B, 3 RTRIM('zyxOraxyz', 'xyz') C 4 FROM 5 dual; A B C ------------ ------------ ------------ Ora ***Ora zyxOra SQL Server 仅仅能去除空格 1> SELECT RTRIM(' SQL ') A 2> go A --------- SQL 南無阿弥陀佛 28 (1 行受影响) MySQL mysql> SELECT RTRIM(' SQL ') A // +--------+ | A | +--------+ | SQL | +--------+ 1 row in set (0.00 sec) 取得发音 SOUNDEX Oracle SQL> SELECT 2 SOUNDEX('Hello') A 3 FROM 4 dual; A -------- H400 SQL Server 1> SELECT 2> SOUNDEX('Hello') A 3> go A ----- H120 MySQL SOUNDEX(str) 从 str 返回一个 soundex 字符串。 两个具有几乎同样探测的字符串应该具有同 样的 soundex 字符串。一个标准的 soundex 字符串的长度为 4 个字符,然而 SOUNDEX() 函数会返回一个人以长度的字符串 。 可 使 用 结 果 中 的 SUBSTRING() 来得到一个标准 soundex 字符串。在 str 中,会忽略所有未按照 字母顺序排列的字符。 所有不在 A-Z 范围之内的国际字母符号被视为元音字 母。 mysql> SELECT SOUNDEX('Hello'); -> 'H400' mysql> SELECT SOUNDEX('Quadratically'); -> 'Q36324' 注意:这个函数执行原始的 Soundex 算法,而非更加流行的加强版本(如 D. Knuth 所述)。其区别在于原始版本首先会删去元音,其次是重复,而加强版则首先删 去重复,而后删去元音。 expr1 SOUNDS LIKE expr2 这相当于 SOUNDEX(expr1) = SOUNDEX(expr2)。 南無阿弥陀佛 29 取得发音的不同 DIFFERENCE SQL Server DIFFERENCE ( character_expression , character_expression ) 参数 character_expression 类型为 char 或 varchar 的表达式。character_expression 也可以是 text 类型, 但只有前 8,000 个字节有效。 返回类型 int 备注 返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等:0 表示几乎不同或完全不同,4 表示几乎相同或完全相同。 返回由重复的空格组成的字符串 SPACE Oracle 没有这个函数 但是可以通过 LPAD 或者 RPAD 来实现 SQL> SELECT 2 LPAD(' ', 10) || 'A' AS A, 3 RPAD(' ', 10) || 'B' AS B 4 FROM 5 dual; A B ----------- ----------- A B SQL Server 1> SELECT SPACE(10) + 'A' 2> go ----------- A (1 行受影响) 南無阿弥陀佛 30 MySQL mysql> SELECT SPACE(10) A // +------------+ | A | +------------+ | | +------------+ 1 row in set (0.00 sec) mysql> SELECT SPACE(40) A // +------------------------------------------+ | A | +------------------------------------------+ | | +------------------------------------------+ 1 row in set (0.00 sec) 小数转换为字符串 STR (SQL Server Only) Oracle SQL Server 第一个参数为数字 第二个参数为精度 第三个参数为小数 1> SELECT 2> STR(123.45) A, 3> STR(123.45, 8) B, 4> STR(123.45, 8, 1) C 5> go A B C ---------- -------- -------- 123 123 123.5 (1 行受影响) 逐字符比较字符串大小 STRCMP (MySQL Only) MySQL mysql> SELECT -> STRCMP ('MySQL', 'Mysql') A -> // +---+ | A | +---+ | 0 | +---+ 南無阿弥陀佛 31 1 row in set (0.00 sec) mysql> SELECT -> STRCMP ('ABCDE', 'ABCDEF') A -> // +----+ | A | +----+ | -1 | +----+ 1 row in set (0.00 sec) mysql> SELECT -> STRCMP ('ABCDE', 'ABCD') A -> -> // +---+ | A | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> SELECT -> STRCMP ('ABC', 'ABB') A // +---+ | A | +---+ | 1 | +---+ 1 row in set (0.00 sec) 删除指定长度的字符并在指定的起始点插入另一组字符 STUFF(SQL Server Only) Oracle 没有这个函数 但是可以通过 SUBSTR 实现 SQL> CREATE OR REPLACE FUNCTION STUFF( 2 v_BaseStr VARCHAR2, 3 v_StartIndex INT, 4 v_Length INT, 南無阿弥陀佛 32 5 v_ReplaceStr VARCHAR2 6 )RETURN VARCHAR2 IS 7 BEGIN 8 RETURN 9 SUBSTR(v_BaseStr, 1, v_StartIndex - 1) || 10 v_ReplaceStr || 11 SUBSTR(v_BaseStr, v_StartIndex + v_Length); 12 END; 13 / 函数已创建。 SQL> SELECT 2 STUFF (',1,2,3', 1, 1, '') A, 3 STUFF(';A;B;C', 1, 3, 'Z;') B, 4 STUFF('X;Y;Z', 3, 1, 'C') C 5 FROM 6 dual; A ----------------------------------------------------- B ----------------------------------------------------- C ----------------------------------------------------- 1,2,3 Z;B;C X;C;Z SQL Server 1> SELECT 2> STUFF(',1,2,3', 1, 1, '') A, 3> STUFF(';A;B;C', 1, 3, 'Z;') B, 4> STUFF('X;Y;Z', 3, 1, 'C') C 5> go A B C ------ ----- ------ 1,2,3 Z;B;C X;C;Z (1 行受影响) MySQL 参考 MySQL 的 INSERT(str,pos,len,newstr) 函数。 南無阿弥陀佛 33 返回字符串一部分 SUBSTR / SUBSTRING Oracle 如果第 3 个参数没 有,默认到底 SQL> SELECT SUBSTR('abcdefg', 2, 3) FROM dual; SUBSTR ------ bcd SQL Server 1> SELECT SUBSTRING('abcdefg', 2, 3) 2> go ------ bcd (1 行受影响) MySQL SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不带有 len 参数的格式从字符串 str 返回一个子字符串,起始于位置 pos。带有 len 参数的格式从字符串 str 返回一个长度同 len 字符相同的子字符串,起始于 位置 pos。 使用 FROM 的格式为标准 SQL 语法。也可能对 pos 使用一个负 值。假若这样,则子字符串的位置起始于字符串结尾的 pos 字符,而不是字符 串的开头位置。在以下格式的函数中可以对 pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' SUBSTRING_INDEX (MySQL Only) MySQL SUBSTRING_INDEX(str,delim,count) 在定界符 delim 以及 count 出现前,从字符串 str 返回自字符串。若 count 为正 值,则返回最终定界符(从左边开始)左边的一切内容。若 count 为负值,则返回 定界符(从右边开始)右边的一切内容。 mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -> 'www.mysql' 南無阿弥陀佛 34 mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -> 'mysql.com' TRANSLATE Oracle 语 法 : TRANSLATE(char, from, to) 用法:返回将出现 在 from 中的每个 字符替换为to中的 相应字符以后的字 符串。 若 from 比 to 字符 串长,那么在 from 中比to中多出的字 符将会被删除。 三个参数中有一个 是空,返回值也将 是空值。 SQL> SELECT TRANSLATE('2KRW229', 2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 3 '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') AS "License" 4 FROM DUAL; License ------- 9XXX999 某些情况下,要从一个字符串中,提取数字信息的,可以使用: SQL> SELECT TRANSLATE('2KRW229', 2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') 3 AS "Translate example" 4 FROM DUAL; Tran ---- 2229 更简单的写法,就是第一个字母是不存在的字母,后面全部是需要被删除的。 SQL> SELECT TRANSLATE('2KRW229', 2 '#ABCDEFGHIJKLMNOPQRSTUVWXYZ', '#') 3 AS "Translate example" 4 FROM DUAL; Tran ---- 2229 还有一种写法,就是把需要保留的都标记下来,其他的都替换为空白 SQL> SELECT TRANSLATE('2KRW229', 2 '1234567890' || '2KRW229', '1234567890') 3 AS "Translate example" 4 FROM DUAL; Tran 南無阿弥陀佛 35 ---- 2229 SQL> SELECT 2 TRANSLATE( 3 '妄想执著如来智慧德相妄想执著', 4 '如来智慧德相妄想执著', 5 '如来智慧德相') A, 6 TRANSLATE( 7 '妄想执著如来智慧德相妄想执著', 8 '#妄想执著', 9 '#') B, 10 TRANSLATE( 11 '妄想执著如来智慧德相妄想执著', 12 '如来智慧德相' || '妄想执著如来智慧德相妄想执著', 13 '如来智慧德相') C 14 FROM 15 dual; A B C ------------ ------------ ------------ 如来智慧德相 如来智慧德相 如来智慧德相 SQL Server CREATE FUNCTION TRANSLATE( @string VARCHAR(MAX), @from_str VARCHAR(MAX), @to_str VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN -- 返回将(所有出现的)from_str中的每个字符替换为to_str中的相应字符 以后的string。 -- TRANSLATE 是REPLACE 所提供的功能的一个超集。 -- 如果from_str 比to_str 长,那么在from_str 中而不在to_str 中的 额外字符将从string 中被删除,因为它们没有相应的替换字符。 -- to_str 不能为空。 -- Oracle 将空字符串解释为NULL,并且如果TRANSLATE 中的任何参数为 NULL,那么结果也是NULL。 IF @string IS NULL OR @from_str IS NULL OR @to_str IS NULL BEGIN RETURN NULL; END; 南無阿弥陀佛 36 -- 源长度与目标长度 DECLARE @FromLen INT, @ToLen INT; SET @FromLen = LEN(@from_str); SET @ToLen = LEN(@to_str); -- 准备用于返回的数值. DECLARE @resultVal VARCHAR(MAX); SET @resultVal = @string; -- 用于存储本次需要替换的字符信息. DECLARE @thisTimeReplace CHAR(1); -- 从后向前依次替换. WHILE @FromLen > 0 BEGIN -- 取得本次即将要替换的字符. SET @thisTimeReplace = SUBSTRING(@from_str, @FromLen, 1); IF CHARINDEX(@thisTimeReplace, @from_str) < @FromLen BEGIN -- 假如当前这个要替换的字符,在前面还有,那么这里就不替换了 -- 原因,为了支持 -- SELECT TRANSLATE('2KRW229', '1234567890' || '2KRW229', '1234567890') -- 这样的效果. -- 向前处理上一个. SET @FromLen = @FromLen - 1; CONTINUE; END IF @FromLen > @ToLen BEGIN -- from_str 比to_str 长,那么在from_str 中而不在to_str 中的 额外字符将从string 中被删除,因为它们没有相应的替换字符。 SET @resultVal = REPLACE(@resultVal, SUBSTRING(@from_str, @FromLen, 1), ''); END ELSE BEGIN -- from_str中的每个字符替换为to_str中的相应字符以后的string SET @resultVal = REPLACE(@resultVal, @thisTimeReplace, SUBSTRING(@to_str, @FromLen, 1)); END; 南無阿弥陀佛 37 -- 处理完当前字符后,向前处理上一个. SET @FromLen = @FromLen - 1; END; -- 依次处理完毕后,返回结果. RETURN @resultVal; END go 1> SELECT dbo.TRANSLATE('2KRW229', 2> '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 3> '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') AS "License" 4> go License ---------------- 9XXX999 (1 行受影响) 1> 2> SELECT dbo.TRANSLATE('2KRW229', 3> '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') 4> AS "Translate example" 5> go Translate example ---------------- 2229 (1 行受影响) 1> 2> 3> SELECT dbo.TRANSLATE('2KRW229', 4> '#ABCDEFGHIJKLMNOPQRSTUVWXYZ', '#') 5> AS "Translate example" 6> go Translate example ---------------- 2229 (1 行受影响) 1> 2> SELECT dbo.TRANSLATE('2KRW229', 3> '1234567890' + '2KRW229', '1234567890') 4> AS "Translate example" 南無阿弥陀佛 38 5> go Translate example ---------------- 2229 (1 行受影响) 删除前后空格或其它字符 TRIM Oracle SQL> SELECT TRIM(' Ora ') A, 2 TRIM(LEADING '*' FROM '***Ora***') B, 3 TRIM(TRAILING '*' FROM '***Ora***') C, 4 TRIM(BOTH '*' FROM '***Ora***') D 5 FROM 6 dual; A B C D ------ ------------ ------------ ------ Ora Ora*** ***Ora Ora SQL Server 不支持 MySQL mysql> SELECT TRIM(' Ora ') A, -> TRIM(LEADING '*' FROM '***Ora***') B, -> TRIM(TRAILING '*' FROM '***Ora***') C, -> TRIM(BOTH '*' FROM '***Ora***') D -> // +------+--------+--------+------+ | A | B | C | D | +------+--------+--------+------+ | Ora | Ora*** | ***Ora | Ora | +------+--------+--------+------+ 1 row in set (0.00 sec) 转换为大写 UPPER Oracle SQL> SELECT 2 UPPER('oRacle SQL sERVER') A 3 FROM 4 dual; 南無阿弥陀佛 39 A ---------------------------------- ORACLE SQL SERVER SQL Server 1> SELECT UPPER('oRacle SQL sERVER') A 2> go A ---------------------------------- ORACLE SQL SERVER (1 行受影响) MySQL mysql> SELECT UCASE ('oRacle SQL sERVER') A // +-------------------+ | A | +-------------------+ | ORACLE SQL SERVER | +-------------------+ 1 row in set (0.00 sec) 数学函数 绝对值 ABS Oracle SQL> SELECT ABS(-7), ABS(7) FROM DUAL; ABS(-7) ABS(7) ---------- ---------- 7 7 SQL Server 1> SELECT ABS(-7), ABS(7) 2> go ----------- ----------- 7 7 (1 行受影响) MySQL mysql> SELECT ABS(-7), ABS(7) -> // +---------+--------+ | ABS(-7) | ABS(7) | +---------+--------+ 南無阿弥陀佛 40 | 7 | 7 | +---------+--------+ 1 row in set (0.00 sec) 反余弦 ACOS Oracle SQL> SELECT ACOS(-1), ACOS(0.5) FROM DUAL; ACOS(-1) ACOS(0.5) ---------- ---------- 3.14159265 1.04719755 SQL Server 1> SELECT ACOS(-1), ACOS(0.5) 2> go ------------------------ ------------------------ 3.1415926535897931 1.0471975511965979 MySQL ACOS(X) 返回 X 反余弦, 即, 余弦是 X 的值。若 X 不在-1 到 1 的范围之内,则返回 NULL 。 mysql> SELECT ACOS(1); -> 0 mysql> SELECT ACOS(1.0001); -> NULL mysql> SELECT ACOS(0); -> 1.5707963267949 反正弦 ASIN Oracle SQL> SELECT ASIN(1), ASIN(0.5) FROM DUAL; ASIN(1) ASIN(0.5) ---------- ---------- 1.57079633 .523598776 SQL Server 1> SELECT ASIN(1), ASIN(0.5) 2> go ------------------------ ------------------------ 1.5707963267948966 0.52359877559829893 (1 行受影响) MySQL mysql> SELECT ASIN(1) A, ASIN(0.5) B; +-----------------+-------------------+ 南無阿弥陀佛 41 | A | B | +-----------------+-------------------+ | 1.5707963267949 | 0.523598775598299 | +-----------------+-------------------+ 1 row in set (0.03 sec) 反正切 ATAN Oracle SQL> SELECT ATAN(0), ATAN(0.5) FROM DUAL; ATAN(0) ATAN(0.5) ---------- ---------- 0 .463647609 SQL Server 1> SELECT ATAN(0), ATAN(0.5) 2> go ------------------------ ------------------------ 0 0.46364760900080609 (1 行受影响) MySQL ATAN(X) 返回 X 的反正切,即,正切为 X 的值。 mysql> SELECT ATAN(2); -> 1.1071487177941 mysql> SELECT ATAN(-2); -> -1.1071487177941 反正切 ATAN2 / ATN2 Oracle SQL> SELECT ATAN2(1,2) FROM DUAL; ATAN2(1,2) ---------- .463647609 SQL Server 1> SELECT ATN2(1,2) 2> go ------------------------ 0.46364760900080609 (1 行受影响) MySQL ATAN(Y,X) , ATAN2(Y,X) 南無阿弥陀佛 42 返回两个变量 X 及 Y 的反正切。 它类似于 Y 或 X 的反正切计算, 除非两个 参数的符号均用于确定结果所在象限。 mysql> SELECT ATAN(-2,2); -> -0.78539816339745 mysql> SELECT ATAN2(PI(),0); -> 1.5707963267949 十进制转二进制 BIN (MySQL Only) MySQL mysql> SELECT BIN (255) A // +----------+ | A | +----------+ | 11111111 | +----------+ 1 row in set (0.00 sec) 二进制转十进制 BIN_TO_NUM (Oracle Only) Oracle SQL> SELECT 2 BIN_TO_NUM(0,0,0,0,1) AS A, 3 BIN_TO_NUM(0,0,0,1,0) AS B, 4 BIN_TO_NUM(0,0,1,0,0) AS C, 5 BIN_TO_NUM(0,1,0,0,0) AS D, 6 BIN_TO_NUM(1,0,0,0,0) AS E, 7 BIN_TO_NUM(1,0,1,0,1) AS F 8 FROM DUAL; A B C D E F ---------- ---------- ---------- ---------- ---------- ---------- 1 2 4 8 16 21 此方法对于 某些枚举类型的计算,计算起来很方便。 参数的数量是可变的 南無阿弥陀佛 43 按位与操作 BITAND (Oracle Only) Oracle SQL> SELECT 2 BITAND(3, 1) AS A, 3 BITAND(15, 7) AS B, 4 BITAND(255, 127) AS C, 5 BITAND(63, 0) AS D 6 FROM DUAL; A B C D ---------- ---------- ---------- ---------- 1 7 127 0 向上取整 CEIL / CEILING Oracle SQL> SELECT CEIL(18.1), CEIL(-18.1) FROM DUAL; CEIL(18.1) CEIL(-18.1) ---------- ----------- 19 -18 SQL Server 1> SELECT CEILING(18.1), CEILING(-18.1) 2> go ----- ----- 19 -18 (1 行受影响) MySQL mysql> SELECT CEILING(18.1), CEILING(-18.1) // +---------------+----------------+ | CEILING(18.1) | CEILING(-18.1) | +---------------+----------------+ | 19 | -18 | +---------------+----------------+ 1 row in set (0.03 sec) 进制转换 CONV (MySQL Only) MySQL mysql> SELECT CONV (255, 10, 2) A -> // 南無阿弥陀佛 44 +----------+ | A | +----------+ | 11111111 | +----------+ 1 row in set (0.00 sec) mysql> SELECT CONV (255, 10, 8) A -> // +------+ | A | +------+ | 377 | +------+ 1 row in set (0.00 sec) mysql> SELECT CONV (255, 10, 16) A -> // +------+ | A | +------+ | FF | +------+ 1 row in set (0.00 sec) 余弦 COS Oracle SQL> SELECT COS(0), COS(90 * 3.14159265) FROM DUAL; COS(0) COS(90*3.14159265) ---------- ------------------ 1 1 SQL Server 1> SELECT COS(0), COS(90 * 3.14159265) 2> go ------------------------ ------------------------ 1 0.99999999999994782 (1 行受影响) MySQL COS(X) 返回 X 的余弦,其中 X 在弧度上已知。 mysql> SELECT COS(PI()); 南無阿弥陀佛 45 -> -1 双曲余弦 COSH (Oracle Only) Oracle SQL> SELECT COSH(0), COSH(90 * 3.14159265/180) FROM DUAL; COSH(0) COSH(90*3.14159265/180) ---------- ----------------------- 1 2.50917847 SQL Server 三角余切 COT (SQL Server Only) Oracle SQL Server 1> SELECT COT(1) 2> go ------------------------ 0.64209261593433076 MySQL COT(X) 返回 X 的余切。 mysql> SELECT COT(12); -> -1.5726734063977 mysql> SELECT COT(0); -> NULL CRC32 (MySQL Only) MySQL CRC32(expr) 计算循环冗余码校验值并返回一个 32 比特无符号值。若参数为 NULL ,则结 果为 NULL。该参数应为一个字符串,而且在不是字符串的情况下会被作为字 符串处理(若有可能)。 mysql> SELECT CRC32('MySQL'); -> 3259397556 mysql> SELECT CRC32('mysql'); -> 2501908538 南無阿弥陀佛 46 度数为单位的角度 DEGREES Oracle SQL Server 1> SELECT DEGREES(3.14159265), DEGREES(PI()/2) 2> go ---------------------------------------- ------------------------ 179.999999794320020000 90 MySQL DEGREES(X) 返回参数 X, 该参数由弧度被转化为度。 mysql> SELECT DEGREES(PI()); -> 180 mysql> SELECT DEGREES(PI() / 2); -> 90 指数值 EXP Oracle SQL> SELECT EXP(1), EXP(2.7) FROM DUAL; EXP(1) EXP(2.7) ---------- ---------- 2.71828183 14.8797317 SQL Server 1> SELECT EXP(1), EXP(2.7) 2> go ------------------------ ------------------------ 2.7182818284590451 14.879731724872837 MySQL EXP(X) 返回 e 的 X 乘方后的值(自然对数的底)。 mysql> SELECT EXP(2); -> 7.3890560989307 mysql> SELECT EXP(-2); -> 0.13533528323661 mysql> SELECT EXP(0); -> 1 南無阿弥陀佛 47 向下取整 FLOOR Oracle SQL> SELECT FLOOR(-23.5), FLOOR(23.5) FROM DUAL; FLOOR(-23.5) FLOOR(23.5) ------------ ----------- -24 23 SQL Server 1> SELECT FLOOR(-23.5), FLOOR(23.5) 2> go ----- ----- -24 23 (1 行受影响) MySQL mysql> SELECT FLOOR(-23.5), FLOOR(23.5) // +--------------+-------------+ | FLOOR(-23.5) | FLOOR(23.5) | +--------------+-------------+ | -24 | 23 | +--------------+-------------+ 1 row in set (0.00 sec) 保留小数位数 FORMAT (MySQL Only) MySQL FORMAT(X,D) 将 number X 设置为格式 '#,###,###.##', 以四舍五入的方式保留到小数点后 D 位, 而返回结果为一个字符串。 mysql> SELECT FORMAT (-123.456,2) A, -> FORMAT (-123.456,1) B, -> FORMAT (-123.456,0) C, -> FORMAT (-123.456,-1) D -> // +---------+--------+------+------+ | A | B | C | D | +---------+--------+------+------+ | -123.46 | -123.5 | -123 | -123 | +---------+--------+------+------+ 1 row in set (0.00 sec) 南無阿弥陀佛 48 转 16 进制 HEX (MySQL Only) MySQL mysql> SELECT Hex (255) A -> // +------+ | A | +------+ | FF | +------+ 1 row in set (0.00 sec) INTERVAL MySQL INTERVAL(N,N1,N2,N3,...) 假如 N < N1,则返回值为 0;假如 N < N2 等等,则返回值为 1;假如 N 为 NULL, 则返回值为 -1 。所有的参数均按照整数处理。为了这个函数的正确运行,必 须满足 N1 < N2 < N3 < ¡¡ < Nn 。其原因是使用了二分查找(极快速)。 mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200) A; +---+ | A | +---+ | 3 | +---+ 1 row in set (0.00 sec) mysql> SELECT INTERVAL(10, 1, 10, 100, 1000) A; +---+ | A | +---+ | 2 | +---+ 1 row in set (0.00 sec) 自然对数 LN / LOG Oracle SQL> SELECT LN(100) FROM DUAL; LN(100) ---------- 南無阿弥陀佛 49 4.60517019 SQL Server 1> SELECT LOG(100) 2> go ------------------------ 4.6051701859880918 (1 行受影响) MySQL LN(X) 返回 X 的自然对数,即, X 相对于基数 e 的对数。 mysql> SELECT LN(2); -> 0.69314718055995 mysql> SELECT LN(-2); -> NULL 这个函数同 LOG(X)具有相同意义。 对数 LOG Oracle SQL> SELECT LOG(2, 32), LOG(5, 25) FROM DUAL; LOG(2,32) LOG(5,25) ---------- ---------- 5 2 SQL Server MySQL LOG(X) LOG(B,X) 若用一个参数调用,这个函数就会返回 X 的自然对数。 mysql> SELECT LOG(2); -> 0.69314718055995 mysql> SELECT LOG(-2); -> NULL 若用两个参数进行调用,这个函数会返回 X 对于任意基数 B 的对数。 mysql> SELECT LOG(2,65536); -> 16 mysql> SELECT LOG(10,100); -> 2 LOG(B,X) 就相当于 LOG(X) / LOG(B)。 以 2 为底的对数 LOG2 MySQL LOG2(X) 南無阿弥陀佛 50 返回 X 的基数为 2 的对数。 mysql> SELECT LOG2(65536); -> 16 mysql> SELECT LOG2(-100); -> NULL 对于查出存储一个数字需要多少个比特,LOG2()非常有效。这个函数相当于表 达式 LOG(X) / LOG(2)。 以 10 为底的对数 LOG10 Oracle SQL Server 1> SELECT LOG10(1000), LOG10(258) 2> go ------------------------ ------------------------ 3 2.4116197059632301 (1 行受影响) MySQL LOG10(X) 返回 X 的基数为 10 的对数。 mysql> SELECT LOG10(2); -> 0.30102999566398 mysql> SELECT LOG10(100); -> 2 mysql> SELECT LOG10(-100); -> NULL LOG10(X)相当于 LOG(10,X)。 余数 MOD / % Oracle SQL> SELECT MOD(23,5), MOD(4,1.3) FROM DUAL; MOD(23,5) MOD(4,1.3) ---------- ---------- 3 .1 SQL Server 1> SELECT 23%5, 4%1.3 2> go ----------- ---- 南無阿弥陀佛 51 3 .1 (1 行受影响) MySQL mysql> SELECT MOD(23,5), MOD(4,1.3) -> // +-----------+------------+ | MOD(23,5) | MOD(4,1.3) | +-----------+------------+ | 3 | 0.1 | +-----------+------------+ 1 row in set (0.00 sec) mysql> SELECT 23%5, 4%1.3; +------+-------+ | 23%5 | 4%1.3 | +------+-------+ | 3 | 0.1 | +------+-------+ 1 row in set (0.00 sec) 哈希值 ORA_HASH (Oracle) Oracle SQL> SELECT 2 ORA_HASH('test', 99, 5) A 3 FROM 4 dual; A ---------- 90 PI 的常量值 PI Oracle SQL Server 1> SELECT PI() 2> go ------------------------ 3.1415926535897931 (1 行受影响) MySQL PI() 南無阿弥陀佛 52 返回 PI(pi)的值。默认的显示小数位数是 7 位,然而 MySQL 内部会使用完全双 精度值。 mysql> SELECT PI(); -> 3.141593 mysql> SELECT PI()+0.000000000000000000; -> 3.141592653589793116 乘指定次方 POWER Oracle SQL> SELECT POWER(4,3), POWER(1.1,2.6), POWER(25,-2), POWER(-2,3) FROM DUAL; POWER(4,3) POWER(1.1,2.6) POWER(25,-2) POWER(-2,3) ---------- -------------- ------------ ----------- 64 1.28121195 .0016 -8 SQL Server 1> SELECT POWER(4,3), POWER(1.1,2.6), POWER(25,-2), POWER(-2,3) 2> go ----------- ---------------------------------------- ----------- ----------- 64 1.3 0 -8 (1 行受影响) MySQL mysql> SELECT POWER(4,3), POWER(1.1,2.6), POWER(25,-2), POWER(-2,3) -> // +------------+------------------+--------------+-------------+ | POWER(4,3) | POWER(1.1,2.6) | POWER(25,-2) | POWER(-2,3) | +------------+------------------+--------------+-------------+ | 64 | 1.28121195203536 | 0.0016 | -8 | +------------+------------------+--------------+-------------+ 1 row in set (0.03 sec) 度数值返回弧度值 RADIANS Oracle SQL Server SELECT RADIANS(90); ----------- 1 (1 行受影响) 南無阿弥陀佛 53 MySQL RADIANS(X) 返回由度转化为弧度的参数 X, (注意 ? 弧度等于 180 度)。 mysql> SELECT RADIANS(90); -> 1.5707963267949 随机数 RAND Oracle SQL Server 返回从 0 到 1 之 间 的 随 机 float 值。 参数 seed 提供种子值的整数 表达式(tinyint、 smallint 或 int)。 如果未指定 seed, 则 Microsoft SQL Server 2005 数 据 库引擎 随机分配 种子值。对于指定 的种子值,返回的 结果始终相同。 1> SELECT RAND(100) 2> go ------------------------ 0.7154366573674853 (1 行受影响) 1> SELECT RAND() 2> go ------------------------ 0.52387284052730643 (1 行受影响) 1> SELECT RAND() 2> go ------------------------ 0.92388223999987373 (1 行受影响) MySQL RAND() RAND(N) 返回一个随机浮点值 v ,范围在 0 到 1 之间 (即, 其范围为 0 ≤ v ≤ 1.0)。 若已指定一个整数参数 N ,则它被用作种子值,用来产生重复序列。 mysql> SELECT RAND(); -> 0.9233482386203 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(); -> 0.63553050033332 mysql> SELECT RAND(); -> 0.70100469486881 南無阿弥陀佛 54 mysql> SELECT RAND(20); -> 0.15888261251047 若要在 i ≤ R ≤ j 这个范围得到一个随机整数 R ,需要用到表达式 FLOOR(i + RAND() * (j ¨ i + 1))。例如, 若要在 7 到 12 的范围(包括 7 和 12)内得 到一个随机整数, 可使用以下语句: SELECT FLOOR(7 + (RAND() * 6)); 在 ORDER BY 语句中,不能使用一个带有 RAND()值的列,原因是 ORDER BY 会计算列的多重时间。然而,可按照如下的随机顺序检索数据行: mysql> SELECT * FROM tbl_name ORDER BY RAND(); ORDER BY RAND()同 LIMIT 的结合从一组列中选择随机样本很有用: mysql> SELECT * FROM table1, table2 WHERE a=b AND c ORDER BY RAND() LIMIT 1000; 注意,在 WHERE 语句中,WHERE 每执行一次, RAND()就会被再计算一次。 RAND()的作用不是作为一个精确的随机发生器,而是一种用来发生在同样的 MySQL 版本的平台之间的可移动 ad hoc 随机数的快速方式。 四舍五入 ROUND Oracle SQL> SELECT ROUND(1.56), ROUND(1.56,1), ROUND(12.34, -2) FROM dual; ROUND(1.56) ROUND(1.56,1) ROUND(12.34,-2) ----------- ------------- --------------- 2 1.6 0 SQL Server 注:该函数要求 2-3 个参数 第3个参数非0时, 为截尾 1> SELECT ROUND(1.56, 0), ROUND(1.56,1), ROUND(12.34, -2) 2> go ----- ----- ------ 2.00 1.60 .00 (1 行受影响) MySQL mysql> SELECT ROUND(1.56), ROUND(1.56,1), ROUND(12.34, -2) -> // +-------------+---------------+------------------+ | ROUND(1.56) | ROUND(1.56,1) | ROUND(12.34, -2) | +-------------+---------------+------------------+ | 2 | 1.6 | 0 | +-------------+---------------+------------------+ 1 row in set (0.00 sec) 南無阿弥陀佛 55 表达式的正 (+1)、零 (0) 或负 (-1) 号 SIGN Oracle SQL> SELECT SIGN(-47.3), SIGN(0), SIGN(47.3) FROM dual; SIGN(-47.3) SIGN(0) SIGN(47.3) ----------- ---------- ---------- -1 0 1 SQL Server 1> SELECT SIGN(-47.3), SIGN(0), SIGN(47.3) 2> go ----- ----------- ----- -1.0 0 1.0 MySQL mysql> SELECT SIGN(-47.3), SIGN(0), SIGN(47.3) // +-------------+---------+------------+ | SIGN(-47.3) | SIGN(0) | SIGN(47.3) | +-------------+---------+------------+ | -1 | 0 | 1 | +-------------+---------+------------+ 1 row in set (0.00 sec) 正弦 SIN Oracle SQL> SELECT SIN(0), SIN(60*3.14159265359/180) FROM dual; SIN(0) SIN(60*3.14159265359/180) ---------- ------------------------- 0 .866025404 SQL Server 1> SELECT SIN(0), SIN(60*3.14159265359/180) 2> go ------------------------ ------------------------ 0 0.86602540378447279 (1 行受影响) MySQL SIN(X) 返回 X 正弦,其中 X 在弧度中被给定。 mysql> SELECT SIN(PI()); -> 1.2246063538224e-16 mysql> SELECT ROUND(SIN(PI())); -> 0 南無阿弥陀佛 56 双曲正弦 SINH (Oracle Only) Oracle SQL> SELECT SINH(0), SINH(60*3.14159265359/180) FROM dual; SINH(0) SINH(60*3.14159265359/180) ---------- -------------------------- 0 1.24936705 SQL Server 平方 SQUARE (SQL Server Only) Oracle SQL Server 1> SELECT SQUARE(8) 2> go ------------------------ 64 (1 行受影响) 平方根 SQRT Oracle SQL> SELECT SQRT(64), SQRT(97.654) FROM dual; SQRT(64) SQRT(97.654) ---------- ------------ 8 9.88200385 SQL Server 1> SELECT SQRT(64), SQRT(97.654) 2> go ------------------------ ------------------------ 8 9.8820038453746815 (1 行受影响) MySQL mysql> SELECT SQRT(64), SQRT(97.654) // +----------+------------------+ | SQRT(64) | SQRT(97.654) | +----------+------------------+ | 8 | 9.88200384537468 | +----------+------------------+ 南無阿弥陀佛 57 1 row in set (0.02 sec) 正切 TAN Oracle SQL> SELECT TAN(0), TAN(-60*3.14159265359/180) FROM dual; TAN(0) TAN(-60*3.14159265359/180) ---------- -------------------------- 0 -1.7320508 SQL Server 1> SELECT TAN(0), TAN(-60*3.14159265359/180) 2> go ------------------------ ------------------------ 0 -1.7320508075691503 (1 行受影响) MySQL TAN(X) 返回 X 的正切,其中 X 在弧度中被给定。 mysql> SELECT TAN(PI()); -> -1.2246063538224e-16 mysql> SELECT TAN(PI()+1); -> 1.5574077246549 双曲正切 TANH (Oracle Only) Oracle SQL> SELECT TANH(0), TANH(-60*3.14159265359/180) FROM dual; TANH(0) TANH(-60*3.14159265359/180) ---------- --------------------------- 0 -.78071444 SQL Server 截尾 TRUNC / ROUND Oracle SQL> SELECT TRUNC(-123.456), TRUNC(-123.456,1), TRUNC(-123.456,-1) FROM dual; TRUNC(-123.456) TRUNC(-123.456,1) TRUNC(-123.456,-1) 南無阿弥陀佛 58 --------------- ----------------- ------------------ -123 -123.4 -120 SQL Server 注:ROUND 第 3 个参数非 0 情况 下,为截尾。 1> SELECT ROUND(151.75, 1, 0), ROUND(151.75, -1, 1), ROUND(151.75, 1, 1); 2> go ------- ------- ------- 151.80 150.00 151.70 (1 行受影响) MySQL TRUNCATE(X,D) 返回被舍去至小数点后 D 位的数字 X。若 D 的值为 0, 则结果不带有小数点或 不带有小数部分。可以将 D 设为负数,若要截去(归零) X 小数点左起第 D 位开始 后面所有低位的值. mysql> SELECT TRUNCATE(1.223,1); -> 1.2 mysql> SELECT TRUNCATE(1.999,1); -> 1.9 mysql> SELECT TRUNCATE(1.999,0); -> 1 mysql> SELECT TRUNCATE(-1.999,1); -> -1.9 mysql> SELECT TRUNCATE(122,-2); -> 100 mysql> SELECT TRUNCATE(10.28*100,0); -> 1028 所有数字的舍入方向都接近于零。 数字格式化显示 Oracle 使用 TO_CHAR 函数 格式化字符中 9 和 0 都表示数字。 前导的 0 表示强制显示,也就是没有数字,也显示 0 前导的 9,在没有数字的时候,显示为空格。 G 表示千分位分隔符号,D 表示小数点。 SQL> SELECT TO_CHAR(12345.6, '999G999D99') FROM DUAL; TO_CHAR(123 ----------- 12,345.60 南無阿弥陀佛 59 SQL> SELECT TO_CHAR(12345.6, '000G000D00') FROM DUAL; TO_CHAR(123 ----------- 012,345.60 S 为正数 设置一个 +号,负数设置一个 ¨号 SQL> SELECT TO_CHAR(12345.6, 'S999G999D99') FROM DUAL; TO_CHAR(123 ----------- +12,345.60 SQL> SELECT TO_CHAR(-12345.6, 'S999G999D99') FROM DUAL; TO_CHAR(-12 ----------- -12,345.60 PR 负数将以 < > 的方式显示 SQL> SELECT TO_CHAR(12345.6, '999G999D99PR') FROM DUAL; TO_CHAR(1234 ------------ 12,345.60 SQL> SELECT TO_CHAR(-12345.6, '999G999D99PR') FROM DUAL; TO_CHAR(-123 ------------ <12,345.60> EEEE 表示科学计数 SQL> SELECT TO_CHAR(1234567890, '9.99EEEE') FROM DUAL; TO_CHAR(12 ---------- 1.23E+09 RM 表示罗马数字 SQL> SELECT TO_CHAR(12, 'RM') FROM DUAL; 南無阿弥陀佛 60 TO_CHAR(12,'RM' --------------- XII SQL Server 对于 SQL Server,要将数字格式化为特定的格式。 使用 Convert 函数。 仅仅对于 float 或 real 数据类型, 以及 money 或 smallmoney 数据类型 才有相应的 格式可以设置。 其他数据类型的,可能就没有可选的格式了。 对于 float 或 real 数据类型 值 输出 0(默认值) 最多包含 6 位。根据需要使用科学记数法。 1 始终为 8 位值。始终使用科学记数法。 2 始终为 16 位值。始终使用科学记数法。 对于 money 或 smallmoney 数据类型。 值 输出 0(默认值) 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位 数,例如 4235.98。 1 小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位 数,例如 3,510.92。 2 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位 数,例如 4235.9819。 DECLARE @myVal AS Money; SET @myVal = 123456789.12345; SELECT Convert(VARCHAR, @myVal, 1); ------------------------------ 123,456,789.12 (1 行受影响) MySQL FORMAT(X,D) 将 number X 设置为格式 '#,###,###.##', 以四舍五入的方式保留到小数点后 D 位, 而返回结果为一个字符串。 南無阿弥陀佛 61 南無阿弥陀佛 62 日期函数 取得数据库系统时间 SYSDATE / GETDATE() Oracle SQL> ALTER session 2 SET nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> SQL> SELECT sysdate FROM DUAL; SYSDATE ------------------- 2010-08-31 18:33:59 SQL> ALTER SESSION SET TIME_ZONE = '-5:0'; 会话已更改。 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; 会话已更改。 SQL> SELECT 2 SESSIONTIMEZONE, 3 CURRENT_DATE, 4 SYSDATE 5 FROM 6 DUAL; SESSIONTIMEZONE ---------------------------------------- CURRENT_DATE SYSDATE ------------------- ------------------- -05:00 2011-06-14 07:26:39 2011-06-14 20:26:38 SQL> SELECT 2 SESSIONTIMEZONE, 3 CURRENT_TIMESTAMP 南無阿弥陀佛 63 4 FROM 5 DUAL; SESSIONTIMEZONE ---------------------------------------- CURRENT_TIMESTAMP ---------------------------------------- -05:00 14-6 月 -11 07.28.34.296000 上午 -05:00 SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; CURRENT_TIMESTAMP --------------------------------------------------------- LOCALTIMESTAMP --------------------------------------------------------- 14-6 月 -11 09.09.32.609000 下午 +08:00 14-6 月 -11 09.09.32.609000 下午 SQL Server 1> SELECT getdate() 2> go ----------------------- 2010-08-31 18:34:40.077 (1 行受影响) 1> SELECT GETDATE(), GETUTCDATE() 2> GO ----------------------- ----------------------- 2011-05-06 23:46:06.373 2011-05-06 15:46:06.373 (1 行受影响) GETUTCDATE() 返回表示当前 UTC 时间(通用协调时间或格林尼治标准时间)的 datetime 值。 当前的 UTC 时间得自当前的本地时间和运行 Microsoft SQL Server 实例的计 算机操作系统中的时区设置。 SQL Server 2008 新增 1> select SYSDATETIME() 2> go 南無阿弥陀佛 64 -------------------------------------- 2011-05-23 13:07:38.1493060 (1 行受影响) 1> select SYSDATETIMEOFFSET ( ) 2> go --------------------------------------------- 2011-05-23 13:08:26.0230442 +08:00 (1 行受影响) 1> select SYSUTCDATETIME ( ) 2> go -------------------------------------- 2011-05-23 05:08:37.8177189 (1 行受影响) MySQL 当前日期 mysql> SELECT CURRENT_DATE(); +----------------+ | CURRENT_DATE() | +----------------+ | 2010-10-22 | +----------------+ 1 row in set (0.00 sec) 当前时间 mysql> select CURRENT_TIME (); +-----------------+ | CURRENT_TIME () | +-----------------+ | 20:16:32 | +-----------------+ 1 row in set (0.00 sec) 当前时间戳 mysql> SELECT CURRENT_TIMESTAMP(); +---------------------+ | CURRENT_TIMESTAMP() | +---------------------+ | 2010-10-22 20:17:07 | +---------------------+ 1 row in set (0.00 sec) 当前时间 mysql> SELECT NOW(); +---------------------+ 南無阿弥陀佛 65 | NOW() | +---------------------+ | 2010-10-22 20:33:23 | +---------------------+ 1 row in set (0.00 sec) 日期增减计算 Oracle 只有一个 ADD_MONTHS,其他的 按 1 天=1 计算 SQL> ALTER session 2 SET nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> SQL> SELECT 'Now' AS date_title, sysdate AS date_value FROM dual 2 UNION ALL 3 SELECT 'Next Sec' AS date_title, sysdate + 1/(24*60*60) AS date_value FROM dual 4 UNION ALL 5 SELECT 'Next Min' AS date_title, sysdate + 1/(24*60) AS date_value FROM du al 6 UNION ALL 7 SELECT 'Next Hour' AS date_title, sysdate + 1/24 AS date_value FROM dual 8 UNION ALL 9 SELECT 'Tomorrow' AS date_title, sysdate + 1 AS date_value FROM dual 10 UNION ALL 11 SELECT 'Next Week' AS date_title, sysdate + 7 AS date_value FROM dual 12 UNION ALL 13 SELECT 'Next Month' AS date_title, ADD_MONTHS(sysdate,1) AS date_value FR OM dual 14 UNION ALL 15 SELECT 'Next Quarter' AS date_title, ADD_MONTHS(sysdate,3) AS date_value FROM dual 16 UNION ALL 17 SELECT 'Next year' AS date_title, ADD_MONTHS(sysdate,12) AS date_value FR OM dual 南無阿弥陀佛 66 18 ; DATE_TITLE DATE_VALUE ------------------------ ------------------- Now 2010-08-31 18:11:01 Next Sec 2010-08-31 18:11:02 Next Min 2010-08-31 18:12:01 Next Hour 2010-08-31 19:11:01 Tomorrow 2010-09-01 18:11:01 Next Week 2010-09-07 18:11:01 Next Month 2010-09-30 18:11:01 Next Quarter 2010-11-30 18:11:01 Next year 2011-08-31 18:11:01 9 rows selected. 注意:NEXT_DAY 的第 2 个参数,与会话中的语言设置有关系。 SQL> SELECT 2 SYSDATE, 3 NEXT_DAY(SYSDATE,'星期日') "下个周日" 4 FROM DUAL; SYSDATE 下个周日 -------------- -------------- 14-6 月 -11 19-6 月 -11 SQL Server 使用 DATEADD 函数 日期部分 缩写 year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw, w hour hh minute mi, n second ss, s millisecond ms 南無阿弥陀佛 67 1> 2> SELECT '现在' AS [日期], GETDATE() AS [数据] 3> UNION ALL 4> SELECT '下一秒' AS [日期], DATEADD(ss, 1, GETDATE() ) AS [数据] 5> UNION ALL 6> SELECT '下一分' AS [日期], DATEADD(mi, 1, GETDATE() ) AS [数据] 7> UNION ALL 8> SELECT '下一小时' AS [日期], DATEADD(hh, 1, GETDATE() ) AS [数据] 9> UNION ALL 10> SELECT '明天' AS [日期], DATEADD(dd, 1, GETDATE() ) AS [数据] 11> UNION ALL 12> SELECT '下周' AS [日期], DATEADD(ww, 1, GETDATE() ) AS [数据] 13> UNION ALL 14> SELECT '下月' AS [日期], DATEADD(mm, 1, GETDATE() ) AS [数据] 15> UNION ALL 16> SELECT '下季度' AS [日期], DATEADD(qq, 1, GETDATE() ) AS [数据] 17> UNION ALL 18> SELECT '明年' AS [日期], DATEADD(yy, 1, GETDATE() ) AS [数据] 19> go 日期 数据 -------- ----------------------- 现在 2010-08-31 17:20:57.950 下一秒 2010-08-31 17:20:58.950 下一分 2010-08-31 17:21:57.950 下一小时 2010-08-31 18:20:57.950 明天 2010-09-01 17:20:57.950 下周 2010-09-07 17:20:57.950 下月 2010-09-30 17:20:57.950 下季度 2010-11-30 17:20:57.950 明年 2011-08-31 17:20:57.950 (9 行受影响) MySQL ADDTIME (日期,时间) 或者 ADDTIME (时间,时间) DATE_ADD 指定日期 + 日/月/周/年 时间增加 mysql> SELECT ADDTIME( -> '2000-01-01 00:00:00.000000', -> '1 2:3:4.000005') A; +----------------------------+ | A | +----------------------------+ | 2000-01-02 02:03:04.000005 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDTIME( 南無阿弥陀佛 68 DATE_SUB 指定日期 - 日/月/周/年 可选单位参数 MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR -> '01:00:00.000000', -> '02:03:04.000005') B; +-----------------+ | B | +-----------------+ | 03:03:04.000005 | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_ADD( -> '2000-01-01 00:00:00.000000', -> INTERVAL 2 DAY) A, -> DATE_SUB( -> '2000-01-01 00:00:00.000000', -> INTERVAL 2 DAY) B; +---------------------+---------------------+ | A | B | +---------------------+---------------------+ | 2000-01-03 00:00:00 | 1999-12-30 00:00:00 | +---------------------+---------------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_ADD( -> '2000-01-01 00:00:00.000000', -> INTERVAL 2 MONTH) A, -> DATE_SUB( -> '2000-01-01 00:00:00.000000', -> INTERVAL 2 MONTH) B; +---------------------+---------------------+ | A | B | +---------------------+---------------------+ | 2000-03-01 00:00:00 | 1999-11-01 00:00:00 | +---------------------+---------------------+ 1 row in set (0.00 sec) 日期组成部分的取得 Oracle 基本上使用 TO_CHAR 来获取 Session altered. SQL> SELECT 2 TO_CHAR(SYSDATE, 'YYYY') AS year, 3 TO_CHAR(SYSDATE, 'MM') AS month, 南無阿弥陀佛 69 4 TO_CHAR(SYSDATE, 'DD') AS day, 5 TO_CHAR(SYSDATE, 'DAY') AS Week 6 FROM DUAL; YEAR MONT DAY WEEK -------- ---- ---- ------------------ 2010 08 31 TUESDAY SQL> SELECT 'Year' AS date_title, TO_CHAR(SYSDATE, 'YYYY') AS date_value FROM DUAL 2 UNION ALL 3 SELECT 'Quarter' AS date_title, TO_CHAR(SYSDATE, 'Q') AS date_value FROM DUAL 4 UNION ALL 5 SELECT 'Month' AS date_title, TO_CHAR(SYSDATE, 'MM') AS date_value FROM D UAL 6 UNION ALL 7 SELECT 'Day' AS date_title, TO_CHAR(SYSDATE, 'DD') AS date_value FROM DUA L 8 UNION ALL 9 SELECT 'Week Of Year' AS date_title, TO_CHAR(SYSDATE, 'IW') AS date_value FROM DUAL 10 UNION ALL 11 SELECT 'Day Of Week' AS date_title, TO_CHAR(SYSDATE, 'D') AS date_value F ROM DUAL 12 UNION ALL 13 SELECT 'Day Of Year' AS date_title, TO_CHAR(SYSDATE, 'DDD') AS date_value FROM DUAL 14 UNION ALL 15 SELECT 'Hour' AS date_title, TO_CHAR(SYSDATE, 'HH24') AS date_value FROM DUAL 16 UNION ALL 17 SELECT 'minute' AS date_title, TO_CHAR(SYSDATE, 'MI') AS date_value FROM DUAL 18 UNION ALL 19 SELECT 'Second' AS date_title, TO_CHAR(SYSDATE, 'SS') AS date_value FROM 南無阿弥陀佛 70 DUAL; DATE_TITLE DATE_VAL ------------------------ -------- Year 2010 Quarter 3 Month 08 Day 31 Week Of Year 35 Day Of Week 3 Day Of Year 243 Hour 17 minute 53 Second 41 10 rows selected. 也可以使用 EXTRACT 函数 SQL> SELECT '年' AS "标题", EXTRACT(YEAR FROM SYSDATE) AS "数值" FROM dual UNION ALL 2 SELECT '月' AS "标题", EXTRACT(MONTH FROM SYSDATE) AS "数值 " FROM dual UNION ALL 3 SELECT '日' AS "标题", EXTRACT(DAY FROM SYSDATE) AS "数值" FROM dual; 标 数值 -- ---------- 年 2011 月 6 日 14 SQL Server 年、月、日有单独函数,其他的由 DATEPART 获取 1> SELECT '年' AS '日期部分', YEAR(GETDATE()) AS [数值] 2> UNION ALL 3> SELECT '季度' AS '日期部分', DATEPART(qq, GETDATE()) AS [数值] 4> UNION ALL 5> SELECT '月' AS '日期部分', MONTH(GETDATE()) AS [数值] 6> UNION ALL 7> SELECT '日' AS '日期部分', DAY(GETDATE()) AS [数值] 8> UNION ALL 9> SELECT '第几周' AS '日期部分', DATEPART(ww, GETDATE()) AS [数 值] 10> UNION ALL 11> SELECT '周中第几天' AS '日期部分', DATEPART(dw, GETDATE()) 南無阿弥陀佛 71 AS [数值] 12> UNION ALL 13> SELECT '年中第几天' AS '日期部分', DATEPART(dy, GETDATE()) AS [数值] 14> UNION ALL 15> SELECT '时' AS '日期部分', DATEPART(hh, GETDATE()) AS [数值] 16> UNION ALL 17> SELECT '分' AS '日期部分', DATEPART(mi, GETDATE()) AS [数值] 18> UNION ALL 19> SELECT '秒' AS '日期部分', DATEPART(ss, GETDATE()) AS [数值] 20> go 日期部分 数值 ---------- ----------- 年 2010 季度 3 月 8 日 31 第几周 36 周中第几天 3 年中第几天 243 时 17 分 45 秒 25 (10 行受影响) MySQL mysql> SELECT 'NOW' DisplayMode, NOW() B -> UNION ALL -> SELECT 'Second' DisplayMode, Second(NOW()) B -> UNION ALL -> SELECT 'Minute' DisplayMode, MINUTE(NOW()) B -> UNION ALL -> SELECT 'Hour' DisplayMode, Hour(NOW()) B -> UNION ALL -> SELECT 'Day' DisplayMode, DAY(NOW()) B -> UNION ALL -> SELECT 'Week' DisplayMode, Week(NOW()) B -> UNION ALL -> SELECT 'Month' DisplayMode, Month(NOW()) B -> UNION ALL -> SELECT 'Year' DisplayMode, Year(NOW()) B; +-------------+---------------------+ | DisplayMode | B | +-------------+---------------------+ | NOW | 2010-10-22 20:43:09 | | Second | 9 | | Minute | 43 | 南無阿弥陀佛 72 | Hour | 20 | | Day | 22 | | Week | 42 | | Month | 10 | | Year | 2010 | +-------------+---------------------+ 8 rows in set (0.00 sec) mysql> SELECT 'DAYOFWEEK Sunday=1' DisplayMode, DAYOFWEEK(NOW()) B -> UNION ALL -> SELECT 'DAYOFMONTH' DisplayMode, DAYOFMONTH(NOW()) B -> UNION ALL -> SELECT 'DayOfYear' DisplayMode, DAYOFYEAR(NOW()) B -> UNION ALL -> SELECT 'Eng Week' DisplayMode, DAYNAME(NOW()) B -> UNION ALL -> SELECT 'Eng Month' DisplayMode, MONTHNAME(NOW()) B; +--------------------+---------+ | DisplayMode | B | +--------------------+---------+ | DAYOFWEEK Sunday=1 | 6 | | DAYOFMONTH | 22 | | DayOfYear | 295 | | Eng Week | Friday | | Eng Month | October | +--------------------+---------+ 5 rows in set (0.00 sec) mysql> SELECT EXTRACT(YEAR FROM NOW()) -> UNION ALL -> SELECT EXTRACT(YEAR_MONTH FROM NOW()) -> UNION ALL -> SELECT EXTRACT(DAY_MINUTE FROM NOW()); +--------------------------+ | EXTRACT(YEAR FROM NOW()) | +--------------------------+ | 2010 | | 201010 | | 222101 | +--------------------------+ 3 rows in set (0.00 sec) 南無阿弥陀佛 73 两个日期之间的差值 Oracle 两日期相减,单位是¡ 天¡ SQL> SELECT 2 TO_DATE('2012-12-21', 'YYYY-MM-DD') - sysdate AS have_dates 3 FROM 4 dual; HAVE_DATES ---------- 842.234896 MONTHS_BETWEEN 函数,单位是¡ 月¡ SQL> SELECT 2 MONTHS_BETWEEN(TO_DATE('2012-12-21', 'YYYY-MM-DD'), sysdate) AS have_mont hs 3 FROM 4 dual; HAVE_MONTHS ----------- 27.6526609 SQL Server 1> SELECT '年' AS '日期部分', DATEDIFF(yy, '2012-12-21', GETDATE()) AS [数值 ] 2> UNION ALL 3> SELECT '季度' AS '日期部分', DATEDIFF(qq, '2012-12-21', GETDATE()) AS [数 值] 4> UNION ALL 5> SELECT '月' AS '日期部分', DATEDIFF(mm, '2012-12-21', GETDATE()) AS [数值] 6> UNION ALL 7> SELECT '日' AS '日期部分', DATEDIFF(dd, '2012-12-21', GETDATE()) AS [数值] 8> UNION ALL 9> SELECT '周' AS '日期部分', DATEDIFF(ww, '2012-12-21', GETDATE()) AS [数值 ] 南無阿弥陀佛 74 10> UNION ALL 11> SELECT '时' AS '日期部分', DATEDIFF(hh, '2012-12-21', GETDATE()) AS [数 值] 12> UNION ALL 13> SELECT '分' AS '日期部分', DATEDIFF(mi, '2012-12-21', GETDATE()) AS [数 值] 14> UNION ALL 15> SELECT '秒' AS '日期部分', DATEDIFF(ss, '2012-12-21', GETDATE()) AS [数 值] 16> go 日期部分 数值 ---- ----------- 年 -2 季度 -9 月 -28 日 -843 周 -120 时 -20214 分 -1212810 秒 -72768599 (8 行受影响) MySQL DATEDIFF 只 计 算到天的单位 TIMEDIFF 精 确 到毫秒单位 mysql> SELECT DATEDIFF ( -> '2000-01-01 00:00:00.000000', -> '2000-01-02 05:06:07.000008') A; +------+ | A | +------+ | -1 | +------+ 1 row in set (0.00 sec) mysql> SELECT -> TIMEDIFF ('2000-01-01 00:00:00.000000', -> '2000-01-02 05:06:07.000008') A; +------------------+ | A | +------------------+ | -29:06:07.000008 | +------------------+ 1 row in set (0.00 sec) 南無阿弥陀佛 75 日期的截尾 Oracle alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; Session altered. SQL> SELECT 'Year' AS truncType , TRUNC( SYSDATE, 'YYYY' ) FROM DUAL 2 UNION ALL 3 SELECT 'Quarter' AS truncType, TRUNC( SYSDATE, 'Q' ) FROM DUAL 4 UNION ALL 5 SELECT 'Month' AS truncType, TRUNC( SYSDATE, 'MM' ) FROM DUAL 6 UNION ALL 7 SELECT 'Week' AS truncType, TRUNC( SYSDATE, 'W' ) FROM DUAL 8 UNION ALL 9 SELECT 'Day' AS truncType, TRUNC( SYSDATE, 'D' ) FROM DUAL 10 UNION ALL 11 SELECT 'Hour' AS truncType, TRUNC( SYSDATE, 'HH' ) FROM DUAL; TRUNCTYPE TRUNC(SYSDATE,'YYYY -------------- ------------------- Year 2010-01-01 00:00:00 Quarter 2010-10-01 00:00:00 Month 2010-10-01 00:00:00 Week 2010-10-15 00:00:00 Day 2010-10-17 00:00:00 Hour 2010-10-17 22:00:00 6 rows selected. LAST_DAY = 本月最后一天 SQL> SELECT 2 LAST_DAY(SYSDATE) A 3 FROM 4 dual; A -------------- 30-6 月 -11 SQL Server 1> SELECT '本年' AS [截尾方式], DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS [结果] 2> UNION ALL 3> SELECT ' 本 年 月 份 ' AS [ 截 尾 方 式 ], DATEADD(MONTH, 南無阿弥陀佛 76 DATEDIFF(MONTH, 0, GETDATE() ), 0) AS [结果] 4> UNION ALL 5> SELECT '本周一' AS [截尾方式], DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0 ) AS [结果] 6> UNION ALL 7> SELECT '本季度' AS [截尾方式], DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0) AS [结果] 8> UNION ALL 9> SELECT ' 本 月 结 束 ' AS [ 截 尾 方 式 ], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE( )) + 1, 0) -1 AS [结果] 10> UNION ALL 11> SELECT ' 本 年 结 束 ' AS [ 截 尾 方 式 ], DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE() ) + 1, 0) - 1 AS [结果] 12> UNION ALL 13> SELECT ' 本 周 结 束 ' AS [ 截 尾 方 式 ], DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE() ) + 1, 0) - 1 AS [结果] 14> UNION ALL 15> SELECT '本季度结束' AS [截尾方式], DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) + 1, 0) - 1 AS [结果] 16> 17> go 截尾方式 结果 ---------- ----------------------- 本年 2010-01-01 00:00:00.000 本年月份 2010-10-01 00:00:00.000 本周一 2010-10-04 00:00:00.000 本季度 2010-10-01 00:00:00.000 本月结束 2010-10-31 00:00:00.000 本年结束 2010-12-31 00:00:00.000 本周结束 2010-10-10 00:00:00.000 本季度结束 2010-12-31 00:00:00.000 (8 行受影响) 1> MySQL 返回 datetime 的日期部分 mysql> SELECT DATE('2010-10-11 20:10:10') A; +------------+ | A | +------------+ 南無阿弥陀佛 77 | 2010-10-11 | +------------+ 1 row in set (0.00 sec) 返回指定日期的月末的一天. mysql> SELECT LAST_DAY(NOW()) A; +------------+ | A | +------------+ | 2010-10-31 | +------------+ 1 row in set (0.00 sec) mysql> SELECT '本年' AS `截尾方式`, -> DATE(DATE_SUB(NOW(),INTERVAL DAYOFYEAR(NOW())-1 DAY)) AS `结果` -> UNION ALL -> SELECT '本年' AS `截尾方式`, -> MAKEDATE ( YEAR(NOW()), 1) AS `结果` -> UNION ALL -> SELECT '本年月份' AS `截尾方式`, -> DATE(DATE_SUB(NOW(),INTERVAL DAYOFMONTH(NOW())-1 DAY)) AS `结果` -> UNION ALL -> SELECT '本周一' AS `截尾方式`, -> DATE(DATE_SUB(NOW(),INTERVAL DAYOFWEEK(NOW())-2 DAY)) AS `结果` -> UNION ALL -> SELECT '本周开始[周日]' AS `截尾方式`, -> DATE(DATE_SUB(NOW(),INTERVAL DAYOFWEEK(NOW())-1 DAY)) AS `结果` -> UNION ALL -> SELECT '本月结束' AS `截尾方式`, -> LAST_DAY(NOW()) AS `结果` -> UNION ALL -> SELECT '本年结束' AS `截尾方式`, -> DATE_ADD( -> DATE(DATE_SUB(NOW(),INTERVAL DAYOFYEAR(NOW()) DAY)), -> INTERVAL 1 YEAR) AS `结果` -> UNION ALL -> SELECT '本年结束' AS `截尾方式`, -> DATE_SUB( MAKEDATE ( YEAR(NOW()) + 1, 1), INTERVAL 1 DAY ) AS `结果` -> UNION ALL 南無阿弥陀佛 78 -> SELECT '本周结束' AS `截尾方式`, -> DATE(DATE_SUB(NOW(),INTERVAL DAYOFWEEK(NOW())- 7 DAY)) AS `结果`; +----------------+------------+ | 截尾方式 | 结果 | +----------------+------------+ | 本年 | 2011-01-01 | | 本年 | 2011-01-01 | | 本年月份 | 2011-04-01 | | 本周一 | 2011-04-18 | | 本周开始[周日] | 2011-04-17 | | 本月结束 | 2011-04-30 | | 本年结束 | 2011-12-31 | | 本年结束 | 2011-12-31 | | 本周结束 | 2011-04-23 | +----------------+------------+ 9 rows in set (0.00 sec) 日期的名称 DATENAME SQL Server SELECT DATENAME(year,'1995-10-30 12:15:32.123'); SELECT DATENAME(yy,'1995-10-30 12:15:32.123'); SELECT DATENAME(yyyy,'1995-10-30 12:15:32.123'); -- Return: 1995 SELECT DATENAME(quarter,'1995-10-30 12:15:32.123'); SELECT DATENAME(qq,'1995-10-30 12:15:32.123'); SELECT DATENAME(q,'1995-10-30 12:15:32.123'); -- Return: 4 SELECT DATENAME(month,'1995-10-30 12:15:32.123'); SELECT DATENAME(mm,'1995-10-30 12:15:32.123'); SELECT DATENAME(m,'1995-10-30 12:15:32.123'); -- Return: October SELECT DATENAME(dayofyear,'1995-10-30 12:15:32.123'); SELECT DATENAME(dy,'1995-10-30 12:15:32.123'); SELECT DATENAME(y,'1995-10-30 12:15:32.123'); -- Return: 303 SELECT DATENAME(day,'1995-10-30 12:15:32.123'); SELECT DATENAME(dd,'1995-10-30 12:15:32.123'); 南無阿弥陀佛 79 SELECT DATENAME(d,'1995-10-30 12:15:32.123'); -- Return: 30 SELECT DATENAME(week,'1995-10-30 12:15:32.123'); SELECT DATENAME(wk,'1995-10-30 12:15:32.123'); SELECT DATENAME(ww,'1995-10-30 12:15:32.123'); -- Return: 44 SELECT DATENAME(weekday,'1995-10-30 12:15:32.123'); SELECT DATENAME(dw,'1995-10-30 12:15:32.123'); -- Return: Monday SELECT DATENAME(hour,'1995-10-30 12:15:32.123'); SELECT DATENAME(hour,'1995-10-30 12:15:32.123'); SELECT DATENAME(hh,'10/30/1995 12:15:32.123 PM'); SELECT DATENAME(hh,'10/30/1995 12:15:32.123 PM'); -- Return: 12 SELECT DATENAME(minute,'1995-10-30 12:15:32.123'); SELECT DATENAME(mi,'1995-10-30 12:15:32.123'); SELECT DATENAME(n,'1995-10-30 12:15:32.123'); -- Return: 15 SELECT DATENAME(second,'1995-10-30 12:15:32.123'); SELECT DATENAME(ss,'1995-10-30 12:15:32.123'); SELECT DATENAME(s,'1995-10-30 12:15:32.123'); -- Return: 32 SELECT DATENAME(millisecond,'1995-10-30 12:15:32.123'); SELECT DATENAME(ms,'1995-10-30 12:15:32.123'); -- Return: 123 日期/时间的格式化[日期转换为字符串] Oracle SQL> SELECT 2 TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') 3 FROM 4 dual; TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS' -------------------------------------- 2010-09-27 20:10:33 SQL Server 1> SELECT CONVERT(VARCHAR(30),GETDATE(),9) 南無阿弥陀佛 80 2> go ------------------------------ 09 27 2010 8:11:45:687PM (1 行受影响) 1> SELECT CONVERT(VARCHAR(30),GETDATE(), 102) 2> go ------------------------------ 2010.10.09 SELECT 0 AS ID , Convert(VARCHAR(30), GETDATE(), 0 ) AS result UNION ALL SELECT 100 AS ID , Convert(VARCHAR(30), GETDATE(), 100) AS result UNION ALL SELECT 1 AS ID , Convert(VARCHAR(30), GETDATE(), 1 ) AS result UNION ALL SELECT 101 AS ID , Convert(VARCHAR(30), GETDATE(), 101) AS result UNION ALL SELECT 2 AS ID , Convert(VARCHAR(30), GETDATE(), 2 ) AS result UNION ALL SELECT 102 AS ID , Convert(VARCHAR(30), GETDATE(), 102) AS result UNION ALL SELECT 2 AS ID , Convert(VARCHAR(30), GETDATE(), 2 ) AS result UNION ALL SELECT 102 AS ID , Convert(VARCHAR(30), GETDATE(), 102) AS result UNION ALL SELECT 3 AS ID , Convert(VARCHAR(30), GETDATE(), 3 ) AS result UNION ALL SELECT 103 AS ID , Convert(VARCHAR(30), GETDATE(), 103) AS result UNION ALL SELECT 4 AS ID , Convert(VARCHAR(30), GETDATE(), 4 ) AS result UNION ALL SELECT 104 AS ID , Convert(VARCHAR(30), GETDATE(), 104) AS result UNION ALL SELECT 5 AS ID , Convert(VARCHAR(30), GETDATE(), 5 ) AS result UNION ALL SELECT 105 AS ID , Convert(VARCHAR(30), GETDATE(), 105) AS result UNION ALL SELECT 6 AS ID , Convert(VARCHAR(30), GETDATE(), 6 ) AS result UNION ALL SELECT 106 AS ID , Convert(VARCHAR(30), GETDATE(), 106) AS result UNION ALL SELECT 7 AS ID , Convert(VARCHAR(30), GETDATE(), 7 ) AS result 南無阿弥陀佛 81 UNION ALL SELECT 107 AS ID , Convert(VARCHAR(30), GETDATE(), 107) AS result UNION ALL SELECT 8 AS ID , Convert(VARCHAR(30), GETDATE(), 8 ) AS result UNION ALL SELECT 108 AS ID , Convert(VARCHAR(30), GETDATE(), 108) AS result UNION ALL SELECT 9 AS ID , Convert(VARCHAR(30), GETDATE(), 9 ) AS result UNION ALL SELECT 109 AS ID , Convert(VARCHAR(30), GETDATE(), 109) AS result UNION ALL SELECT 10 AS ID , Convert(VARCHAR(30), GETDATE(), 10 ) AS result UNION ALL SELECT 110 AS ID , Convert(VARCHAR(30), GETDATE(), 110) AS result UNION ALL SELECT 11 AS ID , Convert(VARCHAR(30), GETDATE(), 11 ) AS result UNION ALL SELECT 111 AS ID , Convert(VARCHAR(30), GETDATE(), 111) AS result UNION ALL SELECT 12 AS ID , Convert(VARCHAR(30), GETDATE(), 12 ) AS result UNION ALL SELECT 112 AS ID , Convert(VARCHAR(30), GETDATE(), 112) AS result UNION ALL SELECT 13 AS ID , Convert(VARCHAR(30), GETDATE(), 13 ) AS result UNION ALL SELECT 113 AS ID , Convert(VARCHAR(30), GETDATE(), 113) AS result UNION ALL SELECT 14 AS ID , Convert(VARCHAR(30), GETDATE(), 14 ) AS result UNION ALL SELECT 114 AS ID , Convert(VARCHAR(30), GETDATE(), 114) AS result UNION ALL SELECT 20 AS ID , Convert(VARCHAR(30), GETDATE(), 20 ) AS result UNION ALL SELECT 120 AS ID , Convert(VARCHAR(30), GETDATE(), 120) AS result UNION ALL SELECT 21 AS ID , Convert(VARCHAR(30), GETDATE(), 21 ) AS result UNION ALL SELECT 121 AS ID , Convert(VARCHAR(30), GETDATE(), 121) AS result ID result ----------- ------------------------------ 0 03 19 2011 8:24PM 100 03 19 2011 8:24PM 1 03/19/11 101 03/19/2011 2 11.03.19 南無阿弥陀佛 82 102 2011.03.19 2 11.03.19 102 2011.03.19 3 19/03/11 103 19/03/2011 4 19.03.11 104 19.03.2011 5 19-03-11 105 19-03-2011 6 19 03 11 106 19 03 2011 7 03 19, 11 107 03 19, 2011 8 20:24:27 108 20:24:27 9 03 19 2011 8:24:27:840PM 109 03 19 2011 8:24:27:840PM 10 03-19-11 110 03-19-2011 11 11/03/19 111 2011/03/19 12 110319 112 20110319 13 19 03 2011 20:24:27:840 113 19 03 2011 20:24:27:840 14 20:24:27:840 114 20:24:27:840 20 2011-03-19 20:24:27 120 2011-03-19 20:24:27 21 2011-03-19 20:24:27.840 121 2011-03-19 20:24:27.840 (36 行受影响) MySQL mysql> SELECT -> DATE_FORMAT(NOW(), '%Y~%m~%d %k.%i.%s' ) A; +---------------------+ | A | +---------------------+ | 2010~10~22 20.55.09 | +---------------------+ 1 row in set (0.00 sec) %W 星期名字(Sunday¡¡ Saturday) %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。) %Y 年, 数字, 4 位 南無阿弥陀佛 83 %y 年, 数字, 2 位 %a 缩写的星期名字(Sun¡¡ Sat) %d 月份中的天数, 数字(00¡¡ 31) %e 月份中的天数, 数字(0¡¡ 31) %m 月, 数字(01¡¡ 12) %c 月, 数字(1¡¡ 12) %b 缩写的月份名字(Jan¡¡ Dec) %j 一年中的天数(001¡¡ 366) %H 小时(00¡¡ 23) %k 小时(0¡¡ 23) %h 小时(01¡¡ 12) %I 小时(01¡¡ 12) %l 小时(1¡¡ 12) %i 分钟, 数字(00¡¡ 59) %r 时间,12 小时(hh:mm:ss [AP]M) %T 时间,24 小时(hh:mm:ss) %S 秒(00¡¡ 59) %s 秒(00¡¡ 59) %p AM 或 PM %w 一个星期中的天数(0=Sunday ¡¡ 6=Saturday ) %U 星期(0¡¡ 52), 这里星期天是星期的第一天 %u 星期(0¡¡ 52), 这里星期一是星期的第一天 %% 一个文字¡%¡。 所有的其他字符不做解释被复制到结果中。 日期/时间的格式化[字符串转换为日期] Oracle SQL> SELECT 2 TO_DATE('2010.10.01', 'YYYY.MM.DD') 3 FROM 4 dual; TO_DATE('2 ---------- 2010.10.01 SQL Server 1> SELECT 2> CONVERT(DATETIME, '2012-01-02 09:06:18', 120) 3> go ----------------------- 2012-01-02 09:06:18.000 南無阿弥陀佛 84 (1 行受影响) MySQL mysql> SELECT -> STR_TO_DATE('2010~10~22 20.55.09', -> '%Y~%m~%d %k.%i.%s' ) A; +---------------------+ | A | +---------------------+ | 2010-10-22 20:55:09 | +---------------------+ 1 row in set (0.00 sec) 时间/秒转换 MySQL mysql> SELECT SEC_TO_TIME (3600); +--------------------+ | SEC_TO_TIME (3600) | +--------------------+ | 01:00:00 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT TIME_TO_SEC('00:01:00') A; +----+ | A | +----+ | 60 | +----+ 1 row in set (0.00 sec) 新建日期/时间 MySQL mysql> SELECT MAKEDATE (2010, 100) A; +------------+ | A | +------------+ | 2010-04-10 | +------------+ 南無阿弥陀佛 85 1 row in set (0.00 sec) mysql> mysql> SELECT MAKETIME (10, 20, 30) A; +----------+ | A | +----------+ | 10:20:30 | +----------+ 1 row in set (0.00 sec) 时区相关 Oracle 查看数据库时区 SQL> SELECT DBTIMEZONE FROM DUAL; DBTIME ------ +00:00 ALTER SESSION SET TIME_ZONE = '-5:0'; 修改会话时区 DATE 类型不包含时区信息 TIMESTAMP 类型包含时区信息 SQL> SELECT 2 SESSIONTIMEZONE, 3 CURRENT_DATE, 4 CURRENT_TIMESTAMP, 5 SYSDATE 6 FROM 7 DUAL; SESSIONTIMEZONE ----------------------------------------- CURRENT_DATE ------------------- CURRENT_TIMESTAMP ----------------------------------------- SYSDATE ------------------- 南無阿弥陀佛 86 -05:00 2011-06-14 07:31:20 14-6 月 -11 07.31.19.593000 上午 -05:00 2011-06-14 20:31:19 FROM_TZ 用于为时间标记上时区信息。 SQL> SELECT 2 FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00') 3 FROM DUAL; FROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00') -------------------------------------------------------- 28-3 月 -00 08.00.00.000000000 上午 +03:00 SQL Server SQL Server 2008 新增 SWITCHOFFSET 与 TODATETIMEOFFSET SWITCHOFFSET 更改 DATETIMEOFFSET 值的时区偏移量并保留 UTC 值。 1> SELECT 2> '+8' AS [时区], 3> SYSDATETIMEOFFSET() AS [时间] 4> UNION ALL 5> SELECT 6> '+9' AS [时区], 7> SWITCHOFFSET(SYSDATETIMEOFFSET(), '+09:00') AS [时间] 8> SELECT 9> '-8' AS [时区], 10> SWITCHOFFSET(SYSDATETIMEOFFSET(), '-08:00') AS [时间]; 11> go 时区 时间 -- --------------------------------------------- +8 2011-05-23 13:21:14.5740028 +08:00 +9 2011-05-23 14:21:14.5740028 +09:00 (2 行受影响) 时区 时间 -- --------------------------------------------- -8 2011-05-22 21:21:14.5740028 -08:00 (1 行受影响) TODATETIMEOFFSET 将 datetime2 值转换为 datetimeoffset 值。datetime2 值被解释为指定 time_zone 的本地时间。 南無阿弥陀佛 87 1> SELECT 2> SYSDATETIME() AS [不带时区的时间], 3> TODATETIMEOFFSET( SYSDATETIME(), '+08:00') AS [+8 时区的时间] 4> go 不带时区的时间 +8 时区的时间 -------------------------- ----------------------------- 2011-05-23 13:14:25.6626145 2011-05-23 13:14:25.6626145 +08:00 (1 行受影响) MySQL mysql> SELECT CONVERT_TZ (NOW(), '+8:00', '+9:00') A; +---------------------+ | A | +---------------------+ | 2010-10-22 22:10:18 | +---------------------+ 1 row in set (0.00 sec) 一般比较函数 取得最大值 GREATEST Oracle SQL> SELECT 2 GREATEST(1,3,5,7,9) A 3 FROM 4 dual; A ---------- 9 SQL> select greatest('aa', 'bb', 'cc') A 2 from dual; A -- cc MySQL mysql> SELECT GREATEST (1,3,5,7,9) A; 南無阿弥陀佛 88 +---+ | A | +---+ | 9 | +---+ 1 row in set (0.02 sec) mysql> select greatest('aa', 'bb', 'cc') A -> ; +----+ | A | +----+ | cc | +----+ 1 row in set (0.00 sec) 取得最小值 LEAST Oracle SQL> SELECT 2 LEAST(1,3,5,7,9) A 3 FROM 4 dual; A ---------- 1 SQL> select least('aa', 'bb', 'cc') A 2 from dual; A -- Aa MySQL mysql> SELECT LEAST (1,3,5,7,9) A -> // +---+ | A | +---+ | 1 | +---+ 南無阿弥陀佛 89 1 row in set (0.00 sec) mysql> select least('aa', 'bb', 'cc') A -> ; +----+ | A | +----+ | aa | +----+ 1 row in set (0.00 sec) 数据字典类函数 返回列名 COL_NAME SQL Server COL_NAME ( table_id , column_id ) 参数 table_id 包含列的表的标识 号。table_id 的类 型为 int。 column_id 列 的 标 识 号 。 column_id 参数的 类型为 int。 1> SELECT 2> COL_NAME(OBJECT_ID('t1'), 1), 3> COL_NAME(OBJECT_ID('t1'), 2), 4> COL_NAME(OBJECT_ID('t1'), 3), 5> COL_NAME(OBJECT_ID('t1'), 4) 6> GO id c1 c2 c3 (1 行受影响) 列属性 COLUMNPROPERTY 南無阿弥陀佛 90 SQL Server COLUMNPROPERTY ( id , column , property ) 参数 id 一个表达式,其中包含表或过程的标识符 (ID)。 column 一个表达式,其中包含列或参数的名称。 property 一个表达式,其中包含要为 id 返回的信息 数据库选项设置 DATABASEPROPERTYEX SQL Server DATABASEPROPERTYEX ( database , property ) 参数 database 表示要为其返回命名属性信息的数据库的名称。 database 的数据类型为 nvarchar(128)。 property 表示要返回的数据库属性的名称的表达式。 property 的数据类型为 varchar(128) 返回类型为 sql_variant。 数据库中的主体的 ID 号 DATABASE_PRINCIPAL_ID SQL Server DATABASE_PRINCIPAL_ID ( principal_name ) 参数 principal_name sysname 类型的表达式,表示数据库主体。 如果省略 principal_name,则返回当前用户的 ID。必须加上圆括号。 南無阿弥陀佛 91 取得数据库标识号 DB_ID(SQL Server Only) SQL Server 1> select db_id(), db_name() 2> go ------------------------------------- 5 Test (1 行受影响) 取得数据库名 DB_NAME(SQL Server Only) SQL Server 1> select db_id(), db_name() 2> go ------------------------------------- 5 Test (1 行受影响) 取得文件标识号 FILE_IDEX SQL Server FILE_IDEX ( file_name ) 参数 file_name 一个 sysname 类型的表达式,表示要返回文件 ID 的文件的名称。 取得逻辑文件名 FILE_NAME SQL Server FILE_NAME ( file_id ) 参数 file_id 是要返回其文件名的文件标识号。file_id 的数据类型为 int。 返回类型 nvarchar(128) 备注 file_ID 与 sys.master_files 或 sys.database_files 目录视图中的 file_id 列相对 应。 南無阿弥陀佛 92 取得文件组标识号 FILEGROUP_ID SQL Server FILEGROUP_ID ( 'filegroup_name' ) 参数 ' filegroup_name ' sysname 类型的表达式,表示要为其返回文件组 ID 的文件组名。 取得文件组名 FILEGROUP_NAME SQL Server FILEGROUP_NAME ( filegroup_id ) 参数 filegroup_id 要返回文件组名的文件组 ID 号。filegroup_id 的数据类型为 smallint。 读取文件属性 FILEPROPERTY SQL Server FILEPROPERTY ( file_name , property ) 参数 file_name 包含与将为之返回属性信息的当前数据库相关联的文件名的表达式。file_name 的数据类型为 nchar(128)。 property 包含将返回的文件属性名的表达式。property 的数据类型为 varchar(128) 取得工作站标识号 HOST_ID (SQL Server Only) Oracle SQL Server 1> SELECT HOST_ID(), HOST_NAME() 2> go ---------- ----------------- 南無阿弥陀佛 93 4248 HOME-BED592453C (1 行受影响) 取得工作站名称 HOST_NAME(SQL Server Only) Oracle SQL Server 1> SELECT HOST_ID(), HOST_NAME() 2> go ---------- ----------------- 4248 HOME-BED592453C (1 行受影响) 返回定义的源文本 OBJECT_DEFINITION SQL Server OBJECT_DEFINITION ( object_id ) 参数 object_id 要使用的对象的 ID。object_id 的数据类型为 int,并假定表示当前数据库上下 文中的对象。 SQL Server 数据库引擎 假定 object_id 位于当前数据库上下文中。对象定义的 排序规则始终与调用数据库上下文的排序规则匹配。 OBJECT_DEFINITION 适用于以下对象类型: C = 检查约束 D = 默认值(约束或独立) P = SQL 存储过程 FN = SQL 标量函数 R = 规则 RF = 复制筛选器过程 TR = SQL 触发器(架构范围内的 DML 触发器,或数据库或服务器范围内的 DDL 触发器) IF = SQL 内联表值函数 TF = SQL 表值函数 V = 视图 南無阿弥陀佛 94 返回对象标识 OBJECT_ID SQL Server 使用 OBJECT_ID 不能查询非架构范围内的对象(如 DDL 触发器)。对于在 sys.objects 目录视图中找不到的对象,需要通过查询适当的目录视图来获取该 对象的标识号。例如,若要返回 DDL 触发器的对象标识号,请使用 SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog' OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ] object_name' [ ,'object_type' ] ) 参数 ' object_name ' 要 使 用 的 对 象 。 object_name 的 数 据 类 型 为 varchar 或 nvarchar 。 如 果 object_name 的数据类型为 varchar,则它将隐式转换为 nvarchar。可以选择是 否指定数据库和架构名称。 ' object_type ' 架构范围的对象类型。object_type 的数据类型为 varchar 或 nvarchar。如果 object_type 的数据类型为 varchar,则它将隐式转换为 nvarchar。有关对象类 型的列表,请参阅 sys.objects (Transact-SQL) 中的 type 列。 返回对象名称 OBJECT_NAME SQL Server OBJECT_NAME ( object_id [, database_id ] ) 参数 object_id 要使用的对象的 ID。object_id 的数据类型为 int,并假定为指定数据库或当前 数据库上下文中的架构范围内的对象。 database_id 要在其中查找对象的数据库的 ID。database_id 的数据类型为 int。 返回对象构架名 OBJECT_SCHEMA_NAME 南無阿弥陀佛 95 SQL Server OBJECT_SCHEMA_NAME ( object_id [, database_id ] ) 参数 object_id 要使用的对象的 ID。object_id 的数据类型为 int,并假定为指定数据库或当前 数据库上下文中的架构范围内的对象。 database_id 要在其中查找对象的数据库的 ID。database_id 的数据类型为 int。 对象的有关信息 OBJECTPROPERTY SQL Server OBJECTPROPERTY ( id , property ) 参数 id 是表示当前数据库中对象 ID 的表达式。id 的数据类型为 int,并假定为当前 数据库上下文中的架构范围内的对象。 property 一个表达式,提供 id 指定的对象的返回信息。 对象的有关信息 OBJECTPROPERTYEX SQL Server OBJECTPROPERTYEX ( id , property ) 参数 id 是表示当前数据库中对象 ID 的表达式。id 的数据类型为 int,并假定为当前 数据库上下文中的架构范围内的对象。 property 一个表达式,包含要为 ID 所指定的对象返回的信息。 获取当前用户可用权限 PERMISSIONS SQL Server PERMISSIONS ( [ objectid [ , 'column' ] ] ) 参数 objectid 南無阿弥陀佛 96 安全对象的 ID。如果未指定 objectid,则位图值包含当前用户的语句权限;否 则,位图包含当前用户对该安全对象的权限。指定的安全对象必须在当前数据 库中。使用 OBJECT_ID 函数确定 objectid 值。 ' column ' 返回其权限信息的列的可选名。该列必须是 objectid 指定的表中的有效列名。 获取架构名称 SCHEMA_ID SQL Server SCHEMA_ID( [ schema_name ] ) 参数 schema_name 架 构 的 名 称 。 schema_name 的 数 据 类 型 为 sysname 。 如 果 未 指 定 schema_name,则 SCHEMA_ID 将返回调用方的默认架构的 ID。 1> SELECT SCHEMA_ID(), SCHEMA_ID('Work'), SCHEMA_ID('Report') 2> go ----------- ----------- ----------- 1 5 6 (1 行受影响) 获取架构名称 SCHEMA_NAME SQL Server SCHEMA_NAME ( [ schema_id ] ) schema_id 架构的 ID。schema_id 的数据类型为 int。如果没有定义 schema_id,则 SCHEMA_NAME 将返回调用方的默认架构的名称。 1> SELECT SCHEMA_NAME(), SCHEMA_NAME(5), SCHEMA_NAME(6) 2> go ------------------------------ dbo Work Report 获取服务器实例的属性信息 SERVERPROPERTY SQL Server SERVERPROPERTY ( propertyname ) 南無阿弥陀佛 97 参数 propertyname 包含要返回的服务器属性信息的表达式。 返回会话的 SET 选项设置 SESSIONPROPERTY SQL Server SESSIONPROPERTY (option) 参数 option 该会话的当前选项设置 返回上次更新指定索引的统计信息的日期 STATS_DATE SQL Server SELECT OBJECT_NAME(object_id) AS [表名], name AS [索引名称], STATS_DATE(object_id, index_id) AS [最近统计日期] FROM sys.indexes 返回用户登录名的安全标识号 (SID) SUSER_SID(SQL Server Only) Oracle SQL Server 1> SELECT SUSER_SID('sa') 2> go ------- 0x01 (1 行受影响) 1> SELECT SUSER_SID() 2> go ------- 0x010500000000000515000000A837D665F239B634828BA628EB030000 (1 行受影响) 南無阿弥陀佛 98 从用 户的安全 标识号 (SID) 返回登 录标识名 SUSER_SNAME(SQL Server Only) Oracle SQL Server 1> SELECT SUSER_SNAME() 2> go -------------------------------------------- HOME-BED592453C\wzq (1 行受影响) 1> SELECT SUSER_SNAME(0x01) 2> go -------------------------------------------- sa (1 行受影响) 取得用户 ID UID / USER_ID Oracle SQL> select uid from dual; UID ---------- 33 SQL> SQL Server 当 省 略参 数 user 时,则假定为当前 用户。必须加上圆 括号。 1> SELECT USER_ID(), USER_ID('Demo') 2> GO ------ ------ 1 5 (1 行受影响) MySQL mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ 南無阿弥陀佛 99 | 2 | +-----------------+ 1 row in set (0.00 sec) 取得用户名 USER_NAME SQL Server 省略 id 时,则假 定为当前上下文中 的当前用户。 1> SELECT USER_NAME(), USER_NAME ( USER_ID('Demo') ) 2> GO ----------------- dbo Demo (1 行受影响) 获取数据类型名称的编号 TYPE_ID SQL Server TYPE_ID ( [ schema_name ] type_name ) 参数 type_name 数据类型的名称。type_name 的数据类型为 nvarchar。type_name 可以是系统 数据类型或用户定义的数据类型。 获取指定类型 ID 的未限定的类型名称 TYPE_NAME SQL Server TYPE_NAME ( type_id ) 参数 type_id 要使用的类型的 ID。type_id 的数据类型为 int,它可以引用调用方有权访问 的任意架构中的类型。 返回有关数据类型的信息 TYPEPROPERTY SQL Server TYPEPROPERTY (type , property) 参数 type 数据类型的名称。 property 南無阿弥陀佛 100 要为数据类型返回的信息类型。 南無阿弥陀佛 101 其他函数 取得客户端应用程序名字 APP_NAME SQL Server E:\>sqlcmd -S "localhost\SQLEXPRESS" 1> SELECT APP_NAME() 2> go --------------------------------------------- SQLCMD (1 行受影响) 1> exit E:\>osql -E -S "localhost\SQLEXPRESS" 1> SELECT APP_NAME() 2> go -------------------------------------------- OSQL-32 (1 行受影响) 1> exit 数据校验 BINARY_CHECKSUM SQL Server 返回按照表的某一行或表达式列表计算的二进制校验和值 。 BINARY_CHECKSUM 可用于检测表中行的更改。 1> CREATE TABLE #myTable (id int, col2 varchar(10)); 2> GO 1> INSERT INTO #myTable VALUES (1, 'test'); 2> INSERT INTO #myTable VALUES (2, 'test2'); 3> GO (1 行受影响) 1> SELECT 2> BINARY_CHECKSUM(*), 3> BINARY_CHECKSUM(id), 4> BINARY_CHECKSUM(col2) 5> FROM 南無阿弥陀佛 102 6> #myTable; 7> GO ----------- ----------- ----------- 467540 1 467524 7480402 2 7480434 (2 行受影响) 1> 2> UPDATE #myTable 3> SET col2 = 'TEST' 4> WHERE id = 1; 5> GO (1 行受影响) 1> 2> SELECT 3> BINARY_CHECKSUM(*), 4> BINARY_CHECKSUM(id), 5> BINARY_CHECKSUM(col2) 6> FROM 7> #myTable; 8> GO ----------- ----------- ----------- 327796 1 327780 7480402 2 7480434 (2 行受影响) 类型转换 CAST Oracle 数字转字符 SQL> SELECT TO_CHAR(1, '000') AS test FROM dual; TEST -------- 001 SQL> SELECT TO_CHAR(1234567, '999,999,999') AS test FROM dual; TEST 南無阿弥陀佛 103 ------------------------ 1,234,567 字符转数字 SQL> SELECT TO_NUMBER('1') + 1 AS test FROM dual; TEST ---------- 2 SQL> SELECT TO_NUMBER('1,234,567', '999,999,999') + 1 AS test FROM dual; TEST ---------- 1234568 SQL Server 数字转换为 varchar 1> SELECT CAST(1 as varchar) 2> go ------------------------------ 1 (1 行受影响) 1> SELECT Convert(varchar(1), 1) + '1' 2> go -- 11 (1 行受影响) 字符转换为 int 1> SELECT CAST('1' as int) + 1 2> go ----------- 2 (1 行受影响) 1> SELECT Convert(int, '1') + 1 2> go ----------- 南無阿弥陀佛 104 2 (1 行受影响) 日期转字符 (建议使用 Convert 去转换) 1> SELECT CAST(getdate() as varchar) 2> go ------------------------------ 01 29 2011 1:39PM (1 行受影响) MySQL 注: 可用的类型 二进制,同带 binary 前 缀 的 效 果 : BINARY 字符型,可带参数 : CHAR() 日期 : DATE 时间: TIME 日 期 时 间 型 : DATETIME 浮 点 数 : DECIMAL 整数 : SIGNED 无 符 号 整 数 : UNSIGNED 数字转字符 mysql> SELECT CONCAT ( CAST(1 as char) , '2') AS test; +------+ | test | +------+ | 12 | +------+ 1 row in set (0.00 sec) mysql> SELECT CONCAT ( Convert(1, char) , '2') AS test; +------+ | test | +------+ | 12 | +------+ 1 row in set (0.00 sec) 字符转数字 mysql> SELECT CAST('1' as SIGNED) + 100 AS test; +------+ | test | +------+ | 101 | +------+ 1 row in set (0.00 sec) mysql> SELECT Convert('1' , SIGNED) + 100 AS test; +------+ | test | +------+ | 101 | +------+ 1 row in set (0.00 sec) 南無阿弥陀佛 105 字符串转换为行编号 CHARTOROWID (Oracle Only) Oracle SQL> select * from test_main; ID VALUE ---------- ---------- 1 ONE1 2 TWO1 3 THREE1 SQL> select 2 ROWIDTOCHAR(rowid) 3 from 4 test_main; ROWIDTOCHAR(ROWID) ------------------ AAAM2bAAGAAAAAOAAA AAAM2bAAGAAAAAPAAA AAAM2bAAGAAAAAPAAB SQL> select * from test_main 2 where rowid = CHARTOROWID('AAAM2bAAGAAAAAOAAA'); ID VALUE ---------- ---------- 1 ONE1 数据校验 CHECKSUM SQL Server 返回按照表的某一行或一组表达式计算出来的校验和值。CHECKSUM 用于生 成哈希索引。 1> DROP TABLE #myTable; 南無阿弥陀佛 106 2> CREATE TABLE #myTable (id int, col2 varchar(10)); 3> GO 1> INSERT INTO #myTable VALUES (1, 'test'); 2> INSERT INTO #myTable VALUES (2, 'test2'); 3> GO (1 行受影响) 1> SELECT 2> CHECKSUM (*), 3> CHECKSUM (id), 4> CHECKSUM (col2) 5> FROM 6> #myTable; 7> GO ----------- ----------- ----------- -1854252689 1 -1854252673 1586913175 2 1586913207 (2 行受影响) 1> 2> UPDATE #myTable 3> SET col2 = 'TEST' 4> WHERE id = 1; 5> GO (1 行受影响) 1> 2> SELECT 3> CHECKSUM (*), 4> CHECKSUM (id), 5> CHECKSUM (col2) 6> FROM 7> #myTable; 8> GO ----------- ----------- ----------- -1854252689 1 -1854252673 1586913175 2 1586913207 (2 行受影响) 注:这里看到,默认情况下,大小写不影响 CheckSum 结果。 南無阿弥陀佛 107 数据校验 CHECKSUM_AGG SQL Server 对于 CHECKSUM 与 CHECKSUM_AGG 可以理解为前者是 检查行的。后者是 检查列的。 CHECKSUM_AGG 可用于检测表中的更改。 表中行的顺序不影响 CHECKSUM_AGG 的结果。 此外,CHECKSUM_AGG 函数还可与 DISTINCT 关键字和 GROUP BY 子句 一起使用。 如果表达式列表中的某个值发生更改,则列表的校验和通常也会更改。 但只在极少数情况下,校验值会保持不变。 1> DROP TABLE #myTable; 2> CREATE TABLE #myTable (id INT, val INT); 3> GO 1> INSERT INTO #myTable VALUES (1, 100); 2> INSERT INTO #myTable VALUES (2, 125); 3> GO (1 行受影响) 1> SELECT 2> CHECKSUM_AGG(val) 3> FROM 4> #myTable; 5> GO ----------- 25 (1 行受影响) 1> 2> UPDATE #myTable 3> SET val = 150 4> WHERE id = 1; 5> GO (1 行受影响) 1> 2> SELECT 3> CHECKSUM_AGG(val) 4> FROM 5> #myTable; 6> GO ----------- 235 南無阿弥陀佛 108 (1 行受影响) 返回列的定义的长度 COL_LENGTH SQL Server 1> CREATE TABLE #t1( 2> id INT, 3> c1 varchar(40), 4> c2 nvarchar(40) 5> ); 6> GO 1> 2> SELECT 3> COL_LENGTH('#t1', 'id'), 4> COL_LENGTH('#t1', 'c1'), 5> COL_LENGTH('#t1', 'c2') 6> GO ------ ------ ------ NULL NULL NULL (1 行受影响) 1> CREATE TABLE t1( 2> id INT, 3> c1 varchar(40), 4> c2 nvarchar(40), 5> c3 varchar(MAX) 6> ); 7> GO 1> 2> SELECT 3> COL_LENGTH('t1', 'id'), 4> COL_LENGTH('t1', 'c1'), 5> COL_LENGTH('t1', 'c2'), 6> COL_LENGTH('t1', 'c3') 7> GO ------ ------ ------ ------ 4 40 80 -1 南無阿弥陀佛 109 (1 行受影响) 条件判断函数 DECODE (Oracle Only) Oracle DECODE 限 制 最 大 25 个参数 SQL> SELECT 2 DECODE(GROUPING(sale_item), 1, 'ALL', sale_item) AS iten, 3 SUM(sale_money) AS money 4 FROM 5 sale_report 6 GROUP BY 7 ROLLUP(sale_item); ITEN MONEY ------ ---------- A 733285 B 2382 C 5738 ALL 741405 SQL Server SQL Server 中 可 以 使 用 CASE WHEN 来 解 决 SELECT 语 句 中 条件判断的问题。 1> SELECT 2> CASE 3> WHEN GROUPING(sale_item) = 1 4> THEN '总' 5> ELSE 6> sale_item 7> END AS item, 8> SUM(sale_money) [money] 9> FROM 10> sale_report 11> GROUP BY 12> sale_item WITH ROLLUP; 13> go item money ---- ---------------------------------------- A 733285.00 B 2382.00 C 5738.00 总 741405.00 (4 行受影响) MySQL mysql> SELECT IF(1>2, 2, 3) A; 南無阿弥陀佛 110 +---+ | A | +---+ | 3 | +---+ 1 row in set (0.00 sec) mysql> SELECT -> CASE WHEN 1 > 2 THEN 2 -> ELSE 3 -> END A; +---+ | A | +---+ | 3 | +---+ 1 row in set (0.00 sec) 非空判断 NVL / ISNULL Oracle SQL> SELECT 2 NVL(sale_item, 'ALL') AS item, 3 SUM(sale_money) AS money 4 FROM 5 sale_report 6 GROUP BY 7 ROLLUP(sale_item); ITEM MONEY ------ ---------- A 733285 B 2382 C 5738 ALL 741405 SQL Server 1> SELECT 2> ISNULL(sale_item, '总') AS item, 3> SUM(sale_money) [money] 4> FROM 5> sale_report 6> GROUP BY 7> sale_item WITH ROLLUP; 8> go item money 南無阿弥陀佛 111 ---- ---------------------------------------- A 733285.00 B 2382.00 C 5738.00 总 741405.00 (4 行受影响) MySQL MySQL 的 ISNULL, 只判断 是 NULL 返回 1 不是 NULL 返回 0 mysql> SELECT ISNULL(1+1); +-------------+ | ISNULL(1+1) | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) 要使用 类似 如果空,用另外一个值得函数,使用 IFNULL mysql> SELECT IFNULL(NULL, 'Hi') A; +----+ | A | +----+ | Hi | +----+ 1 row in set (0.00 sec) 非空判断 NVL2 Oracle NVL2 (expr1, expr2, expr3) expr1 不为 NULL, 返回 expr2; 为 NULL, 返 回 expr3。 SQL> SELECT 2 NVL2(sale_item, '[' || sale_item || ']', 'ALL') AS item, 3 SUM(sale_money) AS money 4 FROM 5 sale_report 6 GROUP BY 7 ROLLUP(sale_item); ITEM MONEY ---- ---------- [A] 365 [B] 730 [C] 1095 ALL 2190 南無阿弥陀佛 112 取得第一个非空数据 COALESCE Oracle SQL> SELECT 2 COALESCE(10*1, NULL, NULL, NULL, NULL) A, 3 COALESCE(NULL, 10*2, NULL, NULL, NULL) B, 4 COALESCE(NULL, NULL, 10*3, NULL, NULL) C, 5 COALESCE(NULL, NULL, NULL, 10*4, NULL) D, 6 COALESCE(NULL, NULL, NULL, NULL, 10*5) E 7 FROM 8 dual; A B C D E ---------- ---------- ---------- ---------- ---------- 10 20 30 40 50 SQL Server 1> SELECT 2> COALESCE(10*1, NULL, NULL, NULL, NULL) A, 3> COALESCE(NULL, 10*2, NULL, NULL, NULL) B, 4> COALESCE(NULL, NULL, 10*3, NULL, NULL) C, 5> COALESCE(NULL, NULL, NULL, 10*4, NULL) D, 6> COALESCE(NULL, NULL, NULL, NULL, 10*5) E 7> go A B C D E ----------- ----------- ----------- ----------- ----------- 10 20 30 40 50 (1 行受影响) MySQL mysql> SELECT -> COALESCE(10*1, NULL, NULL, NULL, NULL) A, -> COALESCE(NULL, 10*2, NULL, NULL, NULL) B, -> COALESCE(NULL, NULL, 10*3, NULL, NULL) C, -> COALESCE(NULL, NULL, NULL, 10*4, NULL) D, -> COALESCE(NULL, NULL, NULL, NULL, 10*5) E; +------+------+------+------+------+ | A | B | C | D | E | +------+------+------+------+------+ | 10 | 20 | 30 | 40 | 50 | +------+------+------+------+------+ 1 row in set (0.00 sec) 南無阿弥陀佛 113 取得字段所占用字节数 VSIZE / DATALENGTH Oracle SQL> SELECT 2 VSIZE(SYSDATE) A, 3 VSIZE(user) B 4 FROM 5 dual; A B ---------- ---------- 7 2 SQL Server 1> SELECT 2> DATALENGTH(GETDATE()) A, 3> DATALENGTH(user) B 4> go A B ----------- ----------- 8 6 (1 行受影响) 判断是否是日期 ISDATE (SQL Server Only) Oracle SQL Server 1> SELECT 'NULL' AS [DATA], ISDATE(NULL) AS [ISDATE] 2> UNION ALL 3> SELECT 'ABC' AS [DATA], ISDATE('Abc') AS [ISDATE] 4> UNION ALL 5> SELECT '100' AS [DATA], ISDATE('100') AS [ISDATE] 6> UNION ALL 7> SELECT '-100' AS [DATA], ISDATE('-100') AS [ISDATE] 8> UNION ALL 9> SELECT '.01' AS [DATA], ISDATE('.01') AS [ISDATE] 10> UNION ALL 11> SELECT '1995-10-1' AS [DATA], ISDATE('1995-10-1') AS [ISDATE] 12> UNION ALL 13> SELECT '1/20/95' AS [DATA], ISDATE('1/20/95') AS [ISDATE] 14> UNION ALL 15> SELECT '1995-10-1 13:00' AS [DATA], ISDATE('1995-10-1 13:00') AS [ISDATE] 南無阿弥陀佛 114 16> 17> go DATA ISDATE --------------- ----------- NULL 0 ABC 0 100 0 -100 0 .01 0 1995-10-1 1 1/20/95 0 1995-10-1 13:00 1 (8 行受影响) 判断是否是数字 ISNUMERIC(SQL Server Only) Oracle SQL Server 1> SELECT 'NULL' AS [DATA], ISNUMERIC(NULL) AS [ISNUMERIC] 2> UNION ALL 3> SELECT 'ABC' AS [DATA], ISNUMERIC('Abc') AS [ISNUMERIC] 4> UNION ALL 5> SELECT '100' AS [DATA], ISNUMERIC('100') AS [ISNUMERIC] 6> UNION ALL 7> SELECT '-100' AS [DATA], ISNUMERIC('-100') AS [ISNUMERIC] 8> UNION ALL 9> SELECT '.01' AS [DATA], ISNUMERIC('.01') AS [ISNUMERIC] 10> UNION ALL 11> SELECT '1995-10-1' AS [DATA], ISNUMERIC('1995-10-1') AS [ISNUMERI C] 12> UNION ALL 13> SELECT '1/20/95' AS [DATA], ISNUMERIC('1/20/95') AS [ISNUMERIC] 14> UNION ALL 15> SELECT '1995-10-1 13:00' AS [DATA], ISNUMERIC('1995-10-1 13:00') A S [ISNUMERIC] 南無阿弥陀佛 115 16> 17> go DATA ISNUMERIC --------------- ----------- NULL 0 ABC 0 100 1 -100 1 .01 1 1995-10-1 0 1/20/95 0 1995-10-1 13:00 0 (8 行受影响) 取得 GUID 号 SYS_GUID / NEWID Oracle SQL> SELECT SYS_GUID() FROM DUAL; SYS_GUID() -------------------------------- 727997912F054C7C81604C552FC3D7AE SQL> / SYS_GUID() -------------------------------- 2B0F8287C7A941FA8E16F9636E3CACA5 SQL Server 1> SELECT NEWID() 2> go ------------------------------------ 13D79F41-D088-4F12-8FAE-359112F0486D (1 行受影响) 1> SELECT NEWID() 2> go ------------------------------------ 954C83E2-137D-4692-842D-477ECFEBF890 (1 行受影响) 南無阿弥陀佛 116 注:在 SQL Server 当中,可以通过 SELECT TOP N FROM table ORDER BY newid() 进行随机获取表的前 N 行数据。 MySQL mysql> SELECT UUID(); +--------------------------------------+ | UUID() | +--------------------------------------+ | dd6a331c-dde8-11df-adb1-3e82d1946e6c | +--------------------------------------+ 1 row in set (0.00 sec) 如果两个指定的表达式相等,则返回空值 NULLIF Oracle SQL> SELECT 2 NULLIF(1,2) A, 3 NULLIF(1,1) B 4 FROM 5 dual; A B ---------- ---------- 1 SQL Server 1> SELECT NULLIF(1,2), NULLIF(1,1) 2> go --- --- 1 NUL (1 行受影响) MySQL mysql> SELECT NULLIF(1,2) A, NULLIF(1,1) B; +------+------+ | A | B | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.00 sec) 南無阿弥陀佛 117 取指定位置的数据 ELT (MySQL Only) MySQL ELT(N,str1,str2,str3,...) 若 N = 1,则返回值为 str1 ,若 N = 2,则返回值为 str2 ,以此类推。 若 N 小于 1 或大于参数的数目,则返回值为 NULL 。 ELT() 是 FIELD()的补 数。 mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo') A; +------+ | A | +------+ | ej | +------+ 1 row in set (0.00 sec) mysql> mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo') A; +------+ | A | +------+ | foo | +------+ 1 row in set (0.00 sec) 取数据的位置 FIELD (MySQL Only) MySQL FIELD(str,str1,str2,str3,...) 返回值为 str1, str2, str3,¡¡ 列表中的 str 指数。在找不到 str 的情况下,返回值 为 0 。 如果所有对于 FIELD() 的参数均为字符串,则所有参数均按照字符串进行比 较。如果所有的参数均为数字,则按照数字进行比较。否则,参数按照双倍进 行比较。 如果 str 为 NULL,则返回值为 0 ,原因是 NULL 不能同任何值进行同等比较。 FIELD() 是 ELT()的补数。 mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo') A; +---+ | A | +---+ | 2 | +---+ 1 row in set (0.00 sec) 南無阿弥陀佛 118 mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo') B; +---+ | B | +---+ | 0 | +---+ 1 row in set (0.00 sec) FIND_IN_SET (MySQL Only) MySQL FIND_IN_SET(str,strlist) 假如字符串 str 在由 N 子链组成的字符串列表 strlist 中, 则返回值的范围在 1 到 N 之间 。一个字符串列表就是一个由一些被¡,¡ 符号分开的自链组成的字 符串。如果第一个参数是一个常数字符串,而第二个是 type SET 列,则 FIND_IN_SET() 函数被优化,使用比特计算。如果 str 不在 strlist 或 strlist 为 空字符串,则返回值为 0 。如任意一个参数为 NULL,则返回值为 NULL。 这 个函数在第一个参数包含一个逗号(¡,¡)时将无法正常运行。 mysql> SELECT FIND_IN_SET('b','a,b,c,d') A; +---+ | A | +---+ | 2 | +---+ 1 row in set (0.00 sec) PARSENAME(SQL Server Only) PARSENAME 返回对象名称的指 定部分。可以检索 的对象部分有对象 名、所有者名称、 数据库名称和服务 器名称。 注意: PARSENAME 函 数不指示指定名称 PARSENAME ( 'object_name' , object_piece ) 'object_name' 要检索其指定部分的对象的名称。object_name 的数据类型为 sysname。此参数 是可选的限定对象名称。如果对象名称的所有部分都是限定的,则此名称可包 含四部分:服务器名称、数据库名称、所有者名称以及对象名称。 object_piece 要返回的对象部分。object_piece 的数据类型为 int 值,可以为下列值: 1 = 对象名称 2 = 架构名称 南無阿弥陀佛 119 的对象是否存在。 PARSENAME 仅 返回指定对象名称 的指定部分。 3 = 数据库名称 4 = 服务器名称 1> select parsename('A.B.C.D', 1) 2> go ---------------------------------------- ---------------------------------------- D (1 行受影响) 1> select parsename('A.B.C.D', 2) 2> go ---------------------------------------- ---------------------------------------- C 统计分析函数 测试表/数据 CREATE TABLE test_course ( student_name VARCHAR(10), -- 学生 course_name VARCHAR(10), -- 课程 grade INT -- 成绩 ); INSERT INTO test_course VALUES('甲', '语文', 95); INSERT INTO test_course VALUES('乙', '语文', 85); INSERT INTO test_course VALUES('丙', '语文', 75); INSERT INTO test_course VALUES('丁', '语文', 65); INSERT INTO test_course VALUES('戊', '语文', 55); INSERT INTO test_course VALUES('己', '语文', 50); INSERT INTO test_course VALUES('庚', '语文', 60); INSERT INTO test_course VALUES('辛', '语文', 70); INSERT INTO test_course VALUES('壬', '语文', 80); INSERT INTO test_course VALUES('奎', '语文', 90); 南無阿弥陀佛 120 INSERT INTO test_course VALUES('甲', '数学', 90); INSERT INTO test_course VALUES('乙', '数学', 80); INSERT INTO test_course VALUES('丙', '数学', 70); INSERT INTO test_course VALUES('丁', '数学', 60); INSERT INTO test_course VALUES('戊', '数学', 50); INSERT INTO test_course VALUES('己', '数学', 50); INSERT INTO test_course VALUES('庚', '数学', 60); INSERT INTO test_course VALUES('辛', '数学', 70); INSERT INTO test_course VALUES('壬', '数学', 85); INSERT INTO test_course VALUES('奎', '数学', 95); ROW_NUMBER 顺序编号 Oracle 按照分数 编号 从高到底 SQL> SELECT 2 ROW_NUMBER() OVER( ORDER BY SUM(grade) DESC) AS NO, 3 student_name, 4 SUM(grade) AS AllGrade 5 FROM 6 test_course 7 GROUP BY 8 student_name 9 ORDER BY 10 SUM(grade) DESC 11 ; NO STUDENT_NA ALLGRADE ---------- ---------- ---------- 1 奎 185 2 甲 185 3 乙 165 4 壬 165 5 丙 145 6 辛 140 7 丁 125 8 庚 120 9 戊 105 10 己 100 已选择 10 行。 按照分数 编号 从高到底(区分 课程) 南無阿弥陀佛 121 SELECT ROW_NUMBER() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO, test_course.student_name, test_course.course_name, test_course.grade FROM test_course ORDER BY course_name, grade DESC; NO STUDENT_NA COURSE_NAM GRADE ---------- ---------- ---------- ---------- 1 奎 数学 95 2 甲 数学 90 3 壬 数学 85 4 乙 数学 80 5 辛 数学 70 6 丙 数学 70 7 庚 数学 60 8 丁 数学 60 9 己 数学 50 10 戊 数学 50 1 甲 语文 95 NO STUDENT_NA COURSE_NAM GRADE ---------- ---------- ---------- ---------- 2 奎 语文 90 3 乙 语文 85 4 壬 语文 80 5 丙 语文 75 6 辛 语文 70 7 丁 语文 65 8 庚 语文 60 9 戊 语文 55 10 己 语文 50 已选择 20 行。 SQL Server 按照分数 编号 从高到底 SELECT ROW_NUMBER() OVER( ORDER BY SUM(grade) DESC) AS NO, student_name, SUM(grade) AS AllGrade FROM 南無阿弥陀佛 122 test_course GROUP BY student_name ORDER BY SUM(grade) DESC 1 甲 185 2 奎 185 3 壬 165 4 乙 165 5 丙 145 6 辛 140 7 丁 125 8 庚 120 9 戊 105 10 己 100 按照分数 编号 从高到底(区分 课程) SELECT ROW_NUMBER() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO, * FROM test_course ORDER BY course_name, grade DESC 1 奎 数学 95 2 甲 数学 90 3 壬 数学 85 4 乙 数学 80 5 丙 数学 70 6 辛 数学 70 7 庚 数学 60 8 丁 数学 60 9 戊 数学 50 10 己 数学 50 1 甲 语文 95 2 奎 语文 90 3 乙 语文 85 4 壬 语文 80 5 丙 语文 75 6 辛 语文 70 南無阿弥陀佛 123 7 丁 语文 65 8 庚 语文 60 9 戊 语文 55 10 己 语文 50 RANK 排名不连续 Oracle 按照分数 排名 从高到底 SELECT RANK() OVER( ORDER BY SUM(grade) DESC) AS NO, student_name, SUM(grade) AS AllGrade FROM test_course GROUP BY student_name ORDER BY SUM(grade) DESC NO STUDENT_NA ALLGRADE ---------- ---------- ---------- 1 奎 185 1 甲 185 3 乙 165 3 壬 165 5 丙 145 6 辛 140 7 丁 125 8 庚 120 9 戊 105 10 己 100 已选择 10 行。 按照分数 排名 从高到底(区分 课程) SELECT RANK() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO, test_course.student_name, test_course.course_name, test_course.grade FROM test_course 南無阿弥陀佛 124 ORDER BY course_name, grade DESC NO STUDENT_NA COURSE_NAM GRADE ---------- ---------- ---------- ---------- 1 奎 数学 95 2 甲 数学 90 3 壬 数学 85 4 乙 数学 80 5 辛 数学 70 5 丙 数学 70 7 庚 数学 60 7 丁 数学 60 9 己 数学 50 9 戊 数学 50 1 甲 语文 95 NO STUDENT_NA COURSE_NAM GRADE ---------- ---------- ---------- ---------- 2 奎 语文 90 3 乙 语文 85 4 壬 语文 80 5 丙 语文 75 6 辛 语文 70 7 丁 语文 65 8 庚 语文 60 9 戊 语文 55 10 己 语文 50 已选择 20 行。 SQL Server 按照分数 排名 从高到底 SELECT RANK() OVER( ORDER BY SUM(grade) DESC) AS NO, student_name, SUM(grade) AS AllGrade FROM test_course GROUP BY student_name ORDER BY SUM(grade) DESC 1 甲 185 1 奎 185 3 壬 165 南無阿弥陀佛 125 3 乙 165 5 丙 145 6 辛 140 7 丁 125 8 庚 120 9 戊 105 10 己 100 按照分数 排名 从高到底(区分 课程) SELECT RANK() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO, * FROM test_course ORDER BY course_name, grade DESC 1 奎 数学 95 2 甲 数学 90 3 壬 数学 85 4 乙 数学 80 5 丙 数学 70 5 辛 数学 70 7 庚 数学 60 7 丁 数学 60 9 戊 数学 50 9 己 数学 50 1 甲 语文 95 2 奎 语文 90 3 乙 语文 85 4 壬 语文 80 5 丙 语文 75 6 辛 语文 70 7 丁 语文 65 8 庚 语文 60 9 戊 语文 55 10 己 语文 50 南無阿弥陀佛 126 DENSE_RANK 排名连续 Oracle 按照分数 排名 从高到底 SELECT DENSE_RANK() OVER( ORDER BY SUM(grade) DESC) AS NO, student_name, SUM(grade) AS AllGrade FROM test_course GROUP BY student_name ORDER BY SUM(grade) DESC NO STUDENT_NA ALLGRADE ---------- ---------- ---------- 1 奎 185 1 甲 185 2 乙 165 2 壬 165 3 丙 145 4 辛 140 5 丁 125 6 庚 120 7 戊 105 8 己 100 已选择 10 行。 按照分数 排名 从高到底(区分 课程) SELECT DENSE_RANK() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO, test_course.student_name, test_course.course_name, test_course.grade FROM test_course ORDER BY course_name, grade DESC NO STUDENT_NA COURSE_NAM GRADE 南無阿弥陀佛 127 ---------- ---------- ---------- ---------- 1 奎 数学 95 2 甲 数学 90 3 壬 数学 85 4 乙 数学 80 5 辛 数学 70 5 丙 数学 70 6 庚 数学 60 6 丁 数学 60 7 己 数学 50 7 戊 数学 50 1 甲 语文 95 NO STUDENT_NA COURSE_NAM GRADE ---------- ---------- ---------- ---------- 2 奎 语文 90 3 乙 语文 85 4 壬 语文 80 5 丙 语文 75 6 辛 语文 70 7 丁 语文 65 8 庚 语文 60 9 戊 语文 55 10 己 语文 50 已选择 20 行。 SQL Server 按照分数 排名 从高到底 SELECT DENSE_RANK() OVER( ORDER BY SUM(grade) DESC) AS NO, student_name, SUM(grade) AS AllGrade FROM test_course GROUP BY student_name ORDER BY SUM(grade) DESC 1 甲 185 1 奎 185 2 壬 165 2 乙 165 3 丙 145 4 辛 140 5 丁 125 南無阿弥陀佛 128 6 庚 120 7 戊 105 8 己 100 按照分数 排名 从高到底(区分 课程) SELECT DENSE_RANK() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO, * FROM test_course ORDER BY course_name, grade DESC 1 奎 数学 95 2 甲 数学 90 3 壬 数学 85 4 乙 数学 80 5 丙 数学 70 5 辛 数学 70 6 庚 数学 60 6 丁 数学 60 7 戊 数学 50 7 己 数学 50 1 甲 语文 95 2 奎 语文 90 3 乙 语文 85 4 壬 语文 80 5 丙 语文 75 6 辛 语文 70 7 丁 语文 65 8 庚 语文 60 9 戊 语文 55 10 己 语文 50 NTILE 分组 Oracle 按照分数 划分 5 个区间 从高到底 SELECT 南無阿弥陀佛 129 NTILE(5) OVER( ORDER BY SUM(grade) DESC) AS NO, student_name, SUM(grade) AS AllGrade FROM test_course GROUP BY student_name ORDER BY SUM(grade) DESC NO STUDENT_NA ALLGRADE ---------- ---------- ---------- 1 奎 185 1 甲 185 2 乙 165 2 壬 165 3 丙 145 3 辛 140 4 丁 125 4 庚 120 5 戊 105 5 己 100 已选择 10 行。 按照分数 划分区间 从高到底(区分 课程) SELECT NTILE(5) OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO, test_course.student_name, test_course.course_name, test_course.grade FROM test_course ORDER BY course_name, grade DESC NO STUDENT_NA COURSE_NAM GRADE ---------- ---------- ---------- ---------- 1 奎 数学 95 1 甲 数学 90 2 壬 数学 85 2 乙 数学 80 3 辛 数学 70 3 丙 数学 70 南無阿弥陀佛 130 4 庚 数学 60 4 丁 数学 60 5 己 数学 50 5 戊 数学 50 1 甲 语文 95 NO STUDENT_NA COURSE_NAM GRADE ---------- ---------- ---------- ---------- 1 奎 语文 90 2 乙 语文 85 2 壬 语文 80 3 丙 语文 75 3 辛 语文 70 4 丁 语文 65 4 庚 语文 60 5 戊 语文 55 5 己 语文 50 已选择 20 行。 SQL Server 按照分数 划分 5 个区间 从高到底 SELECT NTILE(5) OVER( ORDER BY SUM(grade) DESC) AS NO, student_name, SUM(grade) AS AllGrade FROM test_course GROUP BY student_name ORDER BY SUM(grade) DESC 1 甲 185 1 奎 185 2 壬 165 2 乙 165 3 丙 145 3 辛 140 4 丁 125 4 庚 120 5 戊 105 5 己 100 按照分数 划分区间 从高到底(区分 课程) SELECT NTILE(5) OVER(PARTITION BY course_name ORDER BY grade DESC) 南無阿弥陀佛 131 AS NO, * FROM test_course ORDER BY course_name, grade DESC 1 奎 数学 95 1 甲 数学 90 2 壬 数学 85 2 乙 数学 80 3 丙 数学 70 3 辛 数学 70 4 庚 数学 60 4 丁 数学 60 5 戊 数学 50 5 己 数学 50 1 甲 语文 95 1 奎 语文 90 2 乙 语文 85 2 壬 语文 80 3 丙 语文 75 3 辛 语文 70 4 丁 语文 65 4 庚 语文 60 5 戊 语文 55 5 己 语文 50 计算一行在组中的相对位置 CUME_DIST Oracle CUME_DIST(x) = number of values in S coming before and including x in the specified order/ N 计算一行在组中的相对位置,CUME_DIST 总是返回大于 0、小于或等于 1 的 数,该数表示该行在 N 行中的位置。 按照分数 排名 从高到底 (这里把 CUME_DIST 作了乘 10 的处理,以便与其 他的作比较.) SELECT 10 * CUME_DIST() OVER( ORDER BY SUM(grade) DESC) AS CUME, student_name, 南無阿弥陀佛 132 SUM(grade) AS AllGrade FROM test_course GROUP BY student_name ORDER BY SUM(grade) DESC; CUME STUDENT_NA ALLGRADE ---------- ---------- ---------- 2 奎 185 2 甲 185 4 乙 165 4 壬 165 5 丙 145 6 辛 140 7 丁 125 8 庚 120 9 戊 105 10 己 100 已选择 10 行。 PERCENT_RANK Oracle PERCENT_RANK PERCENT_RANK(x) = (rank of row in its partition - 1) / (number of rows in the partition - 1) 和 CUME_DIST(累积分配)函数类似,对于一个组中给定的行来说,在计算 那行的序号时,先减 1,然后除以 n-1(n 为组中所有的行数)。 该函数总是返回 0~1(包括 1)之间的数。RANK 函数对于等值的返回序列值 是一样的 按照分数 排名 从高到底 (这里把 PERCENT_RANK 作了乘 10 的处理,以便 与其他的作比较.) SELECT 10 * PERCENT_RANK() OVER( ORDER BY SUM(grade) DESC) AS PERCENT, student_name, SUM(grade) AS AllGrade 南無阿弥陀佛 133 FROM test_course GROUP BY student_name; PERCENT STUDENT_NA ALLGRADE ---------- ---------- ---------- 0 奎 185 0 甲 185 2.22222222 乙 165 2.22222222 壬 165 4.44444444 丙 145 5.55555556 辛 140 6.66666667 丁 125 7.77777778 庚 120 8.88888889 戊 105 10 己 100 已选择 10 行。 计算百分比 RATIO_TO_REPORT Oracle SELECT RATIO_TO_REPORT(SUM(grade)) OVER() AS "所占百分比", student_name, SUM(grade) AS AllGrade FROM test_course GROUP BY student_name; 所占百分比 STUDENT_NA ALLGRADE ---------- ---------- ---------- .101045296 丙 145 .087108014 丁 125 .083623693 庚 120 .069686411 己 100 .128919861 甲 185 .128919861 奎 185 南無阿弥陀佛 134 .114982578 壬 165 .073170732 戊 105 .097560976 辛 140 .114982578 乙 165 已选择 10 行。 窗口函数 (Oracle 特有) Oracle 分析函数的语法: 分析函数( [参数] ) OVER ( 分析语句 ) 分析语句的语法: [ 分区语句 ] [ 排序语句 [ 窗口语句 ] ] 窗口函数 windowing_clause = { ROWS | RANGE } { BETWEEN { UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND { UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } | { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING } } ROWS 是关键字,指定窗口由物理行构成 RANGE 是关键字,指定窗口由逻辑偏移量构成 BETWEEN..AND 是关键字,用来指定窗口的起始点和终结点 UNBOUNDED PRECEDING 指明窗口开始于分组的第一行 CURRENT ROW 作为起始点,指明窗口开始于当前行或当前值 南無阿弥陀佛 135 作为终结点,指明窗口结束于当前行或当前值 UNBOUNDED FOLLOWING 指明窗口结束于分组的最后一行 value_expr 为物理或逻辑偏移量表达式 value_expr PRECEDING 表示从 当前行 - value_expr 行开始,到当前行结束 value_expr FOLLOWING 表示从 当前行 行开始,当前行 + value_expr 行结束 逐步累计 Oracle ROWS UNBOUNDED PRECEDING 意味着 物理行从分组的第一行开始,直到 当前行结束。 SELECT student_name, SUM(grade) AS AllGrade, SUM(SUM(grade)) OVER(ORDER BY student_name ROWS UNBOUNDED PRECEDING) AS "依次累计" FROM test_course GROUP BY student_name; STUDENT_NA ALLGRADE 依次累计 ---------- ---------- ---------- 丙 145 145 丁 125 270 庚 120 390 己 100 490 甲 185 675 奎 185 860 壬 165 1025 戊 105 1130 辛 140 1270 乙 165 1435 已选择 10 行。 南無阿弥陀佛 136 移动平均 1 Oracle 下面是用于计算移动平均的例子 ROWS 2 PRECEDING 意味着 物理行从当前行-2 行开始,到当前行结束。 SELECT student_name, SUM(grade) AS AllGrade, SUM(SUM(grade)) OVER(ORDER BY student_name ROWS 2 PRECEDING) AS "3 人移动合计", AVG(SUM(grade)) OVER(ORDER BY student_name ROWS 2 PRECEDING) AS "3 人移动平均" FROM test_course GROUP BY student_name; STUDENT_NA ALLGRADE 3 人移动合计 3 人移动平均 ---------- ---------- ----------- ----------- 丙 145 145 145 丁 125 270 135 庚 120 390 130 己 100 345 115 甲 185 405 135 奎 185 470 156.666667 壬 165 535 178.333333 戊 105 455 151.666667 辛 140 410 136.666667 乙 165 410 136.666667 已选择 10 行。 移动平均 2 Oracle 下面是用于计算移动平均的例子 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 意味着 物理行从 当前行-1 行开始,到当前行+1 行结束。 SELECT student_name, SUM(grade) AS AllGrade, SUM(SUM(grade)) 南無阿弥陀佛 137 OVER(ORDER BY student_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS "3 人移 动合计", AVG(SUM(grade)) OVER(ORDER BY student_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS "3 人移 动平均" FROM test_course GROUP BY student_name; STUDENT_NA ALLGRADE 3 人移动合计 3 人移动平均 ---------- ---------- ----------- ----------- 丙 145 270 135 丁 125 390 130 庚 120 345 115 己 100 405 135 甲 185 470 156.666667 奎 185 535 178.333333 壬 165 455 151.666667 戊 105 410 136.666667 辛 140 410 136.666667 乙 165 305 152.5 已选择 10 行。 移动平均 3 Oracle 下面是用于计算移动平均的例子 RANGE 20 PRECEDING 意味着 逻辑行的数值,从当前行的数值-20 开始,到当前行的数值结束。 SELECT student_name, SUM(grade) AS AllGrade, SUM(SUM(grade)) OVER(ORDER BY SUM(grade) RANGE 20 PRECEDING) AS "向前 20 分移动合计", AVG(SUM(grade)) OVER(ORDER BY SUM(grade) RANGE 20 PRECEDING) AS "向前 20 分移动平均" FROM test_course 南無阿弥陀佛 138 GROUP BY student_name; STUDENT_NA ALLGRADE 向前 20 分移动合计 向前 20 分移动平均 ---------- ---------- ---------------- ---------------- 己 100 100 100 戊 105 205 102.5 庚 120 325 108.333333 丁 125 350 116.666667 辛 140 385 128.333333 丙 145 410 136.666667 乙 165 475 158.333333 壬 165 475 158.333333 奎 185 700 175 甲 185 700 175 已选择 10 行。 注意: 上面的窗口函数,为逻辑行。 如果数据是日期逻辑的话,使用下面 2 种函数处理 RANGE NUMTODSINTERVAL(100, 'DAY'/'HOUR'/'MINUTE'/'SECOND') RANGE NUMTOYMINTERVAL(1, 'YEAR'/ 'MONTH') FIRST_VALUE and LAST_VALUE Oracle ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 意味着 物理行从 当前行-1 行开始,到当前行+1 行结束。 FIRST_VALUE 为窗口中的第一个数据. LAST_VALUE 为窗口中的最后一个数据. SELECT student_name, SUM(grade) AS AllGrade, FIRST_VALUE(SUM(grade)) OVER(ORDER BY student_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS "第一个", LAST_VALUE(SUM(grade)) OVER(ORDER BY student_name 南無阿弥陀佛 139 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS "末一个" FROM test_course GROUP BY student_name; STUDENT_NA ALLGRADE 第一个 末一个 ---------- ---------- ---------- ---------- 丙 145 145 125 丁 125 145 120 庚 120 125 100 己 100 120 185 甲 185 100 185 奎 185 185 165 壬 165 185 105 戊 105 165 140 辛 140 105 165 乙 165 140 165 已选择 10 行。 LAG/LEAD Oracle LAG 为取当前行的 前 n 行的数据 LEAD 为取当前行的 前 n 行的数据 SELECT student_name, SUM(grade) AS AllGrade, LAG(SUM(grade), 2) OVER(ORDER BY student_name) AS "前 2 个", LEAD(SUM(grade), 2) OVER(ORDER BY student_name) AS "后 2 个" FROM test_course GROUP BY student_name; STUDENT_NA ALLGRADE 前 2 个 后 2 个 ---------- ---------- ---------- ---------- 丙 145 120 南無阿弥陀佛 140 丁 125 100 庚 120 145 185 己 100 125 185 甲 185 120 165 奎 185 100 105 壬 165 185 140 戊 105 185 165 辛 140 165 乙 165 105 已选择 10 行。 FIRST/LAST Oracle 这里需要注意,不要与 FIRST_VALUE and LAST_VALUE 混淆。 FIRST/LAST 需要在 KEEP (DENSE_RANK ) 中使用。 用途是在一个 特定的范围内,取最小/最大的数值。 避免 额外的再写 表关联 或者 嵌套的子查询 。 SELECT course_name, student_name, grade, AVG(grade) KEEP (DENSE_RANK FIRST ORDER BY grade) OVER (PARTITION BY course_name) AS "本门课程最低分", AVG(grade) KEEP (DENSE_RANK LAST ORDER BY grade) OVER (PARTITION BY course_name) AS "本门课程最高分" FROM test_course; COURSE_NAM STUDENT_NA GRADE 本门课程最低分 本门课程最高 分 ---------- ---------- ---------- -------------- -------------- 数学 壬 85 50 95 数学 辛 70 50 95 数学 庚 60 50 95 数学 己 50 50 95 南無阿弥陀佛 141 数学 奎 95 50 95 数学 丁 60 50 95 数学 丙 70 50 95 数学 乙 80 50 95 数学 甲 90 50 95 数学 戊 50 50 95 语文 奎 90 50 95 COURSE_NAM STUDENT_NA GRADE 本门课程最低分 本门课程最高 分 ---------- ---------- ---------- -------------- -------------- 语文 壬 80 50 95 语文 辛 70 50 95 语文 庚 60 50 95 语文 己 50 50 95 语文 戊 55 50 95 语文 丁 65 50 95 语文 丙 75 50 95 语文 甲 95 50 95 语文 乙 85 50 95 已选择 20 行。 WITHIN GROUP Oracle [RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST]( constant expression [, ...] ) WITHIN GROUP ( ORDER BY order by expression [ASC|DESC] [NULLS FIRST|NULLS LAST][, ...] ) WITHIN GROUP 就是根据数值,计算出所在的排名。 SELECT RANK() OVER( ORDER BY SUM(grade) DESC) AS NO, student_name, SUM(grade) AS AllGrade FROM test_course GROUP BY student_name ORDER BY 南無阿弥陀佛 142 SUM(grade) DESC NO STUDENT_NA ALLGRADE ---------- ---------- ---------- 1 奎 185 1 甲 185 3 乙 165 3 壬 165 5 丙 145 6 辛 140 7 丁 125 8 庚 120 9 戊 105 10 己 100 已选择 10 行。 -- 查看 总分 140 分的, 排名在什么位置上. SELECT RANK(140) WITHIN GROUP( ORDER BY SUM(grade) DESC) AS NO FROM test_course GROUP BY student_name; NO ---------- 6 PERCENTILE_CONT | PERCENTILE_DISC Oracle PERCENTILE_CONT 返回一个与输入的分布百分比值相对应的数据值,分布百分比的计算方法见 函数 PERCENT_RANK PERCENTILE_DISC 返回一个与输入的分布百分比值相对应的数据值,分布百分比的计算方法见 函数 CUME_DIST,如果没有正好对应的数据值,就取大于该分布值的下一个 南無阿弥陀佛 143 值。 注意:本函数与 PERCENTILE_CONT 的区别在找不到对应的分布值时返回 的替代值的计算方法不同 SELECT student_name, grade, PERCENT_RANK() OVER( ORDER BY grade) AS "PERCENT_RANK", CUME_DIST() OVER( ORDER BY grade) AS "CUME_DIST" FROM test_course; STUDENT_NA GRADE PERCENT_RANK CUME_DIST ---------- ---------- ------------ ---------- 戊 50 0 .15 己 50 0 .15 己 50 0 .15 戊 55 .157894737 .2 庚 60 .210526316 .35 庚 60 .210526316 .35 丁 60 .210526316 .35 丁 65 .368421053 .4 辛 70 .421052632 .55 辛 70 .421052632 .55 丙 70 .421052632 .55 STUDENT_NA GRADE PERCENT_RANK CUME_DIST ---------- ---------- ------------ ---------- 丙 75 .578947368 .6 壬 80 .631578947 .7 乙 80 .631578947 .7 乙 85 .736842105 .8 壬 85 .736842105 .8 奎 90 .842105263 .9 甲 90 .842105263 .9 奎 95 .947368421 1 甲 95 .947368421 1 已选择 20 行。 SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY grade) AS CONT, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY grade) AS DISC 南無阿弥陀佛 144 FROM test_course ORDER BY course_name; CONT DISC ---------- ---------- 70 70 SELECT PERCENTILE_CONT(0.15) WITHIN GROUP (ORDER BY grade) AS CONT, PERCENTILE_DISC(0.15) WITHIN GROUP (ORDER BY grade) AS DISC FROM test_course ORDER BY course_name; CONT DISC ---------- ---------- 54.25 50 中位数 Median Oracle SELECT course_name, Median(grade) AS "中位数", AVG(grade) AS "平均数" FROM test_course GROUP BY course_name; COURSE_NAM 中位数 平均数 ---------- ---------- ---------- 数学 70 71 语文 72.5 72.5 南無阿弥陀佛 145 MODEL 查询函数(Oracle 特有) MODEL 语法: [] [] [MAIN ] [PARTITION BY ()] DIMENSION BY () MEASURES () [] [RULES] (, ,.., ) ::= ::= RETURN {ALL|UPDATED} ROWS ::= [IGNORE NAV | [KEEP NAV] [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE] ::= [UPDATE | UPSERT | UPSERT ALL] [AUTOMATIC ORDER | SEQUENTIAL ORDER] [ITERATE () [UNTIL ]] ::= REFERENCE ON ON () DIMENSION BY () MEASURES () 首先说明必须的选项: MODEL 关键字 DIMENSION BY 维度 MEASURES 指定作为数组的列 RULES 可选:对数组进行各种操作的描述。 -- 原有的 SQL,查询每个月的销售额. With cte AS ( SELECT TO_CHAR(SALE_DATE, 'MM') AS month, SUM(sale_money) AS sum_money FROM sale_report GROUP BY TO_CHAR(SALE_DATE, 'MM') 南無阿弥陀佛 146 ) SELECT * FROM cte ORDER BY month; MO SUM_MONEY -- ---------- 01 62806 02 56714 03 62868 04 60855 05 62930 06 60915 07 62992 08 63023 09 61005 10 63085 11 61065 MO SUM_MONEY -- ---------- 12 63147 已选择 12 行。 -- 使用 MODEL 语句,实现类似 UNION ALL 操作的处理。 -- 在原有 SQL 的 每个月的销售额 的基础上,进一步追加 每个季度 与 全年 的销售额。 With cte AS ( SELECT TO_CHAR(SALE_DATE, 'MM') AS month, SUM(sale_money) AS sum_money FROM sale_report GROUP BY TO_CHAR(SALE_DATE, 'MM') ) SELECT * FROM cte MODEL DIMENSION BY (month) -- 按照 月 为 维度 MEASURES(sum_money) -- 数组的数据为 sum_money RULES( 南無阿弥陀佛 147 sum_money['Q1'] = sum_money['01']+sum_money['02']+sum_money['03'], sum_money['Q2'] = sum_money['04']+sum_money['05']+sum_money['06'], sum_money['Q3'] = sum_money['07']+sum_money['08']+sum_money['09'], sum_money['Q4'] = sum_money['10']+sum_money['11']+sum_money['12'], sum_money['YY'] = sum_money['Q1']+sum_money['Q2']+sum_money['Q3']+sum_money['Q4'] ) ORDER BY month; MO SUM_MONEY -- ---------- 01 62806 02 56714 03 62868 04 60855 05 62930 06 60915 07 62992 08 63023 09 61005 10 63085 11 61065 MO SUM_MONEY -- ---------- 12 63147 Q1 182388 Q2 184700 Q3 187020 Q4 187297 YY 741405 已选择 17 行。 -- 上一个 SQL, 用 + 用的太多了。 -- 这里用 SUM()[BETWEEN 和 AND] 返回特定范围内的数据单元 -- 这里用 SUM()[ IN ] 返回特定范围内的数据单元 With cte AS ( SELECT TO_CHAR(SALE_DATE, 'MM') AS month, SUM(sale_money) AS sum_money 南無阿弥陀佛 148 FROM sale_report GROUP BY TO_CHAR(SALE_DATE, 'MM') ) SELECT * FROM cte MODEL DIMENSION BY (month) -- 按照 月 为 维度 MEASURES(sum_money) -- 数组的数据为 sum_money ( sum_money['Q1'] = SUM(sum_money)[month BETWEEN '01' AND'03'], sum_money['Q2'] = SUM(sum_money)[month BETWEEN '04' AND'06'], sum_money['Q3'] = SUM(sum_money)[month BETWEEN '07' AND'09'], sum_money['Q4'] = SUM(sum_money)[month BETWEEN '10' AND'12'], sum_money['YY'] = SUM(sum_money)[month IN ('Q1', 'Q2', 'Q3', 'Q4')] ) ORDER BY month; 结果同上,就不重复复制粘贴了。 CURRENTV() 取当前行 Oracle -- 本查询用于在 每一行上,增加一列(上月销售合计) -- 这里与前面不同点有以下几点: -- 1.CTE 当中,增加了一列 0 AS prev_sum_money -- 2.月的处理,由 TO_CHAR(SALE_DATE, 'MM') 变为 EXTRACT(MONTH FROM SALE_DATE) -- 3.在于 MEASURES 中有 2 列数据.也就是可以有 2 个数组,分别为 sum_money 与 prev_sum_money -- 4.使用了 FOR 语句,遍历数组从 2 月到 12 月 -- 5.使用了 CURRENTV() 函数,取得当前数组索引,然后用来-1。从而获取 上 月销售合计. With cte AS ( SELECT EXTRACT(MONTH FROM SALE_DATE) AS month, SUM(sale_money) AS sum_money, 0 AS prev_sum_money FROM 南無阿弥陀佛 149 sale_report GROUP BY EXTRACT(MONTH FROM SALE_DATE) ) SELECT * FROM cte MODEL DIMENSION BY (month) -- 按照 月 为 维度 MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money ( prev_sum_money[FOR month FROM 2 TO 12 INCREMENT 1] = sum_money[CURRENTV() - 1] ) ORDER BY month; MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- -------------- 1 62806 0 2 56714 62806 3 62868 56714 4 60855 62868 5 62930 60855 6 60915 62930 7 62992 60915 8 63023 62992 9 61005 63023 10 63085 61005 11 61065 63085 MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- -------------- 12 63147 61065 已选择 12 行。 -- 在前面的基础上,增加年的字段. -- 然后再维度中,增加 年的 维度. With cte AS 南無阿弥陀佛 150 ( SELECT EXTRACT(YEAR FROM SALE_DATE) AS year, EXTRACT(MONTH FROM SALE_DATE) AS month, SUM(sale_money) AS sum_money, 0 AS prev_sum_money FROM sale_report GROUP BY EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE) ) SELECT * FROM cte MODEL DIMENSION BY (year, month) -- 按照 年,月 为 维度 MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money ( prev_sum_money[2009, FOR month FROM 2 TO 12 INCREMENT 1] = sum_money[CURRENTV(), CURRENTV() - 1] ) ORDER BY month; YEAR MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- ---------- -------------- 2009 1 62806 0 2009 2 56714 62806 2009 3 62868 56714 2009 4 60855 62868 2009 5 62930 60855 2009 6 60915 62930 2009 7 62992 60915 2009 8 63023 62992 2009 9 61005 63023 2009 10 63085 61005 2009 11 61065 63085 YEAR MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- ---------- -------------- 2009 12 63147 61065 已选择 12 行。 南無阿弥陀佛 151 IS PRESENT / PRESENTV / PRESENTNNV Oracle -- 在前面的基础上,增加 2010 年 1 月的 上月 数据 (数据库中无 2010 年数 据) -- 这里的 IS PRESENT :当数据单元指定的记录在 MODEL 子句执行之前存 在,则 IS PRESENT 返回 TRUE。 -- PRESENTV() 如果 cell 引用的记录在 MODEL 子句执行以前就存在,那么 PRESENTV(cell,expr1,expr2)返回表达式 expr1。如果这条记录不存在,则返回 表达式 expr2。 -- PRESENTNNV() 如果 cell 引用的单元在 MODEL 子句执行之前已经存在,并 且该单元的值不为空,则 PRESENTNNV(cell,expr1,expr2)返回表达式 expr1。如 果记录不存在,或单元值为空值,则返回表达式 expr2。 With cte AS ( SELECT EXTRACT(YEAR FROM SALE_DATE) AS year, EXTRACT(MONTH FROM SALE_DATE) AS month, SUM(sale_money) AS sum_money, 0 AS prev_sum_money FROM sale_report GROUP BY EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE) ) SELECT * FROM cte MODEL DIMENSION BY (year, month) -- 按照 年,月 为 维度 MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money ( prev_sum_money[2009, FOR month FROM 2 TO 12 INCREMENT 1] = sum_money[CURRENTV(), CURRENTV() - 1], prev_sum_money[2010, 1] = sum_money[2009, 12], prev_sum_money[2010, FOR month FROM 2 TO 3 INCREMENT 1] = CASE WHEN sum_money[CURRENTV(), CURRENTV() - 1] IS 南無阿弥陀佛 152 PRESENT THEN sum_money[CURRENTV(), CURRENTV() - 1] ELSE 0 END, prev_sum_money[2010, FOR month FROM 4 TO 6 INCREMENT 1] = PRESENTV(sum_money[CURRENTV(), CURRENTV() - 1], sum_money[CURRENTV(), CURRENTV() - 1], 0), prev_sum_money[2010, FOR month FROM 7 TO 9 INCREMENT 1] = PRESENTNNV(sum_money[CURRENTV(), CURRENTV() - 1], sum_money[CURRENTV(), CURRENTV() - 1], 0), prev_sum_money[2010, FOR month FROM 10 TO 12 INCREMENT 1] = sum_money[CURRENTV(), CURRENTV() - 1] ) ORDER BY year, month; YEAR MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- ---------- -------------- 2009 1 62806 0 2009 2 56714 62806 2009 3 62868 56714 2009 4 60855 62868 2009 5 62930 60855 2009 6 60915 62930 2009 7 62992 60915 2009 8 63023 62992 2009 9 61005 63023 2009 10 63085 61005 2009 11 61065 63085 YEAR MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- ---------- -------------- 2009 12 63147 61065 2010 1 63147 2010 2 0 2010 3 0 2010 4 0 2010 5 0 2010 6 0 2010 7 0 南無阿弥陀佛 153 2010 8 0 2010 9 0 2010 10 YEAR MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- ---------- -------------- 2010 11 2010 12 已选择 24 行。 测试插入几条 2010 年的数据,用于验证结果. INSERT INTO sale_report SELECT TO_DATE('20100201', 'YYYYMMDD'), 'A', 20 FROM dual UNION ALL SELECT TO_DATE('20100501', 'YYYYMMDD'), 'A', 50 FROM dual UNION ALL SELECT TO_DATE('20100801', 'YYYYMMDD'), 'A', 80 FROM dual UNION ALL SELECT TO_DATE('20101101', 'YYYYMMDD'), 'A', 110 FROM dual; 再次执行的结果: YEAR MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- ---------- -------------- 2009 1 62806 0 2009 2 56714 62806 2009 3 62868 56714 2009 4 60855 62868 2009 5 62930 60855 2009 6 60915 62930 2009 7 62992 60915 2009 8 63023 62992 2009 9 61005 63023 2009 10 63085 61005 2009 11 61065 63085 YEAR MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- ---------- -------------- 2009 12 63147 61065 2010 1 63147 2010 2 20 0 2010 3 20 南無阿弥陀佛 154 2010 4 0 2010 5 50 0 2010 6 50 2010 7 0 2010 8 80 0 2010 9 80 2010 10 YEAR MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- ---------- -------------- 2010 11 110 2010 12 110 已选择 24 行。 IGNORE NAV 选项 Oracle -- 本例子用于演示 IGNORE NAV -- IGNORE NAV 的返回值如下: -- 空值或缺失数字值时返回 0。 -- 空值或缺失字符串值时返回空字符串。 -- 空值或缺失日期值时返回 01-JAN-2000。 -- 其他所有数据库类型时返回空值。 -- KEEP NAV 对空值或缺失数字值返回空值。注意默认条件下使用 KEEP NAV。 With cte AS ( SELECT EXTRACT(YEAR FROM SALE_DATE) AS year, EXTRACT(MONTH FROM SALE_DATE) AS month, SUM(sale_money) AS sum_money, 0 AS prev_sum_money FROM sale_report WHERE EXTRACT(YEAR FROM SALE_DATE) = 2010 GROUP BY EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE) ) 南無阿弥陀佛 155 SELECT * FROM cte MODEL IGNORE NAV DIMENSION BY (year, month) -- 按照 年,月 为 维度 MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money ( prev_sum_money[2010, FOR month FROM 2 TO 12 INCREMENT 1] = sum_money[CURRENTV(), CURRENTV() - 1] ) ORDER BY year, month; YEAR MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- ---------- -------------- 2010 2 20 0 2010 3 20 2010 4 0 2010 5 50 0 2010 6 50 2010 7 0 2010 8 80 0 2010 9 80 2010 10 0 2010 11 110 0 2010 12 110 已选择 11 行。 RULES UPDATE 选项 -- 本例子用于演示 更新已有的单元 -- 默认情况下,如果表达式左端的引用单元存在,则更新该单元。 -- 如果该单元不存在,就在数组中创建一条新的记录。 -- 可以用 RULES UPDATE 改变这种默认的行为,指出在单元不存在的情况下 不创建新纪录。 With cte AS 南無阿弥陀佛 156 ( SELECT EXTRACT(YEAR FROM SALE_DATE) AS year, EXTRACT(MONTH FROM SALE_DATE) AS month, SUM(sale_money) AS sum_money, 0 AS prev_sum_money FROM sale_report WHERE EXTRACT(YEAR FROM SALE_DATE) = 2010 GROUP BY EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE) ) SELECT * FROM cte MODEL DIMENSION BY (year, month) -- 按照 年,月 为 维度 MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money RULES UPDATE ( prev_sum_money[2010, FOR month FROM 2 TO 12 INCREMENT 1] = sum_money[CURRENTV(), CURRENTV() - 1] ) ORDER BY year, month; YEAR MONTH SUM_MONEY PREV_SUM_MONEY ---------- ---------- ---------- -------------- 2010 2 20 2010 5 50 2010 8 80 2010 11 110 南無阿弥陀佛 157 REFERENCE 语句 与 CV 函数 -- 下面是 REFERENCE 与 CV 的例子。 -- 首先主查询的 GROUP BY 调整为 按物品分组 -- 结果只有 3 行数据 -- 其中的 tax_cte 用于表示 3 种物品的税收 分别为 0.10/0.13/0.17 -- REFERENCE 可以理解为在前面的查询结果的基础上,再进一步在 MODEL 这个级别上,进行维度关联的处理。 -- 例子中基于 tax_cte ,指定了 sale_item 为维度,数值为 per -- 在 主 的 数 据 处 理 中 , tax_money[ANY] = sum_money[CV(sale_item)] * item_ref.per[CV(sale_item)] -- ANY 意味着全部都要处理 -- CV(sale_item) 表示当前维度的 sale_item With cte AS ( SELECT sale_item, SUM(sale_money) AS sum_money, 0 AS tax_money FROM sale_report WHERE EXTRACT(YEAR FROM SALE_DATE) = 2009 GROUP BY sale_item ), tax_cte AS ( SELECT 'B' AS sale_item, 0.13 AS per FROM dual UNION ALL SELECT 'C' AS sale_item, 0.17 AS per FROM dual ) SELECT * FROM cte MODEL REFERENCE item_ref ON (SELECT sale_item, per FROM tax_cte) DIMENSION BY (sale_item) -- 按照 物品 为维度 MEASURES (per) -- 数组的数据为 per IGNORE NAV -- 忽略空(显示为初始数据). MAIN conversion DIMENSION BY (sale_item) -- 按照 物品 为维度 MEASURES(sum_money, tax_money) -- 数 组 的 数 据 为 sum_money, prev_sum_money RULES ( tax_money[ANY] = sum_money[CV(sale_item)] * 南無阿弥陀佛 158 item_ref.per[CV(sale_item)] ) ORDER BY sale_item; SA SUM_MONEY TAX_MONEY -- ---------- ---------- A 733285 0 B 2382 309.66 C 5738 975.46 注意, 由于 A 物品在 tax_cte 没有数据,且 IGNORE NAV, 因此结果为 0 如果删除 IGNORE NAV,结果为: SA SUM_MONEY TAX_MONEY -- ---------- ---------- A 733285 B 2382 309.66 C 5738 975.46 Iterative 方法 Oracle -- Iterative 用于循环处理 -- 首先:初始数据。 SELECT x, s FROM DUAL MODEL DIMENSION BY (1 AS x) MEASURES (1024 AS s) (); X S ---------- ---------- 1 1024 -- 处理一次 SELECT x, s FROM DUAL MODEL DIMENSION BY (1 AS x) MEASURES (1024 AS s) RULES (s[1] = s[1]/2); X S ---------- ---------- 南無阿弥陀佛 159 1 512 -- 循环处理 4 次 SELECT x, s FROM DUAL MODEL DIMENSION BY (1 AS x) MEASURES (1024 AS s) RULES UPDATE ITERATE (4) (s[1] = s[1]/2); X S ---------- ---------- 1 64 -- 复杂的循环处理 -- UNTIL 为跳出循环处理的条件 -- 函数 PREVIOUS() 为取 上一次处理的数据 -- ITERATION_NUMBER 为当前循环执行的次数 SELECT x, s, iterations FROM DUAL MODEL DIMENSION BY (1 AS x) -- 维度 1 MEASURES (1024 AS s, 0 AS iterations) -- 初始值 1024, 初始次数 0 RULES ITERATE (1000) -- 最多循环 1000 次 UNTIL ABS(PREVIOUS(s[1]) - s[1]) < 1 -- 条件: 上次的数值 - 本次的数 值 的绝对值小于 1 (s[1] = s[1]/2, iterations[1] = ITERATION_NUMBER); X S ITERATIONS ---------- ---------- ---------- 1 .5 10 -- 上面那个过程的 每次处理的明细 -- ITERATION_NUMBER 是从 0 开始计算的。 SELECT x, s, iterations FROM DUAL MODEL DIMENSION BY (1 AS x) -- 维度 1 MEASURES (1024 AS s, 0 AS iterations) -- 初始值 1024, 初始次数 0 RULES ITERATE (20) -- 最多循环 20 次 UNTIL ABS(s[ITERATION_NUMBER + 1] - s[ITERATION_NUMBER + 2]) < 1 -- 条件: 上次的数值 - 本次的数值 的绝对值小于 1 (s[ITERATION_NUMBER + 2] = s[ITERATION_NUMBER + 1]/2, iterations[ITERATION_NUMBER + 2] = ITERATION_NUMBER + 1); X S ITERATIONS 南無阿弥陀佛 160 ---------- ---------- ---------- 1 1024 0 2 512 1 3 256 2 4 128 3 5 64 4 6 32 5 7 16 6 8 8 7 9 4 8 10 2 9 11 1 10 X S ITERATIONS ---------- ---------- ---------- 12 .5 11 已选择 12 行。 AUTOMATIC ORDER Oracle -- 初始情况下,正确的赋值顺序。 SELECT x, s, s1, s2, s3 FROM DUAL MODEL DIMENSION BY (1 AS x) -- 维度 1 MEASURES (1024 AS s, 0 AS s1, 0 AS s2, 0 AS s3) -- 初始值 RULES ( s1[1] = s[1] / 2, s2[1] = s1[1] / 2, s3[1] = s2[1] / 2 ); X S S1 S2 S3 ---------- ---------- ---------- ---------- ---------- 1 1024 512 256 128 -- 假如赋值顺序被打乱了。 SELECT x, s, s1, s2, s3 FROM DUAL MODEL DIMENSION BY (1 AS x) -- 维度 1 南無阿弥陀佛 161 MEASURES (1024 AS s, 0 AS s1, 0 AS s2, 0 AS s3) -- 初始值 RULES ( s3[1] = s2[1] / 2, s2[1] = s1[1] / 2, s1[1] = s[1] / 2 ); X S S1 S2 S3 ---------- ---------- ---------- ---------- ---------- 1 1024 512 0 0 -- 自动调整赋值顺序. SELECT x, s, s1, s2, s3 FROM DUAL MODEL DIMENSION BY (1 AS x) -- 维度 1 MEASURES (1024 AS s, 0 AS s1, 0 AS s2, 0 AS s3) -- 初始值 RULES AUTOMATIC ORDER ( s3[1] = s2[1] / 2, s2[1] = s1[1] / 2, s1[1] = s[1] / 2 ); X S S1 S2 S3 ---------- ---------- ---------- ---------- ---------- 1 1024 512 256 128 排序规则 Oracle -- 下面用于以 SEQUENTIAL ORDER 的方式来处理 逐月累计销售合计 -- SEQUENTIAL ORDER 表示按照顺序依次处理。 -- 排序方式为:ORDER BY month ASC 也就是 月份 从小到大的处理方式开始 处理 -- 本例子就是直接按顺序执行: -- 本月的 逐月累计数据 = 上月 逐月累计数据 + 本月销售数据。 SELECT month, sum_money, all_sum FROM sale_report GROUP BY 南無阿弥陀佛 162 EXTRACT(MONTH FROM SALE_DATE) MODEL DIMENSION BY (EXTRACT(MONTH FROM SALE_DATE) AS month) -- 按照 月 为 维度 MEASURES(SUM(sale_money) AS sum_money, 0 AS all_sum) -- 数组的数据 为 sum_money, all_sum IGNORE NAV RULES SEQUENTIAL ORDER ( all_sum[ANY] ORDER BY month ASC = all_sum[CV(month) - 1] + sum_money[CV(month)] ) ORDER BY month; MONTH SUM_MONEY ALL_SUM ---------- ---------- ---------- 1 62806 62806 2 56714 119520 3 62868 182388 4 60855 243243 5 62930 306173 6 60915 367088 7 62992 430080 8 63023 493103 9 61005 554108 10 63085 617193 11 61065 678258 MONTH SUM_MONEY ALL_SUM ---------- ---------- ---------- 12 63147 741405 已选择 12 行。 注意:假如上面的 ORDER BY month ASC 修改为 ORDER BY month DESC,那么结果数据将不正确。 南無阿弥陀佛 163 安全函数 是否可访问指定数据库 HAS_DBACCESS(SQL Server Only) Oracle SQL Server 如果可以访问该数 据 库 , 那 么 HAS_DBACCESS 返回 1。如果用户 不 能 访 问 该 数 据 库,那么它返回 0。 如果该数据库名无 效,则返回 NULL 1> SELECT 2> HAS_DBACCESS('Stock'), 3> HAS_DBACCESS('master'), 4> HAS_DBACCESS('not_exist') 5> go ----------- ----------- ----------- 1 1 NULL (1 行受影响) 用户是否是 Microsoft? Windows NT? 组或 Microsoft SQL Server? 角 色的成员 IS_MEMBER (SQL Server Only) Oracle SQL Server 0 当 前 用 户 不 是 group 或 role 的 成员。 1 当 前 用 户 是 group 或 role 的 成员。 NULL group 或 role 是无效的。 1> SELECT 2> IS_MEMBER('db_owner'), 3> IS_MEMBER('db_backupoperator'), 4> IS_MEMBER('not_exist') 5> go ----------- ----------- ----------- 1 1 NULL (1 行受影响) 南無阿弥陀佛 164 指明当前的用户登录是否是指定的服务器角色的成员 IS_SRVROLEMEMBER (SQL Server Only) Oracle SQL Server login 的数据类型 为 sysname,默认 值为 NULL。如果 未指定,那么使用 当前用户的登录帐 户。 0 login 不是 role 的成员。 1 login 是 role 的 成员。 NULL role 或 login 是无效的。 1> SELECT 2> IS_SRVROLEMEMBER ('dbcreator'), 3> IS_SRVROLEMEMBER ('sysadmin', 'BUILTIN\Users'), 4> IS_SRVROLEMEMBER ('setupadmin', 'stock') 5> go ----------- ----------- ----------- 1 0 1 (1 行受影响) 取 得 连 接 到 SQL Server 实 例 的 登 录 名 ORIGINAL_LOGIN SYSTEM_USER SQL Server C:\>sqlcmd -S "localhost\SQLEXPRESS" -U 1> select ORIGINAL_LOGIN() 2> go ---------------------- HOME-BED592453C\wzq 1> SELECT SYSTEM_USER 2> go ------------------------ HOME-BED592453C\wzq (1 行受影响) C:\>sqlcmd -S "localhost\SQLEXPRESS" -U Demo -P demo 1> select ORIGINAL_LOGIN() 2> go ---------------------- Demo 南無阿弥陀佛 165 1> SELECT SYSTEM_USER 2> go --------------------- Demo (1 行受影响) 取得用户名 USER Oracle SQL> select user from dual; USER ----------------------------- HR SQL Server 1> select user 2> go ------------------ dbo (1 行受影响) 1> SELECT SESSION_USER 2> go ----------------------- dbo (1 行受影响) 1> select CURRENT_USER 2> go --------------------------- dbo (1 行受影响) MySQL mysql> SELECT USER(); +----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) 南無阿弥陀佛 166 聚集函数 聚合函数部分,使用附录中的 SALE_REPORT 测试表,与测试数据。 平均值 AVG Oracle SQL> SELECT 2 sale_item, 3 AVG(sale_money) 4 FROM 5 sale_report 6 GROUP BY 7 sale_item; SALE AVG(SALE_MONEY) ---- --------------- A 2009 B 6.5260274 C 15.7205479 SQL Server 1> SELECT 2> sale_item, 3> AVG(sale_money) 4> FROM 5> sale_report 6> GROUP BY 7> sale_item 8> go sale_item --------- ---------------------------------------- A 2009.000000 C 15.720547 B 6.526027 (3 行受影响) MySQL mysql> SELECT -> sale_item, -> AVG(sale_money) -> FROM -> sale_report -> GROUP BY -> sale_item -> // +-----------+-----------------+ 南無阿弥陀佛 167 | sale_item | AVG(sale_money) | +-----------+-----------------+ | A | 2009.000000 | | B | 6.526027 | | C | 15.720548 | +-----------+-----------------+ 3 rows in set (0.00 sec) 相关系数 CORR(Oracle) 返回一对表达式的相关系数,它是如下的缩写: COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2)) 从统计上讲,相关性是变量之间关联的强度,变量之间的关联意味着在某种程度上一个变量的值可由其它的值进 行预测。通过返回一个-1~1 之间的一个数, 相关系数给出了关联的强度,0 表示不相关。 Oracle SQL> SELECT 2 sale_item, 3 CORR( 4 TO_NUMBER(TO_CHAR(sale_date, 'DD')), 5 sale_money) AS A 6 FROM 7 sale_report 8 GROUP BY 9 sale_item; SA A -- ---------- A B .011893177 C 1 对结果有疑惑,可去找到创建测试数据的那段存储过程,可以明白。 CORR_S CORR_K 适合非参数相关或者级别关联,具体还需要进一步测试 南無阿弥陀佛 168 行的计数 COUNT Oracle SQL> SELECT 2 sale_item, 3 COUNT(sale_money) 4 FROM 5 sale_report 6 GROUP BY 7 sale_item; SALE COUNT(SALE_MONEY) ---- ----------------- A 365 B 365 C 365 SQL Server 1> SELECT 2> sale_item, 3> COUNT(sale_money) 4> FROM 5> sale_report 6> GROUP BY 7> sale_item 8> go sale_item --------- ----------- A 365 C 365 B 365 (3 行受影响) MySQL mysql> SELECT -> sale_item, -> COUNT(sale_money) -> FROM -> sale_report -> GROUP BY -> sale_item -> // +-----------+-------------------+ | sale_item | COUNT(sale_money) | +-----------+-------------------+ | A | 365 | | B | 365 | | C | 365 | +-----------+-------------------+ 南無阿弥陀佛 169 3 rows in set (0.00 sec) COUNT_BIG SQL Server 返回组中的项数。COUNT_BIG 的用法与 COUNT 函数类似。两个函数唯一的 差别是它们的返回值。COUNT_BIG 始终返回 bigint 数据类型值。COUNT 始 终返回 int 数据类型值。 1> SELECT 2> sale_item, 3> COUNT_BIG(sale_money) 4> FROM 5> sale_report 6> GROUP BY 7> sale_item 8> go sale_item --------- -------------------- A 365 C 365 B 365 (3 行受影响) 总体协方差 COVAR_POP Oracle SQL> SELECT 2 sale_item, 3 COVAR_POP( 4 TO_NUMBER(TO_CHAR(sale_date, 'DD')), 5 sale_money) AS A 6 FROM 7 sale_report 8 GROUP BY 9 sale_item; SA A -- ---------- A 0 南無阿弥陀佛 170 B .360698067 C 77.3739613 为了提高试验的精确性和准确性,对处理以外的一切条件都需要采取有效措施严加控制,使它们在各处理间尽量 一致,这叫试验控制。但在有些情况下,即使作出很大努力也难以使试验控制达到预期目的。例如:研究几种配 合饲料对猪的增重效果,希望试验仔猪的初始重相同,因为仔猪的初始重不同,将影响到猪的增重。经研究发现: 增重与初始重之间存在线性回归关系。但是,在实际试验中很难满足试验仔猪初始重相同这一要求。这时可利用 仔猪的初始重(记为 x)与其增重(记为 y)的回归关系,将仔猪增重都矫正为初始重相同时的增重,于是初始重不同 对仔猪增重的影响就消除了。由于矫正后的增重是应用统计方法将初始重控制一致而得到的,故叫统计控制。统 计控制是试验控制的一种辅助手段。经过这种矫正,试验误差将减小,对试验处理效应估计更为准确。若 y 的变 异主要由 x 的不同造成(处理没有显著效应),则各矫正后的 间将没有显著差异(但原 y 间的差异可能是显著的)。 若 y 的变异除掉 x 不同的影响外,尚存在不同处理的显著效应,则可期望各 间将有显著差异(但原 y 间差异可能 是不显著的)。此外,矫正后的 和原 y 的大小次序也常不一致。所以,处理平均数的回归矫正和矫正平均数的显 著性检验,能够提高试验的准确性和精确性,从而更真实地反映试验实际。这种将回归分析与方差分析结合在一 起,对试验数据进行分析的方法,叫做协方差分析(analysis of covariance)。 样本协方差 COVAR_SAMP Oracle SQL> SELECT 2 sale_item, 3 COVAR_SAMP ( 4 TO_NUMBER(TO_CHAR(sale_date, 'DD')), 5 sale_money) AS A 6 FROM 7 sale_report 8 GROUP BY 9 sale_item; SA A -- ---------- A 0 B .361688996 C 77.5865272 南無阿弥陀佛 171 最大值 MAX Oracle SQL> SELECT 2 sale_item, 3 MAX(sale_money) 4 FROM 5 sale_report 6 GROUP BY 7 sale_item; SALE MAX(SALE_MONEY) ---- --------------- A 2009 B 12 C 31 SQL Server 1> SELECT 2> sale_item, 3> MAX(sale_money) 4> FROM 5> sale_report 6> GROUP BY 7> sale_item 8> go sale_item --------- ------------ A 2009.00 C 31.00 B 12.00 MySQL mysql> SELECT -> sale_item, -> MAX(sale_money) -> FROM -> sale_report -> GROUP BY -> sale_item -> // +-----------+-----------------+ | sale_item | MAX(sale_money) | +-----------+-----------------+ | A | 2009.00 | | B | 12.00 | | C | 31.00 | +-----------+-----------------+ 3 rows in set (0.00 sec) 南無阿弥陀佛 172 中位数 MEDIAN Oracle SQL> SELECT 2 sale_item, 3 MEDIAN(sale_money) 4 FROM 5 sale_report 6 GROUP BY 7 sale_item; SA MEDIAN(SALE_MONEY) -- ------------------ A 2009 B 7 C 16 中位数(Median)统计学名词。 1、定义:一组数据按从小到大(或从大到小)的顺序依次排列,处在中间位置的一个数(或最中间两个数据的 平均数,注意:和众数不同,中位数不一定在这组数据中)。 2、意义:反映了一组数的一般情况。 3、中位数的优缺点:中位数是样本数据所占频率的等分线,它不受少数几个极端值的影响,有时用它代表全体 数据的一般水平更合适。 4、在频率分布直方图中,中位数左边和右边的直方图的面积应该相等,由此可以估计中位数的值。 5、中位数也可表述为第 50 百分位数,二者等价。 6、直观印象描述:一半比¡ 我¡ 小,一半比¡ 我¡ 大。 最小值 MIN Oracle SQL> SELECT 2 sale_item, 3 MIN(sale_money) 4 FROM 5 sale_report 6 GROUP BY 7 sale_item; SALE MIN(SALE_MONEY) ---- --------------- A 2009 南無阿弥陀佛 173 B 1 C 1 SQL Server 1> SELECT 2> sale_item, 3> MIN(sale_money) 4> FROM 5> sale_report 6> GROUP BY 7> sale_item 8> go sale_item --------- ------------ A 2009.00 C 1.00 B 1.00 (3 行受影响) MySQL mysql> SELECT -> sale_item, -> MIN(sale_money) -> FROM -> sale_report -> GROUP BY -> sale_item -> // +-----------+-----------------+ | sale_item | MIN(sale_money) | +-----------+-----------------+ | A | 2009.00 | | B | 1.00 | | C | 1.00 | +-----------+-----------------+ 3 rows in set (0.00 sec) 标准偏差 STDDEV / STDEV Oracle SQL> SELECT 2 sale_item, 3 STDDEV(sale_money) 4 FROM 5 sale_report 6 GROUP BY 7 sale_item; 南無阿弥陀佛 174 SALE STDDEV(SALE_MONEY) ---- ------------------ A 0 B 3.45258413 C 8.80832147 SQL Server 1> SELECT 2> sale_item, 3> STDEV(sale_money) 4> FROM 5> sale_report 6> GROUP BY 7> sale_item 8> go sale_item --------- ------------------------ A 0 C 8.8083214729855683 B 3.452584133232603 (3 行受影响) MySQL mysql> SELECT -> sale_item, -> STDDEV_SAMP(sale_money) -> FROM -> sale_report -> GROUP BY -> sale_item -> // +-----------+-------------------------+ | sale_item | STDDEV_SAMP(sale_money) | +-----------+-------------------------+ | A | 0.000000 | | B | 3.452584 | | C | 8.808321 | +-----------+-------------------------+ 3 rows in set (0.00 sec) 标准偏差:统计学名词。一种量度数据分布的分散程度之标准,用以衡量数据值偏离算术平均值的程度。标准偏 差越小,这些值偏离平均值就越少,反之亦然。 标准偏差公式:S = Sqrt[(∑(xi-x 拨)^2) /(N-1)]公式中∑代表总和,x 拨代表 x 的均值,^2 代表二次方,Sqrt 代表平方根。 南無阿弥陀佛 175 总体标准方差 STDDEV_POP/样本标准方差 STDDEV_SAMP Oracle SQL> SELECT 2 sale_item, 3 STDDEV_POP(sale_money) A, 4 STDDEV_SAMP(sale_money) B 5 FROM 6 sale_report 7 GROUP BY 8 sale_item; SA A B -- ---------- ---------- A 0 0 B 3.44785132 3.45258413 C 8.796247 8.80832147 填充统计标准偏差 STDEVP (SQL Server Only) Oracle SQL Server 1> SELECT 2> sale_item, 3> STDEV(sale_money) 4> FROM 5> sale_report 6> GROUP BY 7> sale_item 8> go sale_item --------- ------------------------ A 0 C 8.8083214729855683 B 3.452584133232603 (3 行受影响) 合计值 SUM Oracle SQL> SELECT 南無阿弥陀佛 176 2 sale_item, 3 SUM(sale_money) 4 FROM 5 sale_report 6 GROUP BY 7 sale_item; SALE SUM(SALE_MONEY) ---- --------------- A 733285 B 2382 C 5738 SQL Server 1> SELECT 2> sale_item, 3> SUM(sale_money) 4> FROM 5> sale_report 6> GROUP BY 7> sale_item 8> go sale_item --------- ---------------------------------------- A 733285.00 C 5738.00 B 2382.00 (3 行受影响) MySQL mysql> SELECT -> sale_item, -> SUM(sale_money) -> FROM -> sale_report -> GROUP BY -> sale_item -> // +-----------+-----------------+ | sale_item | SUM(sale_money) | +-----------+-----------------+ | A | 733285.00 | | B | 2382.00 | | C | 5738.00 | +-----------+-----------------+ 3 rows in set (0.00 sec) 南無阿弥陀佛 177 COMPUTE SUM (SQL Server) SQL Server 1> SELECT 2> CONVERT(varchar(10), sale_date, 112), 3> sale_item, 4> sale_money 5> FROM 6> sale_report 7> WHERE 8> sale_date BETWEEN '2009-01-01' AND '2009-01-03' 9> COMPUTE SUM(sale_money) 10> GO sale_item sale_money ---------- --------- ------------ 20090101 A 2009.00 20090101 B 1.00 20090101 C 1.00 20090102 A 2009.00 20090102 B 1.00 20090102 C 2.00 20090103 A 2009.00 20090103 B 1.00 20090103 C 3.00 sum ======================================== 6036.00 1> 2> SELECT 3> CONVERT(varchar(10), sale_date, 112), 4> sale_item, 5> sale_money 6> FROM 7> sale_report 8> WHERE 9> sale_date BETWEEN '2009-01-01' AND '2009-01-03' 10> ORDER BY 11> CONVERT(varchar(10), sale_date, 112) 12> COMPUTE SUM(sale_money) by CONVERT(varchar(10), sale_date, 112) 13> GO sale_item sale_money ---------- --------- ------------ 20090101 A 2009.00 20090101 B 1.00 南無阿弥陀佛 178 20090101 C 1.00 sum ---------------------------------------- 2011.00 sale_item sale_money ---------- --------- ------------ 20090102 A 2009.00 20090102 B 1.00 20090102 C 2.00 sum ---------------------------------------- 2012.00 sale_item sale_money ---------- --------- ------------ 20090103 A 2009.00 20090103 B 1.00 20090103 C 3.00 sum ---------------------------------------- 2013.00 方差 VARIANCE / VAR Oracle SQL> SELECT 2 sale_item, 3 VARIANCE(sale_money) 4 FROM 5 sale_report 6 GROUP BY 7 sale_item; SALE VARIANCE(SALE_MONEY) ---- -------------------- A 0 B 11.9203372 C 77.5865272 SQL Server 1> SELECT 2> sale_item, 3> VAR(sale_money) 4> FROM 5> sale_report 南無阿弥陀佛 179 6> GROUP BY 7> sale_item 8> go sale_item --------- ------------------------ A 0 C 77.586527171458656 B 11.920337197049525 (3 行受影响) MySQL mysql> SELECT -> sale_item, -> VAR_SAMP(sale_money) -> FROM -> sale_report -> GROUP BY -> sale_item -> // +-----------+----------------------+ | sale_item | VAR_SAMP(sale_money) | +-----------+----------------------+ | A | 0.000000 | | B | 11.920337 | | C | 77.586527 | +-----------+----------------------+ 3 rows in set (0.00 sec) 样本中各数据与样本平均数的差的平方和的平均数叫做样本方差;样本方差的算术平方根叫做样本标准差。 样本方差和样本标准差都是衡量一个样本波动大小的量,样本方差或样本标准差越大,样本数据的波动就越大。 方差和标准差。 方差和标准差是测算离散趋势最重要、最常用的指标。 方差是各变量值与其均值离差平方的平均数,它是测算数值型数据离散程度的最重要的方法。 标准差为方差的平方根,用 S 表示。 标准差与方差不同的是,标准差和变量的计算单位相同,比方差清楚,因此很多时候我们分析的时候更多的使用 的是标准差。 总体方差 VAR_POP/样本方差 VAR_SAMP Oracle SQL> SELECT 2 sale_item, 南無阿弥陀佛 180 3 VAR_POP(sale_money), 4 VAR_SAMP(sale_money) 5 FROM 6 sale_report 7 GROUP BY 8 sale_item; SA VAR_POP(SALE_MONEY) VAR_SAMP(SALE_MONEY) -- ------------------- -------------------- A 0 0 B 11.8876787 11.9203372 C 77.3739613 77.5865272 填充的统计方差 VARP (SQL Server Only) Oracle SQL Server 1> SELECT 2> sale_item, 3> VARP(sale_money) 4> FROM 5> sale_report 6> GROUP BY 7> sale_item 8> go sale_item --------- ------------------------ A 0 C 77.373961343591645 B 11.887678738975417 (3 行受影响) MySQL mysql> SELECT -> sale_item, -> VARIANCE(sale_money) -> FROM -> sale_report -> GROUP BY -> sale_item -> // +-----------+----------------------+ | sale_item | VARIANCE(sale_money) | +-----------+----------------------+ 南無阿弥陀佛 181 | A | 0.000000 | | B | 11.887679 | | C | 77.373961 | +-----------+----------------------+ 3 rows in set (0.02 sec) ROLLUP 与 CUBE 查询 ROLLUP-简单用法 第一个例子,为统计每种产品,每个季度的销售情况。 每个产品都有 4 个季度的合计 最后有所有产品,4 个季度的总计 Oracle SQL> SELECT 2 sale_item, 3 TO_CHAR(sale_date, 'Q') AS sale_quarter, 4 SUM(sale_money) 5 FROM 6 sale_report 7 GROUP BY 8 ROLLUP(sale_item, TO_CHAR(sale_date, 'Q')); SALE SA SUM(SALE_MONEY) ---- -- --------------- A 1 180810 A 2 182819 A 3 184828 A 4 184828 A 733285 B 1 180 B 2 455 B 3 735 B 4 1012 B 2382 C 1 1398 SALE SA SUM(SALE_MONEY) ---- -- --------------- C 2 1426 C 3 1457 C 4 1457 C 5738 南無阿弥陀佛 182 741405 16 rows selected. SQL Server 1> SELECT 2> sale_item, 3> DATEPART(qq, sale_date) AS sale_quarter, 4> SUM(sale_money) 5> FROM 6> sale_report 7> GROUP BY 8> sale_item, DATEPART(qq, sale_date) WITH ROLLUP 9> go sale_item sale_quarter --------- ------------ ---------------------------------------- A 1 180810.00 A 2 182819.00 A 3 184828.00 A 4 184828.00 A NULL 733285.00 B 1 180.00 B 2 455.00 B 3 735.00 B 4 1012.00 B NULL 2382.00 C 1 1398.00 C 2 1426.00 C 3 1457.00 C 4 1457.00 C NULL 5738.00 NULL NULL 741405.00 (16 行受影响) MySQL mysql> SELECT -> sale_item, -> QUARTER(sale_date) AS sale_quarter, -> SUM(sale_money) -> FROM -> sale_report -> GROUP BY -> sale_item, QUARTER(sale_date) WITH ROLLUP -> // +-----------+--------------+-----------------+ | sale_item | sale_quarter | SUM(sale_money) | +-----------+--------------+-----------------+ | A | 1 | 180810.00 | | A | 2 | 182819.00 | 南無阿弥陀佛 183 | A | 3 | 184828.00 | | A | 4 | 184828.00 | | A | NULL | 733285.00 | | B | 1 | 180.00 | | B | 2 | 455.00 | | B | 3 | 735.00 | | B | 4 | 1012.00 | | B | NULL | 2382.00 | | C | 1 | 1398.00 | | C | 2 | 1426.00 | | C | 3 | 1457.00 | | C | 4 | 1457.00 | | C | NULL | 5738.00 | | NULL | NULL | 741405.00 | +-----------+--------------+-----------------+ 16 rows in set (0.00 sec) ROLLUP-GROUPING GROUPING 函数,用于表示,当前行,是否是对指定字段的小计/总计数据 Oracle SQL> SELECT 2 sale_item, 3 TO_CHAR(sale_date, 'Q') AS sale_quarter, 4 SUM(sale_money), 5 GROUPING(sale_item) A, 6 GROUPING(TO_CHAR(sale_date, 'Q')) B 7 FROM 8 sale_report 9 GROUP BY 10 ROLLUP(sale_item, TO_CHAR(sale_date, 'Q')); SALE SA SUM(SALE_MONEY) A B ---- -- --------------- ---------- ---------- A 1 180810 0 0 A 2 182819 0 0 A 3 184828 0 0 A 4 184828 0 0 A 733285 0 1 B 1 180 0 0 B 2 455 0 0 B 3 735 0 0 B 4 1012 0 0 南無阿弥陀佛 184 B 2382 0 1 C 1 1398 0 0 SALE SA SUM(SALE_MONEY) A B ---- -- --------------- ---------- ---------- C 2 1426 0 0 C 3 1457 0 0 C 4 1457 0 0 C 5738 0 1 741405 1 1 16 rows selected. SQL Server 1> SELECT 2> sale_item item, 3> DATEPART(qq, sale_date) AS sale_q, 4> SUM(sale_money) [money], 5> GROUPING(sale_item) A, 6> GROUPING(DATEPART(qq, sale_date)) B 7> FROM 8> sale_report 9> GROUP BY 10> sale_item, DATEPART(qq, sale_date) WITH ROLLUP 11> go item sale_q money A B ---- ----------- ------------------ --- --- A 1 180810.00 0 0 A 2 182819.00 0 0 A 3 184828.00 0 0 A 4 184828.00 0 0 A NULL 733285.00 0 1 B 1 180.00 0 0 B 2 455.00 0 0 B 3 735.00 0 0 B 4 1012.00 0 0 B NULL 2382.00 0 1 C 1 1398.00 0 0 C 2 1426.00 0 0 C 3 1457.00 0 0 C 4 1457.00 0 0 C NULL 5738.00 0 1 NULL NULL 741405.00 1 1 (16 行受影响) MySQL 不支持 GROUPING 南無阿弥陀佛 185 ROLLUP-调整 NULL 显示 通过 判断 第一个字段 空/非空。 来决定是否是 ¡ 总计¡ 第二个字段之后的,需要通过 GROUPING 来判断,该行是 总计行,还是小计行。 Oracle SQL> SELECT 2 NVL(sale_item, 'ALL') AS item, 3 CASE 4 WHEN GROUPING(sale_item) = 1 5 AND GROUPING(TO_CHAR(sale_date, 'Q')) = 1 6 THEN 'ALL' 7 WHEN GROUPING(sale_item) = 0 8 AND GROUPING(TO_CHAR(sale_date, 'Q')) = 1 9 THEN 'SUB' 10 ELSE 11 TO_CHAR(sale_date, 'Q') 12 END AS sale_q, 13 SUM(sale_money) AS money 14 FROM 15 sale_report 16 GROUP BY 17 ROLLUP(sale_item, TO_CHAR(sale_date, 'Q')); ITEM SALE_Q MONEY ------ ------ ---------- A 1 180810 A 2 182819 A 3 184828 A 4 184828 A SUB 733285 B 1 180 B 2 455 B 3 735 B 4 1012 B SUB 2382 C 1 1398 ITEM SALE_Q MONEY ------ ------ ---------- C 2 1426 C 3 1457 C 4 1457 C SUB 5738 ALL ALL 741405 16 rows selected. 南無阿弥陀佛 186 SQL Server 1> SELECT 2> ISNULL(sale_item, '总计') AS item, 3> CASE 4> WHEN GROUPING(sale_item) = 1 5> AND GROUPING(STR(DATEPART(qq, sale_date))) = 1 6> THEN '总计' 7> WHEN GROUPING(sale_item) = 0 8> AND GROUPING(STR(DATEPART(qq, sale_date))) = 1 9> THEN '小计' 10> ELSE 11> STR(DATEPART(qq, sale_date)) 12> END AS sale_q, 13> SUM(sale_money) [money] 14> FROM 15> sale_report 16> GROUP BY 17> sale_item, STR(DATEPART(qq, sale_date)) WITH ROLLUP; 18> go item sale_q money ---- ---------- ---------------------------------------- A 1 180810.00 A 2 182819.00 A 3 184828.00 A 4 184828.00 A 小计 733285.00 B 1 180.00 B 2 455.00 B 3 735.00 B 4 1012.00 B 小计 2382.00 C 1 1398.00 C 2 1426.00 C 3 1457.00 C 4 1457.00 C 小计 5738.00 总 总计 741405.00 (16 行受影响) MySQL mysql> SELECT -> IFNULL(sale_item, 'ALL') AS sale_item, -> IFNULL(QUARTER(sale_date), 5) AS sale_quarter, -> SUM(sale_money) -> FROM -> sale_report -> GROUP BY -> sale_item, QUARTER(sale_date) WITH ROLLUP 南無阿弥陀佛 187 -> // +-----------+--------------+-----------------+ | sale_item | sale_quarter | SUM(sale_money) | +-----------+--------------+-----------------+ | A | 1 | 180810.00 | | A | 2 | 182819.00 | | A | 3 | 184828.00 | | A | 4 | 184828.00 | | A | 4 | 733285.00 | | B | 1 | 180.00 | | B | 2 | 455.00 | | B | 3 | 735.00 | | B | 4 | 1012.00 | | B | 4 | 2382.00 | | C | 1 | 1398.00 | | C | 2 | 1426.00 | | C | 3 | 1457.00 | | C | 4 | 1457.00 | | C | 4 | 5738.00 | | ALL | 4 | 741405.00 | +-----------+--------------+-----------------+ 16 rows in set, 1 warning (0.00 sec) 注:IFNULL 对表达式处理有一定的问题. CUBE-简单用法 Oracle SQL> SELECT 2 sale_item, 3 TO_CHAR(sale_date, 'Q') AS sale_quarter, 4 SUM(sale_money) 5 FROM 6 sale_report 7 GROUP BY 8 CUBE(sale_item, TO_CHAR(sale_date, 'Q')); SALE SA SUM(SALE_MONEY) ---- -- --------------- 741405 1 182388 2 184700 3 187020 4 187297 南無阿弥陀佛 188 A 733285 A 1 180810 A 2 182819 A 3 184828 A 4 184828 B 2382 SALE SA SUM(SALE_MONEY) ---- -- --------------- B 1 180 B 2 455 B 3 735 B 4 1012 C 5738 C 1 1398 C 2 1426 C 3 1457 C 4 1457 20 rows selected. SQL Server 1> SELECT 2> sale_item, 3> DATEPART(qq, sale_date) AS sale_quarter, 4> SUM(sale_money) 5> FROM 6> sale_report 7> GROUP BY 8> sale_item, DATEPART(qq, sale_date) WITH CUBE 9> go sale_item sale_quarter --------- ------------ --------------- A 1 180810.00 A 2 182819.00 A 3 184828.00 A 4 184828.00 A NULL 733285.00 B 1 180.00 B 2 455.00 B 3 735.00 B 4 1012.00 B NULL 2382.00 C 1 1398.00 C 2 1426.00 C 3 1457.00 C 4 1457.00 C NULL 5738.00 南無阿弥陀佛 189 NULL NULL 741405.00 NULL 1 182388.00 NULL 2 184700.00 NULL 3 187020.00 NULL 4 187297.00 (20 行受影响) MySQL MySQL 不支持 CUBE CUBE-GROUPING Oracle SQL> SELECT 2 sale_item, 3 TO_CHAR(sale_date, 'Q') AS sale_quarter, 4 SUM(sale_money), 5 GROUPING(sale_item) A, 6 GROUPING(TO_CHAR(sale_date, 'Q')) B 7 FROM 8 sale_report 9 GROUP BY 10 CUBE(sale_item, TO_CHAR(sale_date, 'Q')); SALE SA SUM(SALE_MONEY) A B ---- -- --------------- ---------- ---------- 741405 1 1 1 182388 1 0 2 184700 1 0 3 187020 1 0 4 187297 1 0 A 733285 0 1 A 1 180810 0 0 A 2 182819 0 0 A 3 184828 0 0 A 4 184828 0 0 B 2382 0 1 SALE SA SUM(SALE_MONEY) A B ---- -- --------------- ---------- ---------- B 1 180 0 0 B 2 455 0 0 B 3 735 0 0 B 4 1012 0 0 C 5738 0 1 南無阿弥陀佛 190 C 1 1398 0 0 C 2 1426 0 0 C 3 1457 0 0 C 4 1457 0 0 20 rows selected. SQL Server 1> SELECT 2> sale_item, 3> DATEPART(qq, sale_date) AS sale_quarter, 4> SUM(sale_money), 5> GROUPING(sale_item) A, 6> GROUPING(DATEPART(qq, sale_date)) B 7> FROM 8> sale_report 9> GROUP BY 10> sale_item, DATEPART(qq, sale_date) WITH CUBE 11> go sale_item sale_quarter A B --------- ------------ ----------- --- --- A 1 180810.00 0 0 A 2 182819.00 0 0 A 3 184828.00 0 0 A 4 184828.00 0 0 A NULL 733285.00 0 1 B 1 180.00 0 0 B 2 455.00 0 0 B 3 735.00 0 0 B 4 1012.00 0 0 B NULL 2382.00 0 1 C 1 1398.00 0 0 C 2 1426.00 0 0 C 3 1457.00 0 0 C 4 1457.00 0 0 C NULL 5738.00 0 1 NULL NULL 741405.00 1 1 NULL 1 182388.00 1 0 NULL 2 184700.00 1 0 NULL 3 187020.00 1 0 NULL 4 187297.00 1 0 MySQL MySQL 不支持 CUBE 南無阿弥陀佛 191 附录 SALE_REPORT 测试表 Oracle SQL> CREATE TABLE SALE_REPORT ( 2 SALE_DATE DATE NOT NULL , 3 SALE_ITEM VARCHAR(2) NOT NULL , 4 SALE_MONEY DECIMAL(10,2) NOT NULL 5 ) 6 / Table created. SQL Server CREATE TABLE SALE_REPORT ( SALE_DATE DATETIME NOT NULL , SALE_ITEM VARCHAR(2) NOT NULL , SALE_MONEY DECIMAL(10,2) NOT NULL ) go MySQL mysql> CREATE TABLE SALE_REPORT ( -> SALE_DATE DATETIME NOT NULL , -> SALE_ITEM VARCHAR(2) NOT NULL , -> SALE_MONEY DECIMAL(10,2) NOT NULL -> ) -> ; Query OK, 0 rows affected (0.06 sec) SALE_REPORT 测试数据 Oracle SQL> DECLARE 2 v_begin_day DATE; 3 v_end_day DATE; 4 BEGIN 5 v_begin_day := TO_DATE('2009-01-01', 'YYYY-MM-DD'); 6 v_end_day := TO_DATE('2010-01-01', 'YYYY-MM-DD'); 7 8 WHILE v_begin_day < v_end_day LOOP 9 INSERT INTO SALE_REPORT VALUES(v_begin_day, 'A', TO_NUMBER( TO_CHAR(v _begin_day, 'YYYY') )); 10 INSERT INTO SALE_REPORT VALUES(v_begin_day, 'B', 南無阿弥陀佛 192 TO_NUMBER( TO_CHAR(v _begin_day, 'MM') )); 11 INSERT INTO SALE_REPORT VALUES(v_begin_day, 'C', TO_NUMBER( TO_CHAR(v _begin_day, 'DD') )); 12 v_begin_day := v_begin_day + 1; 13 END LOOP; 14 END; 15 / PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL Server 1> DECLARE 2> @v_begin_day DATETIME, @v_end_day DATETIME; 3> BEGIN 4> SET @v_begin_day = '2009-01-01'; 5> SET @v_end_day = '2010-01-01'; 6> 7> WHILE @v_begin_day < @v_end_day 8> BEGIN 9> INSERT INTO SALE_REPORT VALUES(@v_begin_day, 'A', YEAR(@v_begin_day) ) ; 10> INSERT INTO SALE_REPORT VALUES(@v_begin_day, 'B', MONTH(@v_begin_day) ); 11> INSERT INTO SALE_REPORT VALUES(@v_begin_day, 'C', DAY(@v_begin_day) ) ; 12> SET @v_begin_day = @v_begin_day + 1; 13> END; 14> END; 15> go MySQL mysql> DELIMITER // mysql> CREATE PROCEDURE CreateReportData() -> BEGIN -> DECLARE v_begin_day DATE; -> DECLARE v_end_day DATE; -> -> SET v_begin_day = STR_TO_DATE('2009-01-01', '%Y-%m-%d'); -> SET v_end_day = STR_TO_DATE('2010-01-01', '%Y-%m-%d'); -> 南無阿弥陀佛 193 -> WHILE v_begin_day < v_end_day DO -> INSERT INTO SALE_REPORT VALUES -> (v_begin_day, 'A', -> Year(v_begin_day) ); -> INSERT INTO SALE_REPORT VALUES -> (v_begin_day, 'B', -> Month(v_begin_day) ); -> INSERT INTO SALE_REPORT VALUES -> (v_begin_day, 'C', -> DAY(v_begin_day) ); -> SET v_begin_day = DATE_ADD(v_begin_day, INTERVAL 1 DAY); -> END WHILE; -> END; -> // Query OK, 0 rows affected (0.00 sec) mysql> call CreateReportData() // Query OK, 1 row affected (23.78 sec)

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享文档获得金币 ] 2 人已下载

下载文档

相关文档