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

 

4.多表连接查询及多列转一列

select * from(

select a.id as LrId,b.CSerialNo,c.[WordName] + '【' + CAST(c.[CaseYear] AS NVARCHAR(MAX)) + '】' + '第' + d.[YCaseNo] + '号' AS[FullYCaseNo],d.Litigant2,d.LawyerUnit,a.BorrowPerson,a.BorrowDate,a.CDNums,a.RegisterPerson,a.RegisterDate,a.AddMaterialName,a.AddLocation,a.PageNum,a.Remark,e.VolumesCount,e.VolumeCaptions,ROW_NUMBER() OVER(ORDER BY a.Id) AS RowIndex from TCase_LendRegist a
inner join TCase_LendRegistMapWordInfo b on a.Id = b.LRId
inner join TCase_WordInfo c on b.CSerialNo = c.CSerialNo
inner join TCase_DetailInfo d on b.CSerialNo = d.CSerialNo
left join(
select LRId,count(*) as VolumesCount,VolumeCaptions = (stuff((select ',' + VolumeCaption from TCase_LendRegistMapVolumeInfo where LRId = g.LRId for xml path('')),1,1,'')) from TCase_LendRegistMapVolumeInfo g group by LRId
)e on a.Id=e.LRId
)T

 

5.SqlServer建库建表

IF EXISTS (SELECT * FROM sysobjects WHERE name='CRM_LineCompanyConfig' )
DROP TABLE CRM_LineCompanyConfig
create table CRM_LineCompanyConfig(
Id int not null identity(1,1) primary key , --Id
ScheduleManagement int, --工期管理 0关闭 1开启
DesignSquareMeterByDay int, --每天设计多少平米
ProtocolAudit int,  --协议免审核
AuditDiscountLower decimal(18,2), --免审折扣下限
AuditDiscountUpper decimal(18,2), --免审折扣上限
CompanyCode int,  --公司编码 适用范围
CompanyName varchar(100),  --公司名
CreateName varchar(100),  --创建人
CreateAccount varchar(50),  --创建账号
CreateTime datetime --创建时间
)
GO
EXEC sp_addextendedproperty N'MS_Description', N'公司配置表', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig';
GO
EXEC sp_addextendedproperty N'MS_Description', N'id主键自增长', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'id';
GO
EXEC sp_addextendedproperty N'MS_Description', N'工期管理', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'ScheduleManagement';
GO
EXEC sp_addextendedproperty N'MS_Description', N'每天设计多少平米', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'DesignSquareMeterByDay';
GO
EXEC sp_addextendedproperty N'MS_Description', N'协议免审核', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'ProtocolAudit';
GO
EXEC sp_addextendedproperty N'MS_Description', N'免审折扣下限', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'AuditDiscountLower';
GO
EXEC sp_addextendedproperty N'MS_Description', N'免审折扣上限', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'AuditDiscountUpper';
GO
EXEC sp_addextendedproperty N'MS_Description', N'适用范围(公司编码)', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'CompanyCode';
GO
EXEC sp_addextendedproperty N'MS_Description', N'公司名', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'CompanyName';
GO
EXEC sp_addextendedproperty N'MS_Description', N'创建人', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'CreateName';
GO
EXEC sp_addextendedproperty N'MS_Description', N'创建账号', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'CreateAccount';
GO
EXEC sp_addextendedproperty N'MS_Description', N'创建时间', N'SCHEMA', N'dbo',N'TABLE', N'CRM_LineCompanyConfig', N'COLUMN', N'CreateTime';
GO
ALTER TABLE JZDATA..CRM_LineCompanyConfig ADD  DEFAULT (0) FOR [ScheduleManagement]
GO
ALTER TABLE JZDATA..CRM_LineCompanyConfig ADD  DEFAULT (0) FOR [DesignSquareMeterByDay]
GO
ALTER TABLE JZDATA..CRM_LineCompanyConfig ADD  DEFAULT (0) FOR [ProtocolAudit]
GO
ALTER TABLE JZDATA..CRM_LineCompanyConfig ADD  DEFAULT (0) FOR [AuditDiscountLower]
GO
ALTER TABLE JZDATA..CRM_LineCompanyConfig ADD  DEFAULT (0) FOR [AuditDiscountUpper]