SQL适配记录(一)

最新推荐文章于 2024-08-26 10:09:15 发布

zl979899

最新推荐文章于 2024-08-26 10:09:15 发布

阅读量1.7k

点赞数

文章标签： sql sqlserver oracle

本文链接：https://blog.youkuaiyun.com/zl979899/article/details/121155783

版权

副本专栏收录该内容

3 篇文章

订阅专栏

1. 连接符号在Oracle中是||，在SQL server中是+。

2. SQL server中LINENO是关键字，不可作为表字段；嵌套表查询必须设置表的别名。

3. Oracle中的wm_concat()函数

实现行转列功能，即将查询出的某一列值使用逗号进行隔开拼接，成为一条数据
可能报错：oracle.sql.CLOB@65c43d85，需要用to_char(wm_concat())做类型转换
MySQL中有一个group_concat()函数，SQL server中有一个FOR XML PATH('')函数，作用相同或者类似

4. Oracle中的wmsys.wm_concat()和 LISTAGG( )函数都是用来连接字符串。

wmsys.wm_concat是oracle 10g推出
- wmsys是Oracle中的一个用户
- 该函数是一个undocument函数，所以不推荐使用这个函数。因为在后续的版本中还提不提供这个函数都不好说
- 在12C中，oracle不再自带 wmsys.wm_concat()，需要自己创建
- 内部的拼接内容的长度过长时会超出了内部类的保存长度pl/sql varchar2（32767 ）
LISTAGG是oracle 11g推出的
- 该函数的保存长度varchar2(4000)

5. SQL server中stuff(param1, startIndex, length, param2)函数

将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。
param1：一个字符数据表达式。param1可以是常量、变量，也可以是字符列或二进制数据列。
startIndex：一个整数值，指定删除和插入的开始位置。如果 startIndex或 length 为负，则返回空字符串。如果startIndex比param1长，则返回空字符串。startIndex可以是 bigint 类型。
length：一个整数，指定要删除的字符数。如果 length 比param1长，则最多删除到param1 中的最后一个字符。length 可以是 bigint 类型。
如果param1是受支持的字符数据类型，则返回字符数据。如果param1是一个受支持的 binary 数据类型，则返回二进制数据。
如果结果值大于返回类型支持的最大值，则产生错误。

select distinct field = (
        stuff(
            (select ',' + field from table where id = A.id for xml path('')),
            1,
            1,
            ''
        )
    )
from table as A  where id='002'

6. Oracle中的ROW_NUMBER() OVER()函数，同SQL server

ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）。

7. Oracle的Convert() 函数

作用：在oracle中，convert函数是用来转字符集转换的。
语法：CONVERT(string1, char_set_to [, char_set_from])
- string1：要转换的值。它可以是任何的数据类型CHAR、VARCHAR2、NCHAR、 NVARCHAR2、CLOB或NCLOB 　　
- char_set_to：char 转换的字符集的名称
- char_set_from：char存储在数据库中的字符集，默认值是数据库的字符集
示例：select CONVERT('a','utf8','US7ASCII') from dual

8. SQL server中的Convert() 函数

用法一：将数据转换成其他数据类型
- 语法：CONVERT(data_type(length),data_to_be_converted,style)
  - data_type(length) 规定目标数据类型（带有可选的长度）。
  - data_to_be_converted 含有需要转换的值。
  - style 规定日期/时间的输出格式，可省略。
- 示例：select CONVERT(varchar(12) , getdate(), 112 )
- 参考：SQL Server CONVERT() 函数
用法二：decimal类型的数据保留小数点后几位
- 语法：Convert(decimal(18,2),数值)
- 示例：select CONVERT(VARCHAR(20),Convert(decimal(18,2),5.5/2.1))

9. SQL server中的DataLength()和Len()函数，Oracle中是Length()函数

Len() 字符串表达式的字符数，不计尾部空格，但计头部空格和中间的空格。
DataLength() 任何表达式的字节数，包括空格。

10. Oracle中的substr()函数

作用：截取字符串，索引从1开始，截取结果包括索引地址a和b处字符
用法一：substr(string, int a, int b)
用法二：substr(string, int a)
索引地址问题：
- 当a等于0或1时是从第一位开始截取；当a<0时是从后面倒数第一位开始往前截取
- 只要 |a| ≤ b,取a的个数；当 |a| ≥ b时，才取b的个数，由a决定截取位置

11. SQL server中的substring()函数

作用：截取字符串，索引从1开始
用法： substring(string, int a, int b)
索引地址问题：
- 当a>0时，索引从1开始截取，截取长度为 b
- 当a<=0时，索引从1开始截取，截取长度为 b - ((a - 1)的绝对值，如果这个差为负数就返回空

12. Oracle中的listagg()函数

作用：多行合并，oracle 19c不能使用wm_concat函数，只能使用listagg进行列转行。
用法：SELECT listagg(FIELD,',') within group (order by 1) as field FROM table
注意：
- order by必须存在，否则会出现报错。并且排序的字段不同，所获得的结果集不一样。
- 需要使用一定的筛选条件，否则可能报错结果字符串太长ORA-01489: result of string concatenation is too long
- 查询结果集出现乱码，使用to_char()对字段进行字符转换即可

listagg(to_char(field),',') within group(order by field) as field

13. Oracle中的xmlagg()函数

作用：多行合并成一行，结果是一个CLOB。
语法一：xmlagg(xmlparse(content FIELD|| ',' ) order by FIELD).getstringval()
- 函数xmlparse将xml作为文本并将其转换为XMLType数据类型
语法二：xmlagg(xmlelement(x,FIELD , ', ') ORDER BY FIELD).extract('//text()').getstringval()
- xmlelement将创建一个命名为x的XML元素
- XMLAGG创建一个XML片段，是x的XML元素的集合。
- “ .extract（'// text()'）”摆脱了xml的局面，仅保留字符串
如果数据库版本为18c或更高版本，则可以使用json_arrayagg替代xmlagg。