if object_id(‘[P_GetPriceTableBuy]‘,‘P‘) is not null drop procedure P_GetPriceTableBuySET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================create PROCEDURE [dbo].[P_GetPriceTableBuy]@type nvarchar(50),@productId int=0,@ids nvarchar(1000)=‘‘,@keyword nvarchar(1000)=‘‘,@pageIndex int=1,@pageSize int=20ASBEGIN declare @id9 int ,@id10 int; set @id9=18 set @id10=19 --采购二维码表页面展示 if @type=‘GetPriceTableBuy‘ begin declare @sqlcount nvarchar(max); declare @psels nvarchar(max); declare @pcols nvarchar(max); set @psels=cast((select ‘,max(case t6.Name when ‘‘‘+Name+‘‘‘ then t4.ProductPropertyValueId else ‘‘0‘‘ end) as [‘+Name+‘ID],max(case t6.Name when ‘‘‘+Name+‘‘‘ then t4.Value else ‘‘0‘‘ end) as [‘+Name+‘]‘ from [ProductProperty] where ProductCatalogId=2 and ProductPropertyId not in(@id9,@id10) for xml path(‘‘)) as nvarchar(max)) set @pcols=cast((select ‘,[‘+name+‘ID],[‘+name+‘]‘ from [ProductProperty] where ProductCatalogId=2 and ProductPropertyId not in(@id9,@id10) for xml path(‘‘)) as nvarchar(max)) select Name from [ProductProperty] where ProductCatalogId=2 and ProductPropertyId not in(@id9,@id10) set @sqlcount=N‘ ;with cte as ( select ProductBrand_Name,Product_Name,ProductId ‘+@pcols+‘ from (select t2.ProductId,t2.Code,t2.Name as Product_Name,t1.BarCode,t2.ProductBrandId, t5.Name as ProductBrand_Name ‘+@psels+‘ from [ProductSku] as t1 join [Product] t2 on t1.ProductId=t2.ProductId join [ProductSkuProperty] t3 on t1.ProductSkuId=t3.ProductSkuId join [ProductPropertyValue] t4 on t3.ProductPropertyValueId=t4.ProductPropertyValueId join dbo.ProductBrand t5 on t2.ProductBrandId=t5.ProductBrandId join dbo.ProductProperty t6 on t4.ProductPropertyId=t6.ProductPropertyId where t1.[Status]=0 and t2.ProductCatalogId=2 and (t5.Name like ‘‘%‘+@keyword+‘%‘‘ or t2.Name like ‘‘%‘+@keyword+‘%‘‘) group by t2.ProductId,t2.Name,t1.BarCode,t2.ProductBrandId,t5.Name,t2.Code) as a group by ProductBrand_Name,Product_Name,ProductId‘+@pcols+‘ ), c as ( select ProductBrand_Name,Product_Name‘+@pcols+‘,ProductId,row_number() over(order by ProductId) as num from cte ) select * from c where num between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize union all select top 1 ‘‘‘‘,‘‘‘‘ ‘+@pcols+N‘,0, count(*) over()from cte ‘; --print(@sqlcount); exec sp_executesql @sqlcount,N‘@pageIndex int, @pageSize int‘,@pageIndex,@pageSize; end --采购二维码详情 if @type=‘UpdaPriceTableBuy‘ begin declare @sql nvarchar(max); declare @sql2 nvarchar(max); declare @sqlcou nvarchar(max)=N‘‘; declare @psels1 nvarchar(max); declare @pcols1 nvarchar(max); declare @where1 nvarchar(max); declare @w1 nvarchar(max); set @psels1=cast((select N‘,max(case t6.Name when ‘‘‘+Name+N‘‘‘ then t4.ProductPropertyValueId else 0 end) as [‘+Name+‘Id],max(case t6.Name when‘‘‘+Name+‘‘‘then t4.Value else ‘‘0‘‘ end) as [‘+Name+‘]‘ from [ProductProperty] where ProductCatalogId=2 and ProductPropertyId not in(@id9,@id10) for xml path(‘‘)) as nvarchar(max)) set @pcols1=cast((select N‘,[‘+name+N‘Id],[‘+Name+N‘]‘ from [ProductProperty] where ProductCatalogId=2 and ProductPropertyId not in(@id9,@id10) for xml path(‘‘)) as nvarchar(max)) set @where1=N‘ set @w1=cast((select ‘‘ and a.[‘‘+[name]+‘‘Id]=‘‘+cast(b.ProductPropertyValueId as nvarchar(10)) from [ProductProperty] a join ProductPropertyValue b on a.ProductPropertyId=b.ProductPropertyId where ProductCatalogId=2 and a.ProductPropertyId not in(@id9,@id10) and b.ProductPropertyValueId in (‘+@ids+‘) for xml path(‘‘‘‘)) as nvarchar(max)) ‘ exec sp_executesql @where1,N‘@id9 int,@id10 int,@w1 nvarchar(max) output‘,@id9,@id10,@w1 output; set @sql= cast((select N‘,max(case when bValue=‘‘‘+Value+N‘‘‘ then ptb.BuyPrice else 0 end) as [‘+value+N‘]‘ from ProductPropertyValue where ProductPropertyId=@id10 order by ProductPropertyValueId for xml path(‘‘)) as nvarchar(max)); set @sql2= cast((select N‘,max(case when bValue=‘‘‘+Value+N‘‘‘ then a2.ProductSkuId else 0 end) as [‘+value+N‘id]‘ from ProductPropertyValue where ProductPropertyId=@id10 order by ProductPropertyValueId for xml path(‘‘)) as nvarchar(max)); set @sqlcou+=N‘;with a1 as ( select a.ProductPropertyValueId as avid,a.Value as aValue,b.ProductPropertyValueId as bvid,b.Value as bValue from ProductPropertyValue a cross join ProductPropertyValue b where a.ProductPropertyId =@id9 and b.ProductPropertyId=@id10 ),a2 as ( select ProductSkuId,ProductId,Code,Product_Name,ProductBrandId,ProductBrand_Name ‘+@pcols1+N‘ ,sphereid,sphere,lenticularid,lenticular from (select t1.ProductSkuId,t2.ProductId,t2.Name as Product_Name,t1.BarCode,t2.Code,t2.ProductBrandId, t5.Name as ProductBrand_Name ‘+@psels1+N‘ ,MAX(case t6.Name when ‘‘球镜‘‘ then t4.ProductPropertyValueId else 0 end)sphereid, MAX(case t6.Name when ‘‘球镜‘‘ then t4.Value else ‘‘0‘‘ end)sphere, MAX(case t6.Name when ‘‘柱镜‘‘ then t4.ProductPropertyValueId else 0 end)lenticularid, MAX(case t6.Name when ‘‘柱镜‘‘ then t4.Value else ‘‘0‘‘ end)lenticular from [dbo].[ProductSku] as t1 join [Product] t2 on t1.ProductId=t2.ProductId join [ProductSkuProperty] t3 on t1.ProductSkuId=t3.ProductSkuId join [ProductPropertyValue] t4 on t3.ProductPropertyValueId=t4.ProductPropertyValueId join dbo.ProductBrand t5 on t2.ProductBrandId=t5.ProductBrandId join dbo.ProductProperty t6 on t4.ProductPropertyId=t6.ProductPropertyId where t2.ProductCatalogId=2 and t1.ProductId=@pId group by t2.ProductId,t2.Name,t1.BarCode,t2.ProductBrandId,t5.Name,t2.Code,t1.ProductSkuId) as a where 1=1 ‘+@w1+N‘ group by ProductSkuId,ProductId,Code,ProductBrandId,ProductBrand_Name,Product_Name ‘+@pcols1+N‘ ,sphereid,sphere,lenticularid,lenticular ) select a1.aValue‘+@sql+N‘ ‘+@sql2+N‘ from a1 left join a2 on a1.avid=a2.sphereid and a1.bvid=a2.lenticularid left join PriceTableBuy ptb on a2.ProductSkuId=ptb.ProductSkuId group by a1.avid,a1.aValue order by a1.avid‘; exec sp_executesql @sqlcou,N‘@id9 int,@id10 int,@pId int‘,@id9,@id10,@productId endEND

 结果图

最后拼接完成，并执行的SQL语句：

;with cte as ( select ProductBrand_Name,Product_Name,ProductId ,[材料ID],[材料],[折射率ID],[折射率],[曲率设计ID],[曲率设计],[阿贝数ID],[阿贝数],[加膜ID],[加膜],[类型ID],[类型],[定制ID],[定制],[年龄ID],[年龄] from (select t2.ProductId,t2.Code,t2.Name as Product_Name,t1.BarCode,t2.ProductBrandId, t5.Name as ProductBrand_Name ,max(case t6.Name when ‘材料‘ then t4.ProductPropertyValueId else ‘0‘ end) as [材料ID],max(case t6.Name when ‘材料‘ then t4.Value else ‘0‘ end) as [材料],max(case t6.Name when ‘折射率‘ then t4.ProductPropertyValueId else ‘0‘ end) as [折射率ID],max(case t6.Name when ‘折射率‘ then t4.Value else ‘0‘ end) as [折射率],max(case t6.Name when ‘曲率设计‘ then t4.ProductPropertyValueId else ‘0‘ end) as [曲率设计ID],max(case t6.Name when ‘曲率设计‘ then t4.Value else ‘0‘ end) as [曲率设计],max(case t6.Name when ‘阿贝数‘ then t4.ProductPropertyValueId else ‘0‘ end) as [阿贝数ID],max(case t6.Name when ‘阿贝数‘ then t4.Value else ‘0‘ end) as [阿贝数],max(case t6.Name when ‘加膜‘ then t4.ProductPropertyValueId else ‘0‘ end) as [加膜ID],max(case t6.Name when ‘加膜‘ then t4.Value else ‘0‘ end) as [加膜],max(case t6.Name when ‘类型‘ then t4.ProductPropertyValueId else ‘0‘ end) as [类型ID],max(case t6.Name when ‘类型‘ then t4.Value else ‘0‘ end) as [类型],max(case t6.Name when ‘定制‘ then t4.ProductPropertyValueId else ‘0‘ end) as [定制ID],max(case t6.Name when ‘定制‘ then t4.Value else ‘0‘ end) as [定制],max(case t6.Name when ‘年龄‘ then t4.ProductPropertyValueId else ‘0‘ end) as [年龄ID],max(case t6.Name when ‘年龄‘ then t4.Value else ‘0‘ end) as [年龄] from [ProductSku] as t1 join [Product] t2 on t1.ProductId=t2.ProductId join [ProductSkuProperty] t3 on t1.ProductSkuId=t3.ProductSkuId join [ProductPropertyValue] t4 on t3.ProductPropertyValueId=t4.ProductPropertyValueId join dbo.ProductBrand t5 on t2.ProductBrandId=t5.ProductBrandId join dbo.ProductProperty t6 on t4.ProductPropertyId=t6.ProductPropertyId where t1.[Status]=0 and t2.ProductCatalogId=2 and (t5.Name like ‘%%‘ or t2.Name like ‘%%‘) group by t2.ProductId,t2.Name,t1.BarCode,t2.ProductBrandId,t5.Name,t2.Code) as a group by ProductBrand_Name,Product_Name,ProductId,[材料ID],[材料],[折射率ID],[折射率],[曲率设计ID],[曲率设计],[阿贝数ID],[阿贝数],[加膜ID],[加膜],[类型ID],[类型],[定制ID],[定制],[年龄ID],[年龄] ), c as ( select ProductBrand_Name,Product_Name,[材料ID],[材料],[折射率ID],[折射率],[曲率设计ID],[曲率设计],[阿贝数ID],[阿贝数],[加膜ID],[加膜],[类型ID],[类型],[定制ID],[定制],[年龄ID],[年龄],ProductId,row_number() over(order by ProductId) as num from cte ) select * from c where num between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize union all select top 1 ‘‘,‘‘ ,[材料ID],[材料],[折射率ID],[折射率],[曲率设计ID],[曲率设计],[阿贝数ID],[阿贝数],[加膜ID],[加膜],[类型ID],[类型],[定制ID],[定制],[年龄ID],[年龄],0, count(*) over()from cte