求教 金蝶自定义报表SQL语句实现，像第一个例子这样的过滤条件要怎么实现。，

[zym1325148]

-----------------lz_xiaohe------------20080427

Set Nocount on
Create Table #TempInventory(
[FBrNo] [varchar] (10) NOT NULL ,
[FItemID] [int] NOT NULL ,
[FBatchNo] [varchar] (200) NOT NULL ,
[FStockID] [int] NOT NULL ,
[FQty] [decimal](28, 10) NOT NULL ,
[FBal] [decimal](20, 2) NOT NULL ,
[FStockPlaceID] [int] NULL ,
[FKFPeriod] [int] NOT NULL Default(0),
[FKFDate] [varchar] (255) NOT NULL ,
[FMyKFDate] [varchar] (255),
[FStockTypeID] [Int] NOT NULL,
[FQtyLock] [decimal](28, 10) NOT NULL,
[FAuxPropID] [int] NOT NULL,
[FSecQty] [decimal](28, 10) NOT NULL
)
Insert Into #TempInventory
Select u1.FBrNo,u1.FItemID,u1.FBatchNo,u1.FStockID,u1.FQty,u1.FBal,u1.FStockPlaceID,
u1.FKFPeriod,ISNULL(u1.FKFDate,''),ISNULL(u1.FKFDate,''),500,u1.FQtyLock,u1.FAuxPropID,u1.FSecQty
From ICInventory u1 left join t_ICItem t1 on u1.FItemID = t1.FItemID
left join t_Stock t2 on u1.FStockID=t2.FItemID
left join t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID
left join t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID
left join t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID
where u1.FQty<>0 and t1.FDeleted=0

Insert Into #TempInventory
Select u1.FBrNo,u1.FItemID,u1.FBatchNo,u1.FStockID,u1.FQty,u1.FBal,u1.FStockPlaceID,
u1.FKFPeriod,ISNULL(u1.FKFDate,''),ISNULL(u1.FKFDate,''),u1.FStockTypeID,0,u1.FAuxPropID,u1.FSecQty
From POInventory u1 left join t_ICItem t1 on u1.FItemID = t1.FItemID
left join t_Stock t2 on u1.FStockID=t2.FItemID
left join t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID
left join t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID
left join t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID
where u1.FQty<>0 and t1.FDeleted=0



Select t1.FNumber AS FMaterialNumber,t1.FName as FMaterialName,t1.FModel as FMaterialModel,
--t19.FName as FSecUnitName,
u1.FBatchNo,t2.FNumber AS FStockNumber, t2.FName as FStockName ,
t3.FName as FBUUnitName,u1.FQty as FBUQty,
t4.FName as FCUUnitName ,u1.FQty/t4.FCoefficient as FCUUQty,
--u1.FQtyLock as FBUQtyLock,u1.FQtyLock/t4.FCoefficient as FCUUQtyLock,
--t5.FName as FSPName,
u1.FKFPeriod,--保质期天数
case when isdate(u1.FKFDate)=1 then Convert(datetime,u1.FKFDate) else null end as FKFDate,--采购日期
case when isdate(u1.FMyKFDate)=1 then Convert(datetime,u1.FMyKFDate) else null end as FMyKFDate,--当前保质期
Case when isdate(u1.FKFDate)=0 then null else Convert(datetime,u1.FKFDate) + u1.FKFPeriod END AS FMaturityDate,--到期日
t31.Fname Fauxstaockname
,t31.FCoefficient--换算系数
,FLOOR(u1.Fqty/t31.FCoefficient) Fauxstockqty--辅助单位数量
,(u1.Fqty-(FLOOR(u1.Fqty/t31.FCoefficient)*t31.FCoefficient)) FFstockDiff--余数
--上面这一行的作用，是根据辅助单位的名称，计算出辅助单位数量和余数
-- ,t1.FQtyDecimal, t1.FPriceDecimal,0 as FSumSort,
-- t5.FNumber as FSPNumber,t4.FNumber as FCUUnitCode,t4.FMeasureunitID as FCUUnitID
-- ,t1.FitemID ,T2.FitemID FStockID,T5.FSPID FSPID,t9.FName as FAuxPropName,t9.FNumber as FAuxPropNumber,u1.FSecQty

From #TempInventory u1
left join t_ICItem t1 on u1.FItemID = t1.FItemID
left join t_Stock t2 on u1.FStockID=t2.FItemID
left join t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID
left join t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID
left join t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID
left join t_AuxItem t9 on u1.FAuxPropID=t9.FItemID
left join t_Measureunit t19 on t1.FSecUnitID=t19.FMeasureunitID

left join (select * from t_measureunit where fstandard<>1) t31 on t31.Funitgroupid=t1.funitgroupid

left join (SELECT FTypeID,FInterID FItemID, FID FNumber,FName,FSpec
FROM t_SubMessage Where FInterID>0 AND FDeleted=0 And FTypeID=30 ) t32 on t32.FName=t31.Fname


where u1.FQty<>0 and t1.FDeleted=0
and {fnumberfrom} and {fnumberEnd}
and {fstocknumberfrom} and {fstocknumberEnd}-- AND t2.FItemID=178
--and t1.fnumber>='10.10.1.10' and t1.fnumber<='10.10.1.10'
--and t2.fnumber>='1' and t2.fnumber<='1'
Order By t1.FNumber,u1.FBatchNo
Drop Table #TempInventory
这是第一个表，，已经可以实现 过滤功能 如红色字体部分

[zym1325148]

[Quote=引用楼主 zym1325148 的回复:]
-----------------lz_xiaohe------------20080427

Set Nocount on
Create Table #TempInventory(
[FBrNo] [varchar] (10) NOT NULL ,
[FItemID] [int] NOT NULL ,
[FBatchNo] [varchar] (200) NOT NUL……
[/Quote]

Set NoCount On
Create Table #T115B
(
FItemID int default (0),
FSupplyID int default (0),
FInQty Decimal(28,10) Null default(0),
FCUInQty Decimal(28,10) Null default(0),
FInBal Decimal(28,10) Null default(0),
FAmountMust Decimal(28,10) Null default(0),
FCommitQty Decimal(28,10) Null default(0),
FCUCommitQty Decimal(28,10) Null default(0),
FCommitBal Decimal(28,10) Null default(0))
Insert Into #T115B
(FItemID,FSupplyID,FInQty,FInBal,FAmountMust)
Select v2.FItemID,v1.FSupplyID,
Sum(v2.FQty),Sum((Case When v1.FTranType=75 Then v2.FStdAmount ELSE (v2.FStdAmount-v2.FStdTaxAmount) End)),
Sum (v2.FAmountMust)
From ICPurchase v1,ICPurchaseEntry v2,t_ICItem t4,t_Supplier t8

Where v2.FItemID = t4.FItemID
And v1.FInterID=v2.FInterID
And v1.FSupplyID=t8.FItemID
And v1.FTranType in (75,76)
And v1.FDate>='2009-04-28'
And v1.FDate<='2009-04-28'
And v1.FCancelLation=0


Group By v2.FItemID,v1.FSupplyID
Insert Into #T115B
(FItemID,FSupplyID,FCommitQty,FCommitBal)
Select v2.FItemID,v1.FSupplyID,
Sum (v2.FQty), Sum(v2.FAmount)
From ICStockBill v1,ICStockBillEntry v2, t_ICItem t4,t_Supplier t8,t_Stock t99

Where v2.FItemID = t4.FItemID
And v1.FInterID=v2.FInterID AND v2.FDCStockID=t99.FItemID And t99.FTypeID<>502
And v1.FSupplyID=t8.FItemID
And v1.FDate>='2009-04-28'
And v1.FDate<='2009-04-28'
And v1.FCancelLation=0

And v1.FTranType In (1,5)
Group By v2.FItemID,v1.FSupplyID
Update t1 Set t1.FCUInQty=t1.FInQty/t7.FCoefficient,t1.FCUCommitQty=t1.FCommitQty/t7.FCoefficient
From #T115B t1,t_ICItem t4,t_MeasureUnit t7
Where t1.FItemID=t4.FItemID And
t4.FOrderUnitID = t7.FMeasureUnitID

CREATE TABLE #ItemLevel(
FNumber1 Varchar(355),
FName1 Varchar(355),
FNumber2 Varchar(355),
FName2 Varchar(355),
FItemID int,
FNumber VARCHAR(355))

Insert Into #ItemLevel SELECT
Case When CharIndex('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CharIndex('.',FFullNumber)-1) END,
'',
Case When CharIndex('.',FFullNumber,CharIndex('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CharIndex('.',FFullNumber,CharIndex('.',FFullNumber)+1)-1) END,
'',
FItemID,FNumber From t_Item
Where FItemClassID=4
AND FDetail=1
Update t0 SET t0.FName1=t1.FName,t0.FName2=t2.FName
From #ItemLevel t0 left join t_Item t1 On t0.FNumber1=t1.FNumber AND t1.FItemClassID=4 AND t1.FDetail=0
left join t_Item t2 On t0.FNumber2=t2.FNumber AND t2.FItemClassID=4 AND t2.FDetail=0

CREATE TABLE #DATA(
FCustName varchar(355) Null,
FName1 varchar(355) Null,
FName2 varchar(355) Null,
FNumber varchar(355) null,
FShortNumber varchar(355) null,
FName varchar(355) null,
FModel varchar(355) null,
FUnitName varchar(355) null,
FCUUnitName varchar(355) null,
FQtyDecimal smallint null,
FPriceDecimal smallint null,
FInQty decimal(28,10) null default (0),
FCUInQty decimal(28,10) null default (0),
FInBal decimal(28,10) null default (0),
FAmountMust decimal(28,10) null default (0),
FCommitQty decimal(28,10) null default (0),
FCUCommitQty decimal(28,10) null default (0),
FCommitBal decimal(28,10) null default (0),
FRestQty decimal(28,10) null default (0),
FCURestQty decimal(28,10) null default (0),
FRestBal decimal(28,10) null default (0),
FSumSort smallint not null Default(0),
FSortOrder smallint not null Default(0),
FID int IDENTITY)
INSERT INTO #DATA
SELECT t2.FName,tt1.FName1,tt1.FName2,t4.FNumber,'','','','','',6,4, Sum (v1.FInQty),Sum (v1.FCUInQty),Sum (v1.FInBal),Sum(
FAmountMust),Sum(FCommitQty),Sum(FCUCommitQty),sum(FCommitBal),0,0,0,CASE WHEN Grouping(t2.FName)=1 THEN 101 WHEN Grouping(tt1.FName1)=1 THEN 106
WHEN Grouping(tt1.FName2)=1 THEN 107
WHEN Grouping(t4.FNumber)=1 THEN 108 ELSE 0 END,0
FROM #T115B v1
Join t_ICItem t4 On v1.FItemID=t4.FItemID
Left Join t_Supplier t2 On v1.FSupplyID=t2.FItemID
,#ItemLevel tt1
WHERE 1=1
AND v1.FItemID=tt1.FItemID

Group By t2.FName,tt1.FName1,tt1.FName2,t4.FNumber WITH ROLLUP
Update #DATA SET FRestBal=FInBal-FCommitBal

Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel,
t1.FUnitName=t3.FName,t1.FCUUnitName=t4.FName,
t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal
From #DATA t1,t_ICItem t2,t_MeasureUnit t3,t_MeasureUnit t4
Where t1.FNumber = t2.FNumber
And t2.FUnitID=t3.FMeasureUnitID
And t2.FOrderUnitID=t4.FMeasureUnitID
Update #DATA Set FRestQty=FInQty-FCommitQty ,FCURestQty=FCUInQty-FCUCommitQty ,FRestBal=FInBal-FCommitBal
Drop Table #T115B
Update t1 Set FName1=ISNULL(FName1,'')+'['+
ISNULL((Select FNumber1 from #ItemLevel where FName1=t1.FName1 and FNumber=(select top 1 FNumber from #data where FID<t1.FID and len(FNumber)>0 order by FID desc)),'')
+'](小计)' from #data t1 WHERE FSumSort=107
Update #data Set FName1=Replace(FName1,'[]','') WHERE FSumSort=107
Update t1 Set FName2=ISNULL(FName2,'')+'['+
ISNULL((Select FNumber2 from #ItemLevel where FName2=t1.FName2 and FNumber=(select top 1 FNumber from #data where FID<t1.FID and len(FNumber)>0 order by FID desc)),'')
+'](小计)' from #data t1 WHERE FSumSort=108
Update #data Set FName2=Replace(FName2,'[]','') WHERE FSumSort=108
Update #Data Set FCustName=FCustName+'(小计)' WHERE FSumSort=106
Update #Data Set FCustName='合计' WHERE FSumSort=101
Update #Data Set FSortOrder=1 WHERE FSumSort=101
SELECT * FROM #DATA ORDER BY FSortOrder
DROP TABLE #ItemLevel
Drop Table #DATA
这是需要实现的表 想实现如上面的例子中红色部分，， 比如可以实现 通过 供应商、商品代码、日期进行过滤的功能 要怎么做呢？ 谢谢 各位帮帮忙。。

[--小F--]

什么意思？这么长。。

[-晴天]

木有表结构,神仙难下手.

[爱你99]

基础问题，把过滤条件用{}里面内容替代就可以了，例如v1.FDate>='?' 用{fdatefrom}替代, 注意的是基础表别名在整个语句里面最好统一

[dawugui]

建议你提供详细的资料：
例如表的结构，表之间的关系，测试数据，相关算法及需要的结果。
这样有助于我们理解你的意思，更主要的是能尽快让你获得答案或解决问题的方法。

[华芸智森]

一个表结构的描述表.
表名,字段名,类型,是否主键,是否索引,字段描述编号(相同意思的字段名,编号相同,如 A表有 IntGoo sID,B 表有一个IntGoodsKey, 他们的意思相同.所以使用同一个编号).

如果想清析点,多加一个 字段描述
编号,描述.

生成SQL语句,一般由前台处理.