基本sql用法

　　1.连表修改

update TABLE_CQ_HR_10006_人员信息 
set TABLE_CQ_HR_10006_人员信息.g10_key=
TABLE_CQ_HR_10008_岗位信息.g10_key
from TABLE_CQ_HR_10008_岗位信息
inner join TABLE_CQ_HR_10006_人员信息
on TABLE_CQ_HR_10006_人员信息.deptCode_key=TABLE_CQ_HR_10008_岗位信息.deptCode_key
where TABLE_CQ_HR_10008_岗位信息.position_name like'%服务员%'
and TABLE_CQ_HR_10006_人员信息.g10_key
in(
 select a.g10_key from dbo.TABLE_CQ_HR_10008_岗位信息 a
inner join dbo.TABLE_CQ_HR_10007_部门信息 b
on a.deptCode_key=b.deptCode_key
where (position_name like '%传菜员%')
) and TABLE_CQ_HR_10006_人员信息.a0190='107794'

2.连接外部服务器

exec sp_addlinkedserver 'CQMIS','','SQLOLEDB', '服务器地址' 
exec sp_addlinkedsrvlogin 'CQMIS','false',null, 'IT', 'ITMIS'

SELECT * from CQMIS.CQ_MIS.dbo.View_HR_Z0120_E


exec sp_dropserver 'CQMIS ', 'droplogins '

3.多个数据取最新一条

select * from (select a0101,a0190,contract_state,edate,deptCode_key,ROW_NUMBER() over (PARTITION by a01_key order 
by edate desc) rn from TBALE_HT01_HR合同信息) t where t.rn<=1 and contract_state in('新签','续签')
and  DATEDIFF(DD,GETDATE(),edate)<=30

 4.分组组合数据

select deptcode0b,deptcode0c
,
STUFF((SELECT ','+
部门+'-'+工号+'-'+姓名+'-'+级别+'-'+CONVERT(varchar(100),审核时间,23)
FROM View_T_HR_Promotion_R1 as s
where t.deptcode0b=s.deptcode0b
FOR XML PATH('')),1,1,'' ) as content
from View_T_HR_Promotion_R1 as t
group by deptcode0b,deptcode0c