sqlserver 常用总结

补位：

不够10位左补0 select right(replicate('0',10)+rtrim(AREA_CODE),10) from SDJT_LXR

不够12位右补0 select cast(AREA_CODE as varchar)+REPLICATE('0',12-len(AREA_CODE)) from SDJT_LXR; 

case when:

类似mysql if： (CASE WHCD WHEN 1 THEN '小学' WHEN 2 THEN '初中' WHEN 3 THEN '高中/中专' WHEN 4 THEN '大专' 

WHEN 5 THEN '大学及以上' WHEN 6 THEN '未上学' END) WHICD

分页：

基础分页 连表超级慢

select top 10 a.XM, a.SFZHM, a.MZ, a.SQLX, a.KSJFSJ, 
a.PO_SFZHM, a.PO_XM, a.JTZZ, a.PO_MZ, b.XIAN_SHRQ from BUSINESS_JBXX a
LEFT JOIN BUSINESS_SPXX b
on a.JFDX_DM = b.JFDX_DM
where b.JFDX_DM not in (
    select top 10 a.JFDX_DM from BUSINESS_JBXX a
    LEFT JOIN BUSINESS_SPXX b
    on a.JFDX_DM = b.JFDX_DM
    and a.JFDX_DM is not null
    ORDER BY b.JFDX_DM
)
ORDER BY b.JFDX_DM

单表分页较快
select XM, SFZHM, MZ, SQLX, KSJFSJ, PO_SFZHM, PO_XM, JTZZ, PO_MZ from 
(
    select *,row_number() over  
    (
        order by  
        JFDX_DM asc
    ) n 
    from BUSINESS_JBXX  
) hhh 
where hhh.n > 0 
and hhh.n <= 10

连表分页 目前前面数据超快
WITH cte AS(
    select a.XM, a.SFZHM, a.MZ, a.SQLX, a.KSJFSJ, 
        a.PO_SFZHM, a.PO_XM, a.JTZZ, a.PO_MZ, b.XIAN_SHRQ,
        ROW_NUMBER() OVER(ORDER BY b.XIAN_SHRQ) AS RowNo
        from BUSINESS_JBXX a
        LEFT JOIN BUSINESS_SPXX b
        on a.JFDX_DM = b.JFDX_DM
        where XIAN_SHRQ is not null
)
SELECT * FROM cte WHERE RowNo BETWEEN 0 AND 10;

一列多行拼接一行：
SELECT
        top 1
        lxr_id = (
            stuff(
                (
                    SELECT
                        ',' + lxr_id
                    FROM
                        SDJT_LXR_UPLOAD FOR xml path ('')
                ),
                1,
                1,
                ''
            )
        )
    FROM
        SDJT_LXR_UPLOAD AS A

重复数据只去一条：
select s.*  
from ( 
    select *, row_number() over (partition by [jfdx_dm] order by [SYZN_CWSC_DM], [syzn_csny]) as group_idx  
    from SDJT_QSZN
) s
where s.group_idx = 1
 

表解锁：
select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'

declare @spid  int 
Set @spid  = 74 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

求百分比
    select convert(varchar,convert(decimal(10,2),1*1.0/945*100))+'%'

    insert into LIMIT(LIMIT_TYPE_ID, PAR_LIMIT_ID, NAME, ACTION_NAME, ACTION_PATH, ORDER_NUM, REMARK, CATEGORY, SYSTEM_ID, FLAG)
    values(2, 0, '帮扶联系人情况统计', 'sdjt', 'sd/jsp/bflxrCount.jsp', 17, '家庭信息统计', '扶助关怀统计分析', 5, 1)

外键删除
    select name from sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id 
    where f.parent_object_id=object_id('ManageRegionInfo');

    SELECT * FROM sys.foreign_keys
    Where referenced_object_id=OBJECT_ID('UserInfoSub');

    ALTER TABLE ManageRegionInfo DROP constraint FK__ManageReg__subId__10216507

查询text字段不为空 
    select * from SDJT_JBXX_FAMILYSIGN where  ISNULL(datalength (signfile),0) >0 

日期 一个月前并格式化 yyyy-MM-dd
CONVERT(varchar(100), dateadd(month,-1,getdate()), 23) 

根据生日计算年龄
select 
datediff(year,birthday,getdate())-case 
when 
datediff(day,getdate(),dateadd(year,datediff(year,birthday,getdate()),birthday))>=0 then 1 else 0
end age
from lz_con