数据如下:
产品表:
A001
A002
A003
A004
A005
BOM表:
A001由1个B、1个C组成
A002由1个C、2个D组成
A003由1个E、1个F组成
A004由2个C、2个F组成
A005由2个B、1个F组成
价格表:
物料 价格
B 1
C 2
D 2
E 1
F 3
公式:成品单价 = 物料单价*单位数量if object_id(N'test','U') is not null
drop table test
go
select * into test
from
(select [name]=N'A001'
union all
select N'A002'
union all
select N'A003'
union all
select N'A004'
union all
select N'A005')X
go
if object_id(N'test2','U') is not null
drop table test2
go
select * into test2
from
(select [name]=N'A001',bom='B',acount=1
union all
select [name]=N'A001',bom='C',acount=1
UNION ALL
select [name]=N'A002',bom='C',acount=1
union all
select [name]=N'A002',bom='D',acount=2
UNION ALL
select [name]=N'A003',bom='E',acount=1
union all
select [name]=N'A003',bom='F',acount=1
UNION ALL
select [name]=N'A004',bom='C',acount=1
union all
select [name]=N'A004',bom='F',acount=2
UNION ALL
select [name]=N'A005',bom='B',acount=2
union all
select [name]=N'A005',bom='F',acount=1)X
GO
if object_id(N'test3','U') is not null
drop table test3
go
SELECT * INTO TEST3
FROM
(SELECT PROJECT='B',CASH=1
UNION ALL
SELECT PROJECT='C',CASH=2
UNION ALL
SELECT PROJECT='D',CASH=2
UNION ALL
SELECT PROJECT='E',CASH=1
UNION ALL
SELECT PROJECT='F',CASH=3)X
CREATE FUNCTION TESTING(@NAME VARCHAR(4))RETURNS TABLE
AS
RETURN
WITH XWJ
AS
(SELECT [name],BOM,ACOUNT,CASH,px=1 FROM TEST2 A
INNER JOIN TEST3 B ON A.BOM=B.PROJECT AND [NAME]=@NAME)
,
XWJ2
AS
(SELECT [NAME],BOM=null,ACOUNT=null,[cash]=sum(ACOUNT*CASH),px=2 FROM XWJ
group by [name]
)
select * from
(select * from xwj
union all
select * from xwj2)x
go
select TEST.[NAME],BOM,ACOUNT,CASH from test
outer apply TESTING(test.name) as b
ORDER BY TEST.[NAME],PXNAME BOM ACOUNT CASH
---- ---- ----------- -----------
A001 B 1 1
A001 C 1 2
A001 NULL NULL 3
A002 C 1 2
A002 D 2 2
A002 NULL NULL 6
A003 E 1 1
A003 F 1 3
A003 NULL NULL 4
A004 C 1 2
A004 F 2 3
A004 NULL NULL 8
A005 B 2 1
A005 F 1 3
A005 NULL NULL 5(15 行受影响)
select TEST.[NAME],BOM,ACOUNT,CASH from test
outer apply TESTING(test.name) as b
ORDER BY TEST.[NAME],PX
错误信息如下:
-------------------------------------
消息 102,级别 15,状态 1,第 9 行
'.' 附近有语法错误。
-------------------------------------
好像是函数TESTING(test.name)不能这样引用导致,我如果直接输入TESTING('A001')就没有问题,但这不是我要的结果,我要的是所有产品的结果,谢谢!!!
apply 表运算