楼主
wise |
Q:在SQL中如何利用多列数据生成唯一ID进行多表关联？
A：在本题中，首先分别对其中两个表进行左边汇总，然后再对其他四个表进行右边汇总。因为多个表之间没有唯一ID，只能通过多列字段连接生成唯一ID。
该操作例子比较复杂，但可以很大程度理清减少笛卡尔值出现。主要SQL语句如下：
- select A.ID,A.期初帳面,A.期初庫存,A.帳面數量,A.入庫數量,A.出庫數量,A.庫存差異,A.短少數,A.在途數,A.預期結存,B.期初帳面數,B.期初庫存數,B.入庫帳面數,B.入庫庫存數,B.出庫帳面數,B.出庫庫存數,B.補料帳面數,B.補料庫存數 from
- (select 廠別 & "-" & 倉別 &"-" & 品名 & "-" & 物料編號 & "-" & 單位 as ID,sum(期初帳面) as 期初帳面 ,sum(期初庫存) as 期初庫存,sum(帳面數量) as 帳面數量,sum(入庫數量) as 入庫數量,sum(出庫數量) as 出庫數量 ,sum(庫存差異) as 庫存差異 ,sum(短少數) as 短少數,sum(在途數) as 在途數,sum(預期結存) as 預期結存 from
- ( select 廠別,倉別,品名,物料編號,單位,期初帳面,期初庫存,帳面數量,入庫數量,出庫數量,庫存差異,短少數,在途數,預期結存 from [A物控表$A2:O10000]
- union all
- select 廠別,倉別,品名,物料編號,單位,期初帳面,期初庫存,帳面數量,入庫數量,出庫數量,庫存差異,短少數,在途數,預期結存 from [C物控表$A2:O10000] )
- group by 廠別 & "-" & 倉別 &"-" & 品名 & "-" & 物料編號 & "-" & 單位) A
- left join
- (select 廠別 & "-" & 倉別 &"-" & 品名 & "-" & 物料編號 & "-" & 單位 as ID, sum(期初帳面數) as 期初帳面數,sum(期初庫存數) as 期初庫存數,sum(入庫帳面數) as 入庫帳面數,sum(入庫庫存數)as 入庫庫存數,sum(出庫帳面數) as 出庫帳面數,sum(出庫庫存數) as 出庫庫存數,sum(補料帳面數) as 補料帳面數,
- sum(補料庫存數) as 補料庫存數
- from
- ( select 廠別,倉別,品名,物料編號,單位,帳面數 as 期初帳面數 ,庫存數 as 期初庫存數,0 as 入庫帳面數 ,0 as 入庫庫存數,0 as 出庫帳面數,0 as 出庫庫存數,0 as 補料帳面數,0 as 補料庫存數 from [期初明細$]
- union all
- select 廠別,倉別,品名,物料編號,單位,0 as 期初帳面數,0 as 期初庫存數,帳面數 as 入庫帳面數, 庫存數 as 入庫庫存數 ,0 as 出庫帳面數,0 as 出庫庫存數,0 as 補料帳面數,0 as 補料庫存數 from [入庫明細$]
- union all
- select 廠別,倉別,品名,物料編號,單位,0 as 期初帳面數,0 as 期初庫存數,0 as 入庫帳面數 ,0 as 入庫庫存數,帳面數 as 出庫帳面數,庫存數 as 出庫庫存數 ,0 as 補料帳面數,0 as 補料庫存數 from [出庫明細$]
- union all
- select 廠別,倉別,品名,物料編號,單位,0 as 期初帳面數,0 as 期初庫存數,0 as 入庫帳面數 ,0 as 入庫庫存數,0 as 出庫帳面數,0 as 出庫庫存數,帳面數 as 補料帳面數,庫存數 as 補料庫存數 from [補料明細$] )
- group by 廠別 & "-" & 倉別 &"-" & 品名 & "-" & 物料編號 & "-" & 單位) B
- on A.ID=B.ID
 data.rar
|