SqlServer常用语句整理

先记录下来 以后整理

1.常用语句

1.1update连表更新

update a set a.YCaseNo = a.WordName + ‘【‘+ convert(varchar,a.CaseYear) + ‘】‘+ ‘第‘+ convert(varchar,b.YCaseNo) +‘号‘
from [TCase_WordInfo] a inner join dbo.TCase_DetailInfo b on a.CSerialNo = b.CSerialNo

2.统计

select a.CaseYear,e.Data as keptdate,COUNT(distinct(a.CSerialNo)) as words,COUNT(distinct( c.VolumeId)) as volumns,COUNT(distinct(d.SerialNo)) as documents from dbo.TCase_WordInfo a inner join dbo.TCase_BaseInfo b on a.CSerialNo=b.CSerialNo inner join dbo.TCase_DetailInfo f on b.CSerialNo=f.CSerialNo left join TCase_VolumeInfo c on a.CSerialNo = c.CSerialNo left join TCase_DocumentInfo d on c.VolumeId = d.VolumeId left join(select DataId, Data from dbo.TDictionary_BaseData where Id = ‘CAIS_02_001‘) e on b.KeepingTermId = e.DataId where d.IsDeleted=0  and f.Undertaker like ‘%任双添%‘  group by a.CaseYear,e.Data

3.存储过程

use Cais_DB;
go

--判断是否存在存储过程，如果存在则删除
if exists(select * from sys.procedures where name=‘‘)
drop procedure 存储过程名称;
go

--创建存储过程
create procedure Proc_StaticsWordInfo
@StartCaseYear int,
@EndCaseYear int,
@DataId int,
@Undertaker nvarchar(50)
as
begin
declare @Sql nvarchar(2000) = ‘‘
declare @Where nvarchar(1000) = ‘‘
set @Sql = ‘select a.CaseYear,COUNT(distinct(a.CSerialNo)) as words,e.Data as keptdate,COUNT(distinct( c.VolumeId)) as volumns,COUNT(distinct(d.SerialNo)) documents from dbo.TCase_WordInfo a inner join dbo.TCase_BaseInfo b on a.CSerialNo=b.CSerialNo left join TCase_VolumeInfo c on a.CSerialNo = c.CSerialNo left join TCase_DocumentInfo d on c.VolumeId=d.VolumeId left join (select DataId,Data from dbo.TDictionary_BaseData where Id = "CAIS_02_001") e on b.KeepingTermId=e.DataId‘
if(@StartCaseYear is not null and @StartCaseYear <> ‘‘ and @EndCaseYear is not null and @EndCaseYear <> ‘‘ )
set @Where = @Where + ‘ a.CaseYear > ‘ + @StartCaseYear + ‘ a.CaseYear < ‘ + @EndCaseYear
if(@DataId is not null and @DataId <> ‘‘)
set @Where = @Where + ‘ e.@DataId =‘ + @DataId
if(@Undertaker is not null and @Undertaker <> ‘‘)
set @Where = @Where + ‘ a.@@Undertaker =‘ + @Undertaker

set @Sql = @Sql + @Where
set @Sql = @Sql + ‘ group by a.CaseYear,e.Data ‘

end

SqlServer常用语句整理

原文：https://www.cnblogs.com/pengboke/p/14710277.html