【sql】sql一些摘要:1、临时表。2、插入by查询(和case when then)。3、更新by子查询。4、linq的select实体内赋值时尽量少使用linq方法。
--1、临时表(DECLARE @details Table())
DECLARE @details Table(DTaskDetailsId [uniqueidentifier] NOT NULL,--任务id DAdvertCompanyFloorId [uniqueidentifier] NOT NULL,--楼盘id DFloorDiscName [nvarchar](100) NOT NULL,--楼盘名 DAdvertCompanyPointId [uniqueidentifier] NOT NULL,--点位id DMediaName [nvarchar](30) NOT NULL,--媒体位 DElevator [nvarchar](30) NOT NULL,--电梯 DUnitName [nvarchar](100) NOT NULL,--门洞 DFloorUnit [nvarchar](30) NOT NULL,--楼栋 DRegionName [nvarchar](100) NOT NULL --区域名称 )
--2、插入by查询(insert select)(和case when then)--条件中尽量不要(字符+、转换类型、isnotnull)操作字段本身
insert into @details select top (@top) d.[TaskDetailsId] ,CASE when acf.AdvertCompanyFloorId is null then @defaultGuid else acf.AdvertCompanyFloorId end ,d.[FloorDiscName] ,CASE when acpp.AdvertCompanyPointId is null then @defaultGuid else acpp.AdvertCompanyPointId end ,d.[MediaName] ,d.[Elevator] ,d.[UnitName] ,d.[FloorUnit] ,d.RegionName from Task_StageDetails d left join Advert_Company_FloorInfo acf on acf.AdvertCompanyFloorName =d.FloorDiscName and acf.AdvertCompanyId=@advertCompanyId left join [Advert_Company_PointPositionInfo] acpp on d.FloorUnit = acpp.TungName and d.UnitName = acpp.UnitName and (d.Elevator = acpp.ElevatorName or d.Elevator = acpp.StoreyName) and d.MediaName = acpp.MediaName and acf.AdvertCompanyFloorId=acpp.AdvertCompanyFloorId where PlanId=@planId and d.AdvertCompanyPointId=@defaultGuid
--3、更新by子查询(update selec)
--test select * from test1 --id number sort s --1 1 1 2021-01-16 18:02:40.193 --2 2 9 2021-01-16 18:02:40.193 --3 3 3 2021-01-16 18:02:40.193 select * from test2 --id number sort s -- 1 1 1 2020-09-19 15:25:07.007 -- 2 2 2 2020-09-19 15:25:07.007 update test1 set sort = (select top 1 sort from test2 where test1.number=test2.number ) where number in ( select t1.number from test1 t1 inner join test2 t2 on t1.number=t2.number ) go select * from test1 --id number sort s --1 1 1 2021-01-16 18:02:40.193 --2 2 2 2021-01-16 18:02:40.193 --3 3 3 2021-01-16 18:02:40.193 select * from test2 --id number sort s -- 1 1 1 2020-09-19 15:25:07.007 -- 2 2 2 2020-09-19 15:25:07.007
--4、linq的select实体内赋值时尽量少使用linq方法，会派生表后再查询一次，数据量大或者有多个linq方法时很慢。
--例如：下面linq中select的//detailsEmp.FirstOrDefault、// detailsEmp.Count,会执行多次查询Task_StageDetails。
--//join联表再Take(1)，和在select结果集中赋值查询FirstOrDefault，扫描计数逻辑读取次数是一样的。都是子查询TOP 1，再OUTER APPLY。
items = (from img in db.Set<Task_StagePlanImageName>() join plan in db.Set<Task_StagePlanInfo>() on img.PlanId equals plan.PlanId join details in db.Set<Task_StageDetails>().Where(where_detail) on new { img.PlanId, img.ImageName } equals new { details.PlanId, ImageName = details.NewAdvertName } join collect in db.Set<Task_JobNumberCollectImage>() on new { img.Id } equals new { Id = collect.ImageId } into collectEmp from collect in collectEmp.DefaultIfEmpty() select new View_Task_JobNumberCollectImage() { Plan = plan, ImageModel = img, ImageUrl = "",//detailsEmp.FirstOrDefault().NewAdvertImage, ImageTaskCount =0,// detailsEmp.Count(x => x.TaskDetailsId != Guid.Empty), JobNumberCollectImageCount = collect.Count, MediaSpecs = "",//detailsEmp.FirstOrDefault().MediaSpecs, CompanyFloorId = floorIdsIsNull ? Guid.Empty : details.CompanyFloorId, } ).Where(where_base);
--差linq生成的慢sql。
SELECT [Project8].[Sum] AS [Sum], [Project8].[PlanId1] AS [PlanId], [Project8].[ReleaseState] AS [ReleaseState], [Project8].[PlanNumber] AS [PlanNumber], [Project8].[PlanName] AS [PlanName], [Project8].[InstallTime] AS [InstallTime], [Project8].[ReleaseCount] AS [ReleaseCount], [Project8].[NotTakeCount] AS [NotTakeCount], [Project8].[NotSubmitCount] AS [NotSubmitCount], [Project8].[InstallSuccessCount] AS [InstallSuccessCount], [Project8].[FailEndCount] AS [FailEndCount], [Project8].[NotConfirmCount] AS [NotConfirmCount], [Project8].[CreateTime] AS [CreateTime], [Project8].[CreateUserName] AS [CreateUserName], [Project8].[TrialTime] AS [TrialTime], [Project8].[AdvertCompanyId] AS [AdvertCompanyId], [Project8].[CompanyId] AS [CompanyId], [Project8].[IssueState] AS [IssueState], [Project8].[InstallJobIds] AS [InstallJobIds], [Project8].[StartTime] AS [StartTime], [Project8].[EndTime] AS [EndTime], [Project8].[PlanBatchId] AS [PlanBatchId], [Project8].[Id] AS [Id], [Project8].[PlanId] AS [PlanId1], [Project8].[ImageName] AS [ImageName], [Project8].[CollectCount] AS [CollectCount], [Project8].[Number] AS [Number], [Project8].[C1] AS [C1], [Project8].[C3] AS [C2], [Project8].[Count] AS [Count], [Project8].[C2] AS [C3], CASE WHEN (@p__linq__2 = 1) THEN @p__linq__3 ELSE [Project8].[CompanyFloorId] END AS [C4] FROM ( SELECT [Project7].[Id] AS [Id], [Project7].[PlanId] AS [PlanId], [Project7].[ImageName] AS [ImageName], [Project7].[Sum] AS [Sum], [Project7].[CollectCount] AS [CollectCount], [Project7].[Number] AS [Number], [Project7].[PlanId1] AS [PlanId1], [Project7].[ReleaseState] AS [ReleaseState], [Project7].[PlanNumber] AS [PlanNumber], [Project7].[PlanName] AS [PlanName], [Project7].[InstallTime] AS [InstallTime], [Project7].[ReleaseCount] AS [ReleaseCount], [Project7].[NotTakeCount] AS [NotTakeCount], [Project7].[NotSubmitCount] AS [NotSubmitCount], [Project7].[InstallSuccessCount] AS [InstallSuccessCount], [Project7].[FailEndCount] AS [FailEndCount], [Project7].[NotConfirmCount] AS [NotConfirmCount], [Project7].[CreateTime] AS [CreateTime], [Project7].[CreateUserName] AS [CreateUserName], [Project7].[TrialTime] AS [TrialTime], [Project7].[AdvertCompanyId] AS [AdvertCompanyId], [Project7].[CompanyId] AS [CompanyId], [Project7].[IssueState] AS [IssueState], [Project7].[InstallJobIds] AS [InstallJobIds], [Project7].[StartTime] AS [StartTime], [Project7].[EndTime] AS [EndTime], [Project7].[PlanBatchId] AS [PlanBatchId], [Project7].[CompanyFloorId] AS [CompanyFloorId], [Project7].[Count] AS [Count], [Project7].[C1] AS [C1], [Project7].[C2] AS [C2], (SELECT COUNT(1) AS [A1] FROM [dbo].[Task_StageDetails] AS [Extent7] WHERE ([Extent7].[InstallJobNumberId] = @p__linq__0) AND ([Project7].[PlanId] = [Extent7].[PlanId]) AND ([Project7].[ImageName] = [Extent7].[NewAdvertName]) AND ([Extent7].[TaskDetailsId] <> @p__linq__1)) AS [C3] FROM ( SELECT [Project6].[Id] AS [Id], [Project6].[PlanId] AS [PlanId], [Project6].[ImageName] AS [ImageName], [Project6].[Sum] AS [Sum], [Project6].[CollectCount] AS [CollectCount], [Project6].[Number] AS [Number], [Project6].[PlanId1] AS [PlanId1], [Project6].[ReleaseState] AS [ReleaseState], [Project6].[PlanNumber] AS [PlanNumber], [Project6].[PlanName] AS [PlanName], [Project6].[InstallTime] AS [InstallTime], [Project6].[ReleaseCount] AS [ReleaseCount], [Project6].[NotTakeCount] AS [NotTakeCount], [Project6].[NotSubmitCount] AS [NotSubmitCount], [Project6].[InstallSuccessCount] AS [InstallSuccessCount], [Project6].[FailEndCount] AS [FailEndCount], [Project6].[NotConfirmCount] AS [NotConfirmCount], [Project6].[CreateTime] AS [CreateTime], [Project6].[CreateUserName] AS [CreateUserName], [Project6].[TrialTime] AS [TrialTime], [Project6].[AdvertCompanyId] AS [AdvertCompanyId], [Project6].[CompanyId] AS [CompanyId], [Project6].[IssueState] AS [IssueState], [Project6].[InstallJobIds] AS [InstallJobIds], [Project6].[StartTime] AS [StartTime], [Project6].[EndTime] AS [EndTime], [Project6].[PlanBatchId] AS [PlanBatchId], [Project6].[CompanyFloorId] AS [CompanyFloorId], [Project6].[Count] AS [Count], [Project6].[C1] AS [C1], [Project6].[C2] AS [C2] FROM ( SELECT [Project4].[Id] AS [Id], [Project4].[PlanId] AS [PlanId], [Project4].[ImageName] AS [ImageName], [Project4].[Sum] AS [Sum], [Project4].[CollectCount] AS [CollectCount], [Project4].[Number] AS [Number], [Project4].[PlanId1] AS [PlanId1], [Project4].[ReleaseState] AS [ReleaseState], [Project4].[PlanNumber] AS [PlanNumber], [Project4].[PlanName] AS [PlanName], [Project4].[InstallTime] AS [InstallTime], [Project4].[ReleaseCount] AS [ReleaseCount], [Project4].[NotTakeCount] AS [NotTakeCount], [Project4].[NotSubmitCount] AS [NotSubmitCount], [Project4].[InstallSuccessCount] AS [InstallSuccessCount], [Project4].[FailEndCount] AS [FailEndCount], [Project4].[NotConfirmCount] AS [NotConfirmCount], [Project4].[CreateTime] AS [CreateTime], [Project4].[CreateUserName] AS [CreateUserName], [Project4].[TrialTime] AS [TrialTime], [Project4].[AdvertCompanyId] AS [AdvertCompanyId], [Project4].[CompanyId] AS [CompanyId], [Project4].[IssueState] AS [IssueState], [Project4].[InstallJobIds] AS [InstallJobIds], [Project4].[StartTime] AS [StartTime], [Project4].[EndTime] AS [EndTime], [Project4].[PlanBatchId] AS [PlanBatchId], [Project4].[CompanyFloorId] AS [CompanyFloorId], [Project4].[Count] AS [Count], [Project4].[C1] AS [C1], (SELECT TOP (1) [Extent6].[MediaSpecs] AS [MediaSpecs] FROM [dbo].[Task_StageDetails] AS [Extent6] WHERE ([Extent6].[InstallJobNumberId] = @p__linq__0) AND ([Project4].[PlanId] = [Extent6].[PlanId]) AND ([Project4].[ImageName] = [Extent6].[NewAdvertName])) AS [C2] FROM ( SELECT [Project3].[Id] AS [Id], [Project3].[PlanId] AS [PlanId], [Project3].[ImageName] AS [ImageName], [Project3].[Sum] AS [Sum], [Project3].[CollectCount] AS [CollectCount], [Project3].[Number] AS [Number], [Project3].[PlanId1] AS [PlanId1], [Project3].[ReleaseState] AS [ReleaseState], [Project3].[PlanNumber] AS [PlanNumber], [Project3].[PlanName] AS [PlanName], [Project3].[InstallTime] AS [InstallTime], [Project3].[ReleaseCount] AS [ReleaseCount], [Project3].[NotTakeCount] AS [NotTakeCount], [Project3].[NotSubmitCount] AS [NotSubmitCount], [Project3].[InstallSuccessCount] AS [InstallSuccessCount], [Project3].[FailEndCount] AS [FailEndCount], [Project3].[NotConfirmCount] AS [NotConfirmCount], [Project3].[CreateTime] AS [CreateTime], [Project3].[CreateUserName] AS [CreateUserName], [Project3].[TrialTime] AS [TrialTime], [Project3].[AdvertCompanyId] AS [AdvertCompanyId], [Project3].[CompanyId] AS [CompanyId], [Project3].[IssueState] AS [IssueState], [Project3].[InstallJobIds] AS [InstallJobIds], [Project3].[StartTime] AS [StartTime], [Project3].[EndTime] AS [EndTime], [Project3].[PlanBatchId] AS [PlanBatchId], [Project3].[CompanyFloorId] AS [CompanyFloorId], [Project3].[Count] AS [Count], [Project3].[C1] AS [C1] FROM ( SELECT [Project1].[Id] AS [Id], [Project1].[PlanId] AS [PlanId], [Project1].[ImageName] AS [ImageName], [Project1].[Sum] AS [Sum], [Project1].[CollectCount] AS [CollectCount], [Project1].[Number] AS [Number], [Project1].[PlanId1] AS [PlanId1], [Project1].[ReleaseState] AS [ReleaseState], [Project1].[PlanNumber] AS [PlanNumber], [Project1].[PlanName] AS [PlanName], [Project1].[InstallTime] AS [InstallTime], [Project1].[ReleaseCount] AS [ReleaseCount], [Project1].[NotTakeCount] AS [NotTakeCount], [Project1].[NotSubmitCount] AS [NotSubmitCount], [Project1].[InstallSuccessCount] AS [InstallSuccessCount], [Project1].[FailEndCount] AS [FailEndCount], [Project1].[NotConfirmCount] AS [NotConfirmCount], [Project1].[CreateTime] AS [CreateTime], [Project1].[CreateUserName] AS [CreateUserName], [Project1].[TrialTime] AS [TrialTime], [Project1].[AdvertCompanyId] AS [AdvertCompanyId], [Project1].[CompanyId] AS [CompanyId], [Project1].[IssueState] AS [IssueState], [Project1].[InstallJobIds] AS [InstallJobIds], [Project1].[StartTime] AS [StartTime], [Project1].[EndTime] AS [EndTime], [Project1].[PlanBatchId] AS [PlanBatchId], [Project1].[CompanyFloorId] AS [CompanyFloorId], [Project1].[Count] AS [Count], (SELECT TOP (1) [Extent5].[NewAdvertImage] AS [NewAdvertImage] FROM [dbo].[Task_StageDetails] AS [Extent5] WHERE ([Extent5].[InstallJobNumberId] = @p__linq__0) AND ([Project1].[PlanId] = [Extent5].[PlanId]) AND ([Project1].[ImageName] = [Extent5].[NewAdvertName])) AS [C1] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[PlanId] AS [PlanId], [Extent1].[ImageName] AS [ImageName], [Extent1].[Sum] AS [Sum], [Extent1].[CollectCount] AS [CollectCount], [Extent1].[Number] AS [Number], [Extent2].[PlanId] AS [PlanId1], [Extent2].[ReleaseState] AS [ReleaseState], [Extent2].[PlanNumber] AS [PlanNumber], [Extent2].[PlanName] AS [PlanName], [Extent2].[InstallTime] AS [InstallTime], [Extent2].[ReleaseCount] AS [ReleaseCount], [Extent2].[NotTakeCount] AS [NotTakeCount], [Extent2].[NotSubmitCount] AS [NotSubmitCount], [Extent2].[InstallSuccessCount] AS [InstallSuccessCount], [Extent2].[FailEndCount] AS [FailEndCount], [Extent2].[NotConfirmCount] AS [NotConfirmCount], [Extent2].[CreateTime] AS [CreateTime], [Extent2].[CreateUserName] AS [CreateUserName], [Extent2].[TrialTime] AS [TrialTime], [Extent2].[AdvertCompanyId] AS [AdvertCompanyId], [Extent2].[CompanyId] AS [CompanyId], [Extent2].[IssueState] AS [IssueState], [Extent2].[InstallJobIds] AS [InstallJobIds], [Extent2].[StartTime] AS [StartTime], [Extent2].[EndTime] AS [EndTime], [Extent2].[PlanBatchId] AS [PlanBatchId], [Extent3].[CompanyFloorId] AS [CompanyFloorId], [Extent4].[Count] AS [Count] FROM [dbo].[Task_StagePlanImageName] AS [Extent1] INNER JOIN [dbo].[Task_StagePlanInfo] AS [Extent2] ON [Extent1].[PlanId] = [Extent2].[PlanId] LEFT OUTER JOIN [dbo].[Task_StageDetails] AS [Extent3] ON ([Extent3].[InstallJobNumberId] = @p__linq__0) AND ([Extent1].[PlanId] = [Extent3].[PlanId]) AND ([Extent1].[ImageName] = [Extent3].[NewAdvertName]) LEFT OUTER JOIN [dbo].[Task_JobNumberCollectImage] AS [Extent4] ON [Extent1].[Id] = [Extent4].[ImageId] ) AS [Project1] ) AS [Project3] ) AS [Project4] ) AS [Project6] ) AS [Project7] ) AS [Project8] WHERE [Project8].[C3] > 0
--5、sqlserver执行大量数据update select时，用中间表代替select的大量数据，避免执行慢(推测原因是阻塞和锁，以及每一次子查询的where执行消耗)。
--任务视图中间表更新 truncate table Table_TaskNoAdvertPoint insert into Table_TaskNoAdvertPoint select distinct d.[TaskDetailsId] as DTaskDetailsId ,d.PlanId as DPlanId ,CASE when acf.AdvertCompanyFloorId is null then '00000000-0000-0000-0000-000000000000' else acf.AdvertCompanyFloorId end as DAdvertCompanyFloorId --,acf.AdvertCompanyFloorId as DAdvertCompanyFloorId ,d.[FloorDiscName] as DFloorDiscName ,CASE when acpp.AdvertCompanyPointId is null then '00000000-0000-0000-0000-000000000000' else acpp.AdvertCompanyPointId end as DAdvertCompanyPointId --,acpp.AdvertCompanyPointId as DAdvertCompanyPointId ,d.[MediaName] as DMediaName ,d.[Elevator] as DElevator ,d.[UnitName] as DUnitName ,d.[FloorUnit] as DFloorUnit ,d.RegionName as DRegionName from Task_StageDetails d left join Advert_Company_FloorInfo acf on acf.AdvertCompanyFloorName =d.FloorDiscName and acf.AdvertCompanyId=d.AdvertCompanyId left join [Advert_Company_PointPositionInfo] acpp on d.FloorUnit = acpp.TungName and d.UnitName = acpp.UnitName and (d.Elevator = acpp.ElevatorName or d.Elevator = acpp.StoreyName) and d.MediaName = acpp.MediaName and acf.AdvertCompanyFloorId=acpp.AdvertCompanyFloorId where d.AdvertCompanyPointId= '00000000-0000-0000-0000-000000000000' and d.planid=@planId --匹配楼盘 update Task_StageDetails set AdvertCompanyFloorId = (select top 1 DAdvertCompanyFloorId from Table_TaskNoAdvertPoint d where Task_StageDetails.TaskDetailsId=d.DTaskDetailsId ) where Task_StageDetails.TaskDetailsId in (select DTaskDetailsId from Table_TaskNoAdvertPoint where DPlanId=@planId ) --匹配点位 update Task_StageDetails set AdvertCompanyPointId = (select top 1 DAdvertCompanyPointId from Table_TaskNoAdvertPoint d where Task_StageDetails.TaskDetailsId=d.DTaskDetailsId ) where Task_StageDetails.TaskDetailsId in (select DTaskDetailsId from Table_TaskNoAdvertPoint where DPlanId=@planId )
--6、查看sql执行的计划 set statistics profile on
SET STATISTICS TIME ON
SET STATISTICS IO ON
--7、查询是否区分大小写
设置某表某列: alter table TableName ALTER Column ColumnName nvarchar(200) COLLATE Chinese_PRC_CS_AS --(Chinese_PRC_CS_AS 区分，Chinese_PRC_CI_AS 不区分)
当次查询:
select * from TableName where ColumnName collate Chinese_PRC_CS_AS=‘value‘ --区分
select * from TableName where ColumnName collate Chinese_PRC_CS_AS like ‘value%‘ --不区分
或者转二进制查询。
--8、left join和where in相遇
//where存在in时，select列或group列包含left join的表字段，会慢。使用inner或where的in前面增加使用到leftjoin的表的字段增加条件 is not null。
例如
//--视图内有leftjoin，使用in条件需要再in前加left的非空条件
List<View_OrderGetNucleateView> nucleates = DBSession.IView_OrderGetNucleateViewDAL.GetListViewBy(x =>x.SampleNumber!=null && batchNumbers.Contains(x.BatchNumberNucleicAcid));
例如
SELECT dbo.Order_GetNucleate.BatchNumberNucleicAcid
,dbo.Order_GetNucleate.StateNucleicAcid
,dbo.Member_PetSampleBox.SampleNumber
,count(dbo.Member_PetSampleBox.SampleNumber) as [Count]
FROM dbo.Order_GetNucleate
left JOIN dbo.Member_PetSampleBox ON dbo.Member_PetSampleBox.PetIds = dbo.Order_GetNucleate.PetIds
AND dbo.Member_PetSampleBox.IsEffective = 1
AND dbo.Member_PetSampleBox.SampleType = dbo.Order_GetNucleate.SampleType
AND dbo.Member_PetSampleBox.BoxType != 1
--这里需要加is not null，或改成inner join
where dbo.Member_PetSampleBox.SampleNumber is not null and BatchNumberNucleicAcid in ('20230626-3C','20230626-2C','20230626-1Y','20230621-1Y','20230620-2C','20230620-1Y','20230619-1Y','20230618-2C','20230618-1Y','20230616-2C','20230616-1Y','20230615-1Y','20230614-2C','20230614-1Y','20230613-1Y','20230612-3C','20230612-2Y','20230612-1Y','20230609-1Y','20230608-1Y')
group by dbo.Order_GetNucleate.BatchNumberNucleicAcid
,dbo.Order_GetNucleate.StateNucleicAcid
,dbo.Member_PetSampleBox.SampleNumber
--9、修改表名列名
--例：
use SmartLockAccountAndOrderDB
go
--修改表名
exec sp_rename 'Lock_OpenConfig','Lock_OpenConfigOrder'
go
--修改表Lock_OpenConfigOrder的列名updated，改为Updated。
exec sp_rename 'Lock_OpenConfigOrder.updated','Updated','COLUMN'
