偶尔会用到的sql

1.将查询结果拼成字符串(用分号将Tom和Jack的学号拼成字符串)

select(

(select   ';'+id   from dbo.students where name in('Jack','Tom')  for xml path(''))

,1,1,'') as ’字符串'

2. With as    递归用法查找部门110及其子部门

--部门代码：dept_code,父级代码：pare_code,部门名称：dept_name

with DeptNode(dept_code,pare_code,dept_name)

as

(

select  dept_code,pare_code,dept_name from SYS_dept where dept_code='100'

union all

select D.dept_code,D.pare_code,D.dept_name from DeptNode N inner join SYS_dept D

on N.dept_code=D.pare_code

)

select * from   DeptNode

3.查询存储过程、视图中的关键字

Use ExFlow--库名

select * from sys.all_objects  a,sys.sql_modules b

where a.is_ms_shipped=0 and a.object_id=b.object_id

and a.type in('P','V','AF')  and b.definiton like'%Soul%'

order by a.name desc