======================================函数操作=========================

--去空格CHAR(32)，回车CHAR(13)，换行符 CHAR(10)

select * from logdb.[dbo].[PageErrLogTbl] A

where REPLACE( REPLACE(errLog, CHAR(13) + CHAR(10), ''),CHAR(32), '') LIKE '%'+REPLACE( REPLACE('System.Data.SqlClient.SqlException (0x80131904): 从字符串转换日期和/或时间时，转换失败。 在 ICE.Data.ExecuteQueryHandler.HandleQueryException(Exception exception) 位置 ', CHAR(13) + CHAR(10), ''),CHAR(32), '')+'%'

--日期格式化

CONVERT(VARCHAR(20),A.addTime,120) as 添加时间

--日期相减

select DATEDIFF ( day , GETDATE() , GETDATE() )

--查询表不等待锁

WITH(NOLOCK)

--类型转换

CONVERT(VARCHAR(20),A.soldcodeId)

--行转列，用逗号分隔

SELECT STUFF((SELECT ','+ CONVERT(VARCHAR(20),schoolID) FROM [CommReSchoolTbl] WHERE reID=6299 and isDel=0 FOR XML PATH('')),1,1,'')

--获取数据表中各个类别的最新数据

SELECT a.*

FROM AuthApplySignTbl a, ( SELECT MAX (addTime) start_time,houseId FROM AuthApplySignTbl GROUP BY houseId ) b

WHERE a.addTime = b.start_time AND a.houseId = b.houseid and a.isDel=0

ORDER BY a.houseid

--CASE 用法1

CASE A.hasKey WHEN 0 THEN '无' WHEN 1 THEN '有'WHEN 2 THEN '审核中' ELSE '未知' END 钥匙

--CASE 用法2

SELECT

CASE WHEN houseId <= 500 THEN '1'

WHEN houseId > 500 AND houseId <= 600 THEN '2'

WHEN houseId > 600 AND houseId <= 800 THEN '3'

WHEN houseId > 800 AND houseId <= 1000 THEN '4'

ELSE 12 END qqq

FROM AuthApplySignTbl

--更新操作

UPDATE B SET B.hasKey=1,B.keyExpire=A.keyExpire

--select *

FROM [HouseKeyTbl] A

INNER JOIN HouseInfoTbl B ON A.houseId=B.id

WHERE A.ID=@chkId

UPDATE B SET B.id=@groupId

--SELECT *

FROM HouseCltGrpTbl A WITH(NOLOCK) --B.zj_id

LEFT JOIN HouseCollectTbl B WITH(NOLOCK) ON A.id=B.id AND B.isDel=0

WHERE A.isDel=0 AND A.PerId=@addper AND B.HouseCode in((SELECT T.txt FROM SplitstrFunc(@houseIdStr,',') T))

======================================表操作=========================

--删除表数据 清除id

truncate table [dbo].[FinaCollectionAccountTbl]

--表备份

select * into [BakTable]..HouseDisplaceTblBak20190215 from [operationdb]..HouseDisplaceTbl

select * into RentReviewLabelTbl from HouseReviewLabelTbl where 1=2

--添加字段并从1开始自增

alter table [HouseCollectTbl] add zj_id int identity(1,1)

--添加主键

alter table [HouseCollectTbl] add constraint pk_zj_id primary key(zj_id)

--添加字段

ALTER TABLE [operationdb].[dbo].[HouseCollectTbl]

ADD zj_id int

--插入临时表

SELECT A.id,A.HouseCode,a.isDel,a.advantage

INTO #Tempdate

FROM [operationdb].[dbo].[HouseCollectTbl] A

======================================查询=========================

--

select J.名称,J.id,J.name

from(

select

J.id,

J.name,

名称 = (

stuff((select ',' + CONVERT(VARCHAR(20),seatID) from ( select A.id,C.name,D.seatID from [ReNameTbl] A

left join [dbo].[CommReSchoolTbl] B on A.id=B.reID and B.isDel=0

left join [dbo].[FixedSchoolNameTbl] C on B.schoolID=C.id and C.del=0

left join [dbo].[CommSeatSchoolTbl] D on B.id=D.rsID and D.isDel=0

where A.del=0

) t where t.id = J.id

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

)

from ( select A.id,C.name,D.seatID from [ReNameTbl] A

left join [dbo].[CommReSchoolTbl] B on A.id=B.reID and B.isDel=0

left join [dbo].[FixedSchoolNameTbl] C on B.schoolID=C.id and C.del=0

left join [dbo].[CommSeatSchoolTbl] D on B.id=D.rsID and D.isDel=0

where A.del=0 ) as J

group by J.id,J.name

) J

where J.id=6299

--

select K.id,K.名称 from(

select

I.id,

名称 = (

stuff((select ',' + CONVERT(VARCHAR(20),name)+'('

+J.名称

+')' from (select A.id,C.name from [ReNameTbl] A

left join [dbo].[CommReSchoolTbl] B on A.id=B.reID and B.isDel=0

left join [dbo].[FixedSchoolNameTbl] C on B.schoolID=C.id and C.del=0

where A.del=0

group by A.id,C.name

) t where t.id = I.id

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

)

from (select A.id ,C.name from [ReNameTbl] A

left join [dbo].[CommReSchoolTbl] B on A.id=B.reID and B.isDel=0

left join [dbo].[FixedSchoolNameTbl] C on B.schoolID=C.id and C.del=0

where A.del=0

group by A.id,C.name) as I

LEFT join (select J.名称,J.id,J.name

from(

select

J.id,

J.name,

名称 = (

stuff((select ',' + CONVERT(VARCHAR(20),seatID) from ( select A.id,C.name,D.seatID from [ReNameTbl] A

left join [dbo].[CommReSchoolTbl] B on A.id=B.reID and B.isDel=0

left join [dbo].[FixedSchoolNameTbl] C on B.schoolID=C.id and C.del=0

left join [dbo].[CommSeatSchoolTbl] D on B.id=D.rsID and D.isDel=0

where A.del=0

) t where t.id = J.id

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

)

from ( select A.id,C.name,D.seatID from [ReNameTbl] A

left join [dbo].[CommReSchoolTbl] B on A.id=B.reID and B.isDel=0

left join [dbo].[FixedSchoolNameTbl] C on B.schoolID=C.id and C.del=0

left join [dbo].[CommSeatSchoolTbl] D on B.id=D.rsID and D.isDel=0

where A.del=0 ) as J

group by J.id,J.name

) J) J on I.id=J.id

group by I.id,J.名称

) K

where K.id=6299

--商圈

select H.reId,H.名称

from(

select

J.reId,

名称 = (

stuff((select ',' + CONVERT(VARCHAR(20),name) from ( select A.reID, B.name from commrebusinessdisttbl A

left join AccountManager..BaseBusinessDistrictTbl B on A.businessDistId=B.id and B.isDel=0

where A.isDel=0

) t where t.reId = J.reId

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

)

from ( select A.reID, B.name from commrebusinessdisttbl A

left join AccountManager..BaseBusinessDistrictTbl B on A.businessDistId=B.id and B.isDel=0

where A.isDel=0 ) as J

group by J.reId,J.name

) H

--where J.reId=6299