sql server高级语法

用‘|’分割

用‘|’分割
with sql1 as (
  select 'wangjp' as name
  union all
  select 'wangjp' 
  union all
  select '苏文慧'
)
select stuff(( select  '|'
+t.Name from sql1 t 
for xml path('')),1,1,'') 

分组并‘|’但不去重
去重在‘|’前面加distinct
注意'|'必须有，如果不要|，可以写成‘’，但是两个单引号必须留着，否则是xml标签格式的

WITH sql1 AS (SELECT '傻瓜' AS type,'wangjp' AS myname
              UNION ALL
              SELECT '傻瓜','wangjp'
              UNION ALL
              SELECT '笨蛋','苏文慧'
              UNION ALL
              SELECT '傻缺','王傻蛋'
              UNION ALL
              SELECT '傻缺','苏棒槌')
select sql1.type,
STUFF((select '|'+a.myname from sql1 a where a.type = sql1.type for xml path('')),1,1,'')
from sql1
group by sql1.type

死锁

--查询死锁
select    
    request_session_id spid,   
    OBJECT_NAME(resource_associated_entity_id) tableName    
from    
    sys.dm_tran_locks   
where    
    resource_type='OBJECT' 

--杀死死锁进程
kill 354 
--显示死锁相关信息
exec sp_who2 354

并且数据库发生死锁的原因多半时没有提交事务造成的