1.with as 用法
1 with temp2 as3 (4 select a.* from [dbo].[erdos_tradepave] a join [dbo].[erdos_checkin] b on a.pk_checkin = b.pk_checkin5 where b.isback='N' and b.isexchange ='Y' )6 7 select * from [dbo].[erdos_tradepave] e where 8 [PK_TRADEPAVE] in (select c.[PK_TRADEPAVE] from [dbo].[erdos_tradepave] c left join [dbo].[erdos_checkin] d on c.pk_checkin = d.pk_checkin
2.with as 多个表
with tempas(select a.* from [dbo].[erdos_tradepave] a join [dbo].[erdos_checkin] b on a.pk_checkin = b.pk_checkinwhere b.isback='N' and b.isexchange ='Y' ),temp1as(select * from [dbo].[erdos_tradepave] e where [PK_TRADEPAVE] in (select c.[PK_TRADEPAVE] from [dbo].[erdos_tradepave] c left join [dbo].[erdos_checkin] d on c.pk_checkin = d.pk_checkin)select * from temp1
3.将一个表的信息插入新表
insert into [Accommodation].[dbo].[CheckIn]([Cid],[OrderCode],[Name],[Sex],[IdCard],[PhoneNo],[FeeScaleCid],[Fee],[RoomCid],[CheckInDateTime])select a.[Cid],a.[VBILLNO],a.[NAME],1,a.[ID],isnull(a.[CONTACTTYPE],''),b.[Cid],b.[FeeValue],c.[Cid],a.[INDATE] from[Erdos].[dbo].[erdos_checkin] a left join [Accommodation].[dbo].[FeeScale] b on b.[FeeValue]=a.[ACCOMMODATIONCOST] left join [Erdos].[dbo].[erdos_dormdoc] c on c.[PK_DORMDOC]=a.[PK_DORMDOC] where a.[DR]=0 and b.[FeeType]=1 and a.[NAME] is not null;
4.update与join联合使用
update a set a.[Sex]=0 from [Accommodation].[dbo].[CheckIn] a left join [Erdos].[dbo].[erdos_checkin] b on b.Cid=a.Cid where b.[SEX]=1;
5.判断数据库表中是否包含某些字符，用charindex判断
update a set a.DormCategoryCid=ISNULL(c.Cid,'D4CD56C7-E861-4EA4-886F-E4ED47BC73BF')from [Erdos].[dbo].[erdos_dormclass] a left join [Erdos].[dbo].[erdos_dormclass] b on b.PK_DORMCLASS=a.PK_DORMCLASSF left join [Accommodation].[dbo].[DormCategory] c on CHARINDEX(c.CategoryName,b.DORMCLASSNAME)>0 where CHARINDEX('专家',a.DORMCLASSNAME)=0 and CHARINDEX('女生',a.DORMCLASSNAME)=0 and CHARINDEX('男生',a.DORMCLASSNAME)=0 and a.ILEVEL=3 and a.DR=0; charindex（要查是否包含的内容，字段名）：=0表示不包含，>0表示包含
6.case when 用法
insert into Accommodation.dbo.Change(Cid,checkincid,fee,PhoneNo,Reason,IdCard,GroupCid,IsCash,CashEndTime,Name,CorpCid,DepartmentCid,RoomCid,Post,ChangeDateTime,Sex,groupname,corpname,department,[status])select NEWID(),b.cid,a.accommodationcost,a.contacttype,a.hpreason,a.id,a.industrial_group,case a.ISXJFLAG when 'Y' then 1 else 0 end ,a.jfenddate,a.NAME,a.pk_corp,a.pk_deptdoc,c.cid,a.pk_om_job,a.TPDATE,a.SEX,d.CHARGEDEPTNAME,d.UNITNAME,e.deptname,a.[Status] from dbo.erdos_tradepave a left join dbo.erdos_checkin b on a.pk_tradepave=b.pk_tradepave left join dbo.erdos_dormdoc c on a.pk_dormdocnew=c.PK_DORMDOCleft join dbo.bd_corp d on a.pk_corp = d.pk_corp left join [dbo].[bd_deptdoc] e on a.pk_deptdoc=e.pk_deptdoc where a.DR=0 and b.Cid is not null and a.NAME is not null ;
case+字段名+when+判断值+then 相等改变成的值 else 不等时改变成的值