22,206
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('item') IS NOT NULL DROP TABLE item
IF OBJECT_ID('bom') IS NOT NULL DROP TABLE bom
IF OBJECT_ID('bom_d') IS NOT NULL DROP TABLE bom_d
GO
CREATE TABLE item(
id INT IDENTITY(1,1) PRIMARY KEY,
item_name NVARCHAR(10),
item_code INT
)
GO
CREATE TABLE bom(
bom_id INT PRIMARY KEY,
bom_item INT,
bom_qtr INT
)
GO
CREATE TABLE bom_d(
bom_d_id INT PRIMARY KEY,
item INT,
bom_id INT,
bom_qtr INT
)
GO
INSERT INTO item VALUES ('成品A',1001)
INSERT INTO item VALUES ('成品A',1002)
INSERT INTO item VALUES ('半成品A',100101)
INSERT INTO item VALUES ('半成品B',100201)
--
INSERT INTO bom VALUES (1,1001,1)
INSERT INTO bom VALUES (2,1002,1)
--
INSERT INTO bom_d VALUES (1,100101,1,1)
INSERT INTO bom_d VALUES (2,100201,2,1)
--
SELECT
a.item_code AS [存货编码]
,a.item_name AS [存货名称]
,isnull(b.bom_qtr,c.bom_qtr) AS [存货耗用量]
FROM item AS a
LEFT JOIN bom AS b ON a.item_code=b.bom_item
LEFT JOIN bom_d AS c ON a.item_code=c.item
--测试数据
if not object_id(N'item') is null
drop table item
Go
Create table item([id] int,[item_name] nvarchar(24),[item_code] int)
Insert item
select 1,N'成品A',1001 union all
select 2,N'成品B',1002 union all
select 3,N'半成品A',100101 union all
select 4,N'半成品A',100201
GO
if not object_id(N'BOM') is null
drop table BOM
Go
Create table BOM([BOM_id] int,[BOM_item] int,[BOM_QTR] int)
Insert BOM
select 1,1001,1 union all
select 2,1002,1
GO
if not object_id(N'BOM_D') is null
drop table BOM_D
Go
Create table BOM_D([BOM_D_ID] int,[item] int,[BOM_ID] int,[BOM_QTR] INT)
Insert BOM_D
select 1,100101,1,1 union all
select 2,100201,2,1
Go
--测试数据结束
SELECT
item.item_code,
item.item_name,
ISNULL(BOM.BOM_QTR, BOM_D.BOM_QTR) AS BOM_QTR
FROM
item
LEFT JOIN
dbo.BOM
ON item_code = BOM_item
LEFT JOIN
dbo.BOM_D
ON item_code = item;