常用sql语句

1.列转字符串

select stuff((


select ','+name 
from Student 


for xml path('')),1,1,'') as name

2.按组排序号

select deptId,empname,
ROW_NUMBER() over(partition by deptId order by empname) RowNum
from pubEmployee

3.更新时使用连接

update d1 
set d1.Value1=d2.Value1
from datatable1 d1
left join datatable2 d2 on d2.ID=d1.ID

4.获取插入数据的自增id

insert into ATable(CreationUser) 
output inserted.id values('aaaa')

5.guid转bigint

select CONVERT(bigint, CONVERT(varbinary, CAST(N'0x' + replace(newid(),'-','') AS char),1))