sqlserver 常用语句

1、多表语句更新

UPDATE B

SET B.loginname=c.loginname

FROM t_employee A, t_systemuser B, oa_employee c

WHERE  A.employeeid=B.belonguserid AND A.oaemployeeid = c.id

 

2、SELECT  CONVERT(VARCHAR(500),PropertyID)+',' FROM Table WHERE RecordStatus='Active' FOR XML PATH('')

3、行转列

select * from DailyIncome ----第一步
pivot
(
sum (IncomeAmount) ----第三步
for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]) ---第二步
) as AvgIncomePerDay