利用SQL在多重合併的數據透視表中進行多次多維計算

lisa198588

各位高人，請指教如下難題！如何將下圖紅色欄位亦通過SQL語句形成計算字段添加至數據透視表中去；表中“結存”是通過   
SELECT "本月FCST",* from [FCST$]
union all
SELECT "拉貨中",* from [拉貨$]
union all
SELECT "期初数量",* from [期初$]
union all  
SELECT "入库数量",* from [入库$]  
union all  
SELECT "出库数量",* from [出库$]  
union all  
select "结存",* from (  select Date,customer,ActualCustomer,PO,Item,期初数量,Cost, AD,Subinventory as 结存数量 from [期初$]  
union all  
select Date,customer,ActualCustomer,PO,Item,入库数量,Cost, AD,Subinventory from [入库$]  
union all
select Date,customer,ActualCustomer, PO, Item,-出库数量,Cost, DN,Subinventory from [出库$])
來實現的，但是在上語句中添加如下紅色字體部分來實現剩餘FCST字段項時  
SELECT "本月FCST",* from [FCST$]
union all
SELECT "拉貨中",* from [拉貨$]
union all
SELECT "期初数量",* from [期初$]  
union all  
SELECT "入库数量",* from [入库$]
union all  
SELECT "出库数量",* from [出库$]
union all
select "剩餘FCST",* from (select Date,customer,ActualCustomer,PO,Item,FCST数量,Cost, AD,Subinventory as 剩餘FCST数量 from [FCST$]  
union all  
select Date,customer,ActualCustomer,PO,Item,-拉貨数量,Cost, AD,Subinventory from [拉貨$]  
union all  
select Date,customer,ActualCustomer, PO, Item,-入庫数量,Cost, DN,Subinventory from [入库$])
union all
select "结存",* from (  select Date,customer,ActualCustomer,PO,Item,期初数量,Cost, AD,Subinventory as 结存数量 from [期初$]  
union all  
select Date,customer,ActualCustomer,PO,Item,入库数量,Cost, AD,Subinventory from [入库$]  
union all
select Date,customer,ActualCustomer, PO, Item,-出库数量,Cost, DN,Subinventory from [出库$])後，則顯示”查詢無法運行或數據庫表無法打開“請高人幫忙解決，具體如附，感謝！

FCST数量[/td]  [td=105]

Expr1000

      
   Item  customer  ActualCustomer  Subinventory  本月FCST  期初数量  入库数量  出库数量  结存  拉貨中  剩餘FCST     
   鉛筆  A  A  Common  

6000

  

500

  

3500

  

1000

  

3000

  

1000

  

1500

     
   鉛筆 汇总  　  　  　  

6000

  

500

  

3500

  

1000

  

3000

  

1000

  

1500

     
   圓珠筆  B  B  Common  

10000

  

390

  

800

  

300

  

890

  

500

  

8700

     
   圓珠筆 汇总  　  　  　  

10000

  

390

  

800

  

300

  

890

  

500

  

8700

     
                        
   

lisa198588

第一次發帖，不是很熟悉！

重新補入附件一次，第一次未成功，請高手幫忙解決吖

amazeyeli

[code=sql]select * from (select * from ...) as a[/code]

lisa198588

Amazeyeli，感謝您的回复！

我試了一下不行，可能是我沒有領會到您的意思，可否請您幫忙提供一下完整的代碼吖？？？

lisa198588

誰幫幫我吖{:soso_e109:}

amazeyeli

昨天没下, 想当然的以为
今天下了, 看着太繁琐了
麻烦把表名及字段名的意思解释下, 我帮你重新设计个
FCST --什么意思
(
  date --日期
  customer --客户
  ActualCustomer --现在的客户?
  PO --不知是什么
  Item --项目
  FCST数量 --数量
  Cost --价格
  AD --不懂
  Subinventory --仓库名?
)

垃货是什么意思

lisa198588

Dear Amazeyeli，感謝您的再次回复！
我表中所用的字段名都是簡稱的，具體如下：
FCST =Forecast 銷售預測
Date =日期
Customer=客户名
Actual Customer = 实际客户，也就是终端客户名【貌似字段名中是不能包含空格，所以我就直接用ActualCustomer代替Actual Customer】
PO= PO No.=訂單号 【貌似字段名中是不能包含空格和“.”的，所以我就直接用PO代替PO No.】
Item = 项目
FCST数量=销售预测数
Cost=成本价
AD= AD No.=入库单号【貌似字段名中是不能包含空格和“.”的，所以我就直接用AD代替AD No.】
Subinventory =仓库别
拉货=进货
可否不改字段名吖？因为用习惯了 ：）

amazeyeli

[code=sql]SELECT A.Item,A.customer,A.ActualCustomer,A.Subinventory,
       SUM(E.FCST数量) AS FCST数量,SUM(B.期初数量) AS 期初数量,
       SUM(C.入库数量) AS 入库数量,SUM(D.出库数量) AS 出库数量,
       SUM(B.期初数量)+SUM(C.入库数量)-SUM(D.出库数量) AS 结存,
       SUM(F.拉貨数量) AS 拉貨数量,
       SUM(E.FCST数量)-SUM(C.入库数量)-SUM(F.拉貨数量) AS 剩余FSCT
FROM
((((([产品信息表$] AS A
INNER JOIN [期初$] AS B
ON A.ID = B.ID
)
INNER JOIN [入库$] AS C
ON A.ID = C.ID
)
INNER JOIN [出库$] AS D
ON A.ID = D.ID
)
INNER JOIN [FCST$] AS E
ON A.ID = E.ID
)
INNER JOIN [拉貨$] AS F
ON A.ID = F.ID
)
GROUP BY A.Item,A.customer,A.ActualCustomer,A.Subinventory[/code]

FCST & Stock Report.rar (95.17 KB, 下载次数: 59)

2012-9-19 12:24 上传

点击文件名下载附件

amazeyeli

[code=sql]SELECT A.Item,A.customer,A.ActualCustomer,A.Subinventory,
       SUM(E.FCST数量) AS FCST数量,SUM(B.期初数量) AS 期初数量,
       SUM(C.入库数量) AS 入库数量,SUM(D.出库数量) AS 出库数量,
       SUM(B.期初数量)+SUM(C.入库数量)-SUM(D.出库数量) AS 结存,
       SUM(F.拉貨数量) AS 拉貨数量,
       SUM(E.FCST数量)-SUM(C.入库数量)-SUM(F.拉貨数量) AS 剩余FSCT
FROM
((((((SELECT Item,customer,ActualCustomer,Subinventory,Cost,PO,AD
FROM [期初$]) AS A
INNER JOIN [期初$] AS B
ON A.Item = B.Item
)
INNER JOIN [入库$] AS C
ON A.Item = C.Item
)
INNER JOIN [出库$] AS D
ON A.Item = D.Item
)
INNER JOIN [FCST$] AS E
ON A.Item = E.Item
)
INNER JOIN [拉貨$] AS F
ON A.Item = F.Item
)
GROUP BY A.Item,A.customer,A.ActualCustomer,A.Subinventory[/code]

lisa198588

辛苦辛苦了，感謝您的幫忙！

但是不是我想要的效果，這個對於我來說不適用喔，我的要求就是我上傳的附件啦，因這個表裡的數據都是從其他工作表裡直接COPY，如果改成您這樣就完成適用不了啦，不過還是很感謝您！