以下是SQL脚本：
CREATE TABLE [dbo].[ICBOMChild](
[FBrNo] [varchar](10) NOT NULL,
[FEntryID] [int] NOT NULL,
[FInterID] [int] NOT NULL,
[FItemID] [int] NOT NULL,
[FAuxQty] [decimal](28, 10) NOT NULL,
[FQty] [decimal](28, 10) NOT NULL,
[FScrap] [decimal](28, 10) NOT NULL,
[FOperSN] [int] NOT NULL,
[FOperID] [int] NOT NULL,
[FMachinePos] [varchar](1000) NULL,
[FNote] [varchar](1000) NULL,
[FMaterielType] [int] NOT NULL,
[FMarshalType] [int] NOT NULL,
[FPercent] [decimal](28, 10) NOT NULL,
[FBeginDay] [datetime] NOT NULL,
[FEndDay] [datetime] NOT NULL,
[FOffSetDay] [decimal](28, 10) NOT NULL,
[FBackFlush] [int] NOT NULL,
[FStockID] [int] NULL,
[FSPID] [int] NOT NULL,
[FSupply] [smallint] NOT NULL,
[FUnitID] [int] NOT NULL,
[FAuxPropID] [int] NOT NULL,
[FPDMImportDate] [datetime] NULL,
[FPositionNo] [nvarchar](4000) NOT NULL,
[FItemSize] [nvarchar](255) NOT NULL,
[FItemSuite] [nvarchar](255) NOT NULL,
[FNote1] [nvarchar](255) NOT NULL,
[FNote2] [nvarchar](255) NOT NULL,
[FNote3] [nvarchar](255) NOT NULL,
[FHasChar] [smallint] NULL,
[FDetailID] [uniqueidentifier] NOT NULL,
[FEntryKey] [int] IDENTITY(1,1) NOT NULL,
[FCostPercentage] [decimal](6, 2) NULL,
CONSTRAINT [Prm_ICBOMChild] PRIMARY KEY CLUSTERED
(
[FInterID] ASC,
[FEntryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[ICBOM] Script Date: 06/18/2015 10:52:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ICBOM](
[FBrNo] [varchar](10) NOT NULL,
[FInterID] [int] NOT NULL,
[FBOMNumber] [varchar](300) NOT NULL,
[FImpMode] [smallint] NOT NULL,
[FUseStatus] [int] NULL,
[FVersion] [varchar](300) NOT NULL,
[FParentID] [int] NULL,
[FItemID] [int] NOT NULL,
[FQty] [decimal](28, 10) NOT NULL,
[FYield] [decimal](28, 10) NULL,
[FCheckID] [int] NULL,
[FCheckDate] [datetime] NULL,
[FOperatorID] [int] NULL,
[FEnterTime] [datetime] NOT NULL,
[FStatus] [smallint] NOT NULL,
[FCancellation] [bit] NOT NULL,
[FTranType] [int] NOT NULL,
[FRoutingID] [int] NOT NULL,
[FBomType] [int] NOT NULL,
[FCustID] [int] NOT NULL,
[FCustItemID] [int] NOT NULL,
[FAccessories] [int] NOT NULL,
[FNote] [varchar](300) NOT NULL,
[FUnitID] [int] NOT NULL,
[FAUXQTY] [decimal](28, 10) NOT NULL,
[FCheckerID] [int] NULL,
[FAudDate] [datetime] NULL,
[FEcnInterID] [int] NOT NULL,
[FBeenChecked] [bit] NOT NULL,
[FForbid] [smallint] NOT NULL,
[FAuxPropID] [int] NOT NULL,
[FPDMImportDate] [datetime] NULL,
[FBOMSkip] [smallint] NOT NULL,
[FClassTypeID] [int] NULL,
[FUserID] [int] NULL,
[FUseDate] [datetime] NULL,
[FPrintCount] [int] NOT NULL,
CONSTRAINT [Prm_ICBOM] PRIMARY KEY CLUSTERED
(
[FInterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[t_Item] Script Date: 06/18/2015 10:52:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_Item](
[FItemID] [int] NOT NULL,
[FItemClassID] [int] NOT NULL,
[FExternID] [int] NOT NULL,
[FNumber] [varchar](80) NOT NULL,
[FParentID] [int] NOT NULL,
[FLevel] [smallint] NOT NULL,
[FDetail] [bit] NOT NULL,
[FName] [varchar](255) NOT NULL,
[FUnUsed] [bit] NULL,
[FBrNo] [varchar](10) NOT NULL,
[FFullNumber] [varchar](80) NOT NULL,
[FDiff] [bit] NOT NULL,
[FDeleted] [smallint] NOT NULL,
[FShortNumber] [varchar](80) NULL,
[FFullName] [varchar](250) NULL,
[UUID] [uniqueidentifier] NOT NULL,
[FGRCommonID] [int] NOT NULL,
[FSystemType] [int] NOT NULL,
[FUseSign] [int] NOT NULL,
[FChkUserID] [int] NULL,
[FAccessory] [smallint] NOT NULL,
[FGrControl] [int] NOT NULL,
[FModifyTime] [timestamp] NOT NULL,
[FHavePicture] [smallint] NOT NULL,
以下是我写的SQL代码:
select
t.FNumber,T.FName as '名称',
I.FBOMNumber as 'BOM单号',
I.FAudDate as '审核时间',
I.FAuxQty as '数量',
I.FBomType as 'BOM单类型',
I.FCancellation as '作废标志',
I.FCheckDate as '新建时间',
I.FCheckerID as '审核人',
I.FCheckID as '新建人员',
I.FCustID as '客户代码',
I.FCustItemID as '客户物料内码',
I.FEcnInterID as 'ECN内码',
I.FEnterTime as '最近修改时间',
I.FForbid as '禁用',
I.FNote as '备注',
I.FOperatorID as '最近修改人',
I.FParentID as 'BOM上级代码',
I.FQty as '数量',
I.FStatus as '状态',
I.FunitID as '单位',
I.FUseStatus as '使用状态',
I.FVersion as '版本号',
I.FYield as '成品率'
from ICBOM I right join ICBOMCHILD ID on I.FInterID=ID.FInterID
left join t_item T ON T.FItemid=ID.FItemid
where I.FBOMNumber is not null
union
select
t.FNumber ,T.FName as '名称',
I.FBOMNumber as 'BOM单号',
I.FAudDate as '审核时间',
I.FAuxQty as '数量',
I.FBomType as 'BOM单类型',
I.FCancellation as '作废标志',
I.FCheckDate as '新建时间',
I.FCheckerID as '审核人',
I.FCheckID as '新建人员',
I.FCustID as '客户代码',
I.FCustItemID as '客户物料内码',
I.FEcnInterID as 'ECN内码',
I.FEnterTime as '最近修改时间',
I.FForbid as '禁用',
I.FNote as '备注',
I.FOperatorID as '最近修改人',
I.FParentID as 'BOM上级代码',
I.FQty as '数量',
I.FStatus as '状态',
I.FunitID as '单位',
I.FUseStatus as '使用状态',
I.FVersion as '版本号',
I.FYield as '成品率'
from ICBOM I right join ICBOMCHILD ID on I.FInterID=ID.FInterID
left join t_item T ON T.FItemid=I.FItemid
where I.FBOMNumber is not null
以上代码查询不了
------解决思路----------------------
SQL Server BOM展开方法整理
SQL写出带扩展数量的BOM多级清单