• MSSsql常用代码1


    1.SQLServer 2008获取自身信息:
        select   b.[value] from sys.columns a left join sys.extended_properties b on a.object_id=b.major_id
                and a.column_id=b.minor_id inner join sysobjects c on a.column_id=c.id
                and a.[name]='列名' and c.[name]='表名'
                SELECT
                表名=case   when   a.colorder=1   then   d.name   else   ''   end,
                表说明=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''   end,
                字段序号=a.colorder,
                字段名=a.name,
                标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   ''else   ''   end,
                主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (
                SELECT   name   FROM   sysindexes   WHERE   indid   in(
                SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid
                )))   then   ''   else   ''   end,
                类型=b.name,
                占用字节数=a.length,
                长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
                小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
                允许空=case   when   a.isnullable=1   then   ''else   ''   end,
                默认值=isnull(e.text,''),
                字段说明=isnull(g.[value],'')
                FROM   syscolumns   a
                left   join   systypes   b   on   a.xusertype=b.xusertype
                inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'
                left   join   syscomments   e   on   a.cdefault=e.id
                left   join   sys.extended_properties   g   on   a.id=g.major_id   and   a.colid=g.minor_id
                left   join   sys.extended_properties   f   on   d.id=f.major_id   and   f.minor_id=0
                --where   d.name='orders'         --如果只查询指定表,加上此条件
                order   by   a.id,a.colorder
    2.截取时间年份:
        DATENAME(YEAR,CA_CreateOn)='2015'
    3.日期相等:
        Convert(varchar(10),level_time,120)='2014-07-17'
    4.随机取数:
        select top n * from tableA order by newid()
    5.insert into select:
        insert into qclist(qcid,svo_id,speaker_id,[status])
        select top 589 4 as qcid,level_uid as svo_id,tab1.id as speaker_id,0 as [status]  from (
        select speaker.id as id,speaker.level_uid,level_time from speaker left join [level] on speaker.id=[level].speaker_id where level_code>0 and [level].qc_status=0) tab1
        right join [user] on tab1.level_uid=[user].id where role_id=2
        and level_uid in (6,7) 
        and level_time between '2014-07-17' and '2016-01-24' order by newid()
    6.SELECT SCOPE_IDENTITY():返回上面操作的数据表最后row的IDENTITY 列的值;
    7.分类统计:
        select COUNT(distinct qc_id) as amend_count,qc_id from qclist where amend_status=0 group by qc_id;
    8.两表更新:
        update speaker set qc_status=qclist.status,qc_time=getdate(),qc_uid={$qc_userid},level_take_uid=0,vf_of_ta=1 from qclist where speaker.id=qclist.speaker_id and qclist.qc_id=$qc_id;
    9.修改表字段长度:
        alter table bu alter column realname nvarchar(200);
    10.添加字段:
        alter table speaker add  qc_status INT default 0,qc_time datetime,qc_uid int,BusinessID varchar(200),is_block int,credentials_type varchar(200),summary varchar(4000),level_status int,check_status INT default 0,check_num int default 0,is_unblock tinyint default    0;
    11.更改标识种子:
        DBCC CHECKIDENT (speaker,RESEED,80);
    12.SQL判断:
        select CONVERT(varchar(10),speaker.level_time,23) as CompleteDate,etms_code,
        speaker.name,institution.name,department.name,
        case occupation when 1 then 'Practicians' when 2 then 'Pharmacists' when 3 then 'Scientists'
        when 4 then 'Health Access Expert/Economist' when 5 then 'Paramedical/ others' end as Category,
        case when mm_level>0 then mm_level else experts_level end as Level,case when if_gov=2 then 'Y' else 'N' end as GOVBG
         from speaker
        left join level on speaker.id=level.speaker_id 
        left join institution on institution.id=speaker.institution_id
        left join department on department.id=speaker.department
        where experts_level is not null and experts_level<>0 
    13.union 使用:
        select inter_log.businessid,department.name,etms_code,'Reject' as [status] from inter_log 
        left join speaker on speaker.businessid=inter_log.businessid
        left join department on department.id=speaker.department
        where oper_type in ('驳回重审','驳回上级') and inter_log.status=1
        union
        select inter_log.businessid,department.name,etms_code,'Approve' as [status] from inter_log 
        left join speaker on speaker.businessid=inter_log.businessid
        left join department on department.id=speaker.department
        where oper_type='评级' and inter_log.status=1
        union
        select speaker.BusinessID,department.name,etms_code,'In Process' as [status] from speaker
        left join department on department.id=speaker.department
        where BusinessID not in (select BusinessID from inter_log where oper_type in ('驳回重审','驳回上级','评级')) and BusinessID is not null
    14.计算非重复数据:
        select name,[user].id,start_time,end_time,ISNULL(pass_percent,0) as pass_percent,qc.id as qc_id,isnull(reject_count,0) as reject_count,list_type from qc
        left join [user] on[user].id=qc.qc_userid 
        left join (select COUNT(id) as reject_count,qc_id from qclist where [status] in (2,4) group by qc_id) tab1 on tab1.qc_id=qc.id
        left join (select COUNT(distinct qc_id) as amend_count,qc_id from qclist where amend_status in (0,1) group by qc_id) tab2 on tab2.qc_id=qc.id
        where [user].id=23 and qc.status=0 and amend_count in (1,2)
  • 相关阅读:
    SQL Server 2008的审核功能
    在SSMS(2008)中对数据库对象进行搜索和筛选
    关于在ASP.NET应用程序中异步调用Web Service的解决方案
    SSIS 2008中的Data Profiler任务
    开始Windows Embedded之旅
    在Access中计算两个日期之间的工作日天数
    当PrintForm遇到"RPC服务不可用的错误”
    REST 的知识 【转载】
    在C#中实现类似Eval这类动态求值的函数【转载】
    行内数据
  • 原文地址:https://www.cnblogs.com/zhaobijin/p/5813284.html
Copyright © 2020-2023  润新知