SQL笔记

佚名 6年前 (2019-04-10) 随笔 1007人围观抢沙发百度已收录

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

======================================表操作=========================
--删除表数据 清除id
truncate table [dbo].[FinaCollectionAccountTbl] 

--表备份
select * into [BakTable]..HouseDisplaceTblBak20190215 from [operationdb]..HouseDisplaceTbl






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

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