SQL笔记

======================================函数操作=========================
--去空格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

--获取数据表中各个类别的最新数据（速度较快）

select * from SubscribedReplyRecord a where a.ReplyTime = (select MAX(b.ReplyTime)  from SubscribedReplyRecord b where a.OpenID=b.OpenID);

 

 

--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