slq 不常用函数、语法

1、with as：

　　WITH AS短语，可以称作为子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到

　　例如：嵌套查询：

　　　　select * from table1 where id in

　　　　(select id from table1 where Name like '%张%')

　　　　使用 with as 可以写作：

　　　　whit ids as (select id from table1 where Name like '%张%')

　　　　select * from table1 where id in ids --ids就是上方 whit 的代码片段 

 

2、for xml path

　　FOR XML PATH ，是将查询结果集以XML形式展现，将多行的结果展示在同一行。

　　例如：

　　　　查询多个字段：

　　　　select * from table1 for xml path('')

　　　　查询结果：

　　　　　　<ID>1</ID>
　　　　　　<Name>张三</Name>
　　　　　　<Sex>1</Sex>
　　　　　　<Age>23</Age>
　　　　　　<IsDel>0</IsDel>
　　　　　　<ID>2</ID>
　　　　　　<Name>柳柳</Name>
　　　　　　<Sex>0</Sex>
　　　　　　<Age>18</Age>
　　　　　　<IsDel>0</IsDel>

　　　　查询一个字段：

　　　　1、select name from table1 for xml path ('')

　　　　查询结果：<name>张三</name><name>李四</name><name>王五</name><name>赵柳</name>

　　　　2、select name+',' from table1 for xml path ('') --不是默认格式则按照给定的格式输出

　　　　查询结果：张三,李四,王五,赵柳,

 

3、stuff

　　将字符串插入到另一个字符串中。 它从第一个字符串的开始位置删除指定长度的字符；然后将第二个字符串插入到第一个字符串的开始位置

　　例如：

　　　　select stuff('abcdefg',2,3,'123456') --意思为从第二个位置删除三个，然后将后方字符串插入该位置

　　　　结果：a123456efg

　　注意：第一个位置是原始字符串，第二个参数是开始删除位置，第三个参数是删除个数，第四个参数为替换字符串

　　　　查询值为 null 的情况：

　　　　　　开始删除位置小于等于0，

　　　　　　开始删除位置大于原始字符串长度

　　　　　　删除个数为负数（删除数大于原始字符串长度则删除到最后一个字符）

 

4、partition

　　可以查询分组后组内排序的结果。

　　　例如：

　　　　SELECT id,col1,col2,col3, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY id DESC) AS ROW_NUM FROM test1 ; --查询结果是根据col1字段进行分组，每组下进行排序

　　　　结果：

　　　　　　id　　 col1　　col2　　col2　　row_num

　　　　　　1　　　1　　　　2　　　　3　　　　1　

　　　　　　5　　　2　　　　6　　　　5　　　　1

　　　　　　3　　　2　　　　9　　　　9　　　　2

　　　　　　2　　　2　　　　7　　　　3　　　　3

　　　　　　4　　　6　　　　4　　　　6　　　　1

　　　　根据col1进行分组，然后根据每组进行排序，不是一组的，row_num从1开始