深圳智汇创想科技有限公司

看下这个索引怎么优化下

DECLARE @CustomerID      INT= 1,
        @DevelopAdminIDs VARCHAR(max)= '7778,8042,563,725,1062,1222,2304,3418,3708,3723,3812,6250,6309,7522,7571,7636,7713,2356,3405,3469,3553,3788,3943,5229,5546,5574,6031,6361,6441,6845,7459,393,7697,7735,994,1027,1283,1282,1472,2078,2139,3353,3360,3466,3801,3945,4193,5599,6252,6408,6617,6846,7521';

--drop table if exists #tabIds;
--create table #tabIds(id int primary key);
--insert into #tabIds
--exec proc_string_split @DevelopAdminIDs,',';

SELECT Count(1)
FROM   (SELECT alias.[ID],
               alias.[CustomerID],
               alias.[SKU],
               alias.[TaskType],
               alias.[Status],
               alias.[SingleBarcode],
               alias.[ListID],
               alias.[PhotoAdminID],
               alias.[PhotoUserName],
               alias.[EnterAdminID],
               alias.[EnterUserName],
               alias.[EnterTime],
               alias.[ReceiveTime],
               alias.[ReceiveAdminID],
               alias.[ReceiveUserName],
               alias.[FinishAdminID],
               alias.[FinishUserName],
               alias.[FinishTime],
               alias.[HandleTime],
               alias.[PhotoDays],
               alias.[EditDays],
               alias.[TotalDays],
               alias.[ReturnAdminID],
               alias.[ReturnUserName],
               alias.[ReturnTime],
               alias.[WriteOffAdminID],
               alias.[WriteOffUserName],
               alias.[WriteOffTime],
               alias.[WarehouseId],
               alias.[DemoTaskNum],
               alias.[DemoAdminId],
               alias.[DemoUserName],
               alias.[DemoTime],
               alias.[DemoDays],
               alias.[ReturnConfirmedTime],
               alias.[ReturnConfirmedAdminId],
               alias.[ReturnConfirmedUserName],
               alias.[WriteOffReason],
               p.ProductProperty,
               pa.ImageTime,
               pa.DevelopAdminName,
               pa.EditTime  ProductAdminEditTime,
               pi.AddTime   ImageCheck_AddTime,
               pi.CheckTime ImageCheck_CheckTime,
               w.WarehouseName
        FROM   RB_Product_PhotoTask(NOLOCK) alias
               LEFT JOIN RB_Product_Admin(NOLOCK) pa
                      ON pa.CustomerID = alias.CustomerID
                         AND pa.SKU = alias.SKU
               inner join #tabIds t on pa.DevelopAdminID=t.id
               LEFT JOIN RB_Product_ImageCheck(NOLOCK) pi
                      ON pi.CustomerID = alias.CustomerID
                         AND pi.SKU = alias.SKU
               LEFT JOIN RB_Product (NOLOCK) p
                      ON p.CustomerID = alias.CustomerID
                         AND p.SKU = alias.SKU
               LEFT JOIN RB_Warehouse (NOLOCK) w
                      ON alias.CustomerID = w.CustomerID
                         AND alias.WarehouseId = w.ID
        WHERE  alias.CustomerID = @CustomerID
               --AND pa.DevelopAdminID IN (SELECT CONVERT(INT, item)
               --                          FROM   Charsplitforin(@DevelopAdminIDs, ','))

                                         ) AS PagerTable;

参考临时表方法，把临时表改为表值参数。

sql已调整

4.ErrorCode=N1557541

开发人员全选的时候有人属于2个部门，导致传到后台的id重复，插入临时表的primary key冲突。