子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--根据辅料名ID和采购单号查询采购单 总表
ALTER proc [dbo].[ERP_GetNumToAccessoryM]
@No varchar(50),
@T_AccessoryMaterialName_ID varchar(50)
as
declare @s varchar(8000)
set @s='select
(select Detail from T_SecondaryMaterialData where Id =T_OrderAccessoryNotice.T_SecondaryMaterialData_ID) as ''描述'',
Color as ''顏色'',
Color as ''染廠色號'',
Color as ''規格/型號'''
select @s=@s+', '''+BackUp2+''' =Max(case BackUp2 when '''+BackUp2+''' then Quantity
else '''' end)'
from T_OrderAccessoryNotice where [No]=@No and T_AccessoryMaterialName_ID=@T_AccessoryMaterialName_ID
exec(@s+',TransformUnit,sum(convert(float,Quantity)) as 總數,Price as 單價 ,(select distinct Taxrate from T_AccessoryQuoteRatify where MaterialName=(select MaterialName from T_AccessoryMaterialName where ID = T_OrderAccessoryNotice.T_AccessoryMaterialName_ID)) as 稅率 ,Remark as 備註 from T_OrderAccessoryNotice where [No]='''+@No+''' and T_AccessoryMaterialName_ID = '''+ @T_AccessoryMaterialName_ID +'''
group by Color,Price, T_OrderAccessoryNotice.T_AccessoryMaterialName_ID,TransformUnit,Remark,T_OrderAccessoryNotice.T_SecondaryMaterialData_ID')
exec [ERP_GetNumToAccessoryM] '20121101000','2887'
求多条数据或单挑数据的解决办法。
ALTER PROC [dbo].[ERP_GetNumToAccessoryM]
@No VARCHAR(50) ,
@T_AccessoryMaterialName_ID VARCHAR(50)
AS
DECLARE @s VARCHAR(8000)
SET @s = 'select
(select Detail from T_SecondaryMaterialData where Id =T_OrderAccessoryNotice.T_SecondaryMaterialData_ID) as ''描述'',
Color as ''顏色'',
Color as ''染廠色號'',
Color as ''規格/型號'''
SELECT @s = @s + ', ''' + BackUp2 + ''' =Max(case BackUp2 when '''
+ BackUp2 + ''' then Quantity
else '''' end)'
FROM T_OrderAccessoryNotice
WHERE [No] = @No
AND T_AccessoryMaterialName_ID = @T_AccessoryMaterialName_ID
EXEC(@s+',TransformUnit,sum(convert(float,Quantity)) as 總數,Price as 單價 ,(select distinct Taxrate from T_AccessoryQuoteRatify where MaterialName in (select MaterialName from T_AccessoryMaterialName where ID = T_OrderAccessoryNotice.T_AccessoryMaterialName_ID)) as 稅率 ,Remark as 備註 from T_OrderAccessoryNotice where [No]='''+@No+''' and T_AccessoryMaterialName_ID = '''+ @T_AccessoryMaterialName_ID +'''
group by Color,Price, T_OrderAccessoryNotice.T_AccessoryMaterialName_ID,TransformUnit,Remark,T_OrderAccessoryNotice.T_SecondaryMaterialData_ID')
EXEC [ERP_GetNumToAccessoryM] '20121101000', '2887'
