加入和删除笛卡尔产品 [英] Joins and removing Cartesian Products

问题描述

我有5个地区，每个地区包含1个商店和1个仓库。我有一个表，其中包含区域和相应的商店和仓库。我也有一个表，列出了商店和仓库的库存。最后，有一个表格列出仓库的允许数量。如果我现在查看库存表，现在它会说如下：

 设施ID ITEM_NUMBER QTY 
 STORE 1 15D 2 
 WAREHOUSE 1 15D 1 
  

问题是，商店1和仓库1属于地区1，我想以下列格式显示：

  REGION WAREHOUSE数量OH商店数量OH 
 1 1 2 1 1 
  

等等所以我写了以下SQL：

创建或替换视图REGION_BALANCES
为
，WAREHOUSES为
（选择A.REGION_CODE，A.REG_DESC，A .WAREHOUSE_NUMBER，A.FACILITY_ID，
C.ITEM_NUMBER，汇总（C.IN_STOCK_QTY）为IN_STOCK_QTY，
B.ALLOWED_QTY
从REG_WHS_STR_ASSOC加入
许可B在（A.FACILITY_ID = B.FACILITY_ID）通过A.REGION_CODE，A.REG_DESC，A.WAREHOUSE_NUMBER，C加入
INVENTORIES C（A.FACILITY_ID = FACILITY_ID）和（B.ITEM_NUMBER = C.ITEM_NUMBER）
ITEM_NUMBER，B.ALLOWED_QTY），
存为
（选择A.REGION_CODE，A.REG_DESC，A.STORE_NUMBER，A.FACILITY_ID，B.ITEM_NUMBER，
sum（B.IN_STOCK_QTY）as IN_STOCK_QTY
来自REG_WHS_STR_ASSOC通过A.REGION_CODE，A.REG_DESC，A.STORE_NUMBER，B.ITEM_NUMBER
在（A.FACILITY_ID = B.FACILITY_ID）
上加入
INVENTORIES ）
选择A.REGION_CODE，A.REG_DESC，A.WAREHOUSE_NUMBER，A.FACILITY_ID，
WAREHOUSES.ITEM_NUMBER，WAREHOUSES.IN_STOCK_QTY，WAREHOUSES.ALLOWED_QTY，
STORES.STORE_NUMBER，STORES.FACILITY_ID， STORES.ITEM_NUMBER，STORES.IN_STOCK_QTY
来自REG_WHS_STR_ASSOC加入
WAREHOUSES（A.REGION_CODE = WAREHOUSES.REGION_CODE）加入
存储在（A.REGION_CODE = STORES.REGION_CODE）
order by 5,1 asc;


我已将我的连接从左连接改为右连接，交叉连接到内连接;然而，我得到30条记录（我需要数百条记录），或者我得到笛卡尔产品。任何人都可以提供建议我做错了什么？

解决方案

这是怎么回事？您使用一些程式码来撷取这些资料。

  SELECT 
 REGION_CODE，REG_DESC，WAREHOUSE_NUMBER，WAREHOUSE_FACILITY_ID，STORE_NUMBER，STORE_FACILITY_ID ，
 FACILITY_ID，ITEM_NUMBER，IN_STOCK_QTY 
 
从REG_WHS_STR_ASSOC R 
 INNER JOIN库存I ON I.FACILITY_ID = R.WAREHOUSE_FACILITY_ID或I.FACILITY_ID = R.STORE_FACILITY_ID 
 GROUP BY REGION_CODE，REG_DESC，WAREHOUSE_NUMBER，WAREHOUSE_FACILITY_ID，STORE_NUMBER，STORE_FACILITY_ID，
 FACILITY_ID，ITEM_NUMBER，IN_STOCK_QTY 
  

我认为这是你正在寻找什么。为了挤压它更多我认为你必须使用子查询 - 这是很好，但也许不那么高效。但我认为这会得到你正在寻找的主要 - 你将不得不与它更多为您的目的

I have 5 regions that each contain 1 store and 1 warehouse. I have a table that contains the regions and corresponding stores and warehouses. I also have a table that lists the inventories of both the stores and warehouses. Finally, have a table that lists the allowed quantities for the warehouse. If I look at the inventory table right now it would say something like:

FACILITY ID        ITEM_NUMBER    QTY
  STORE 1              15D         2 
 WAREHOUSE 1           15D         1

The problem is, Store 1 and Warehouse 1 belong to region 1 and I would like to see it in this format:

REGION    WAREHOUSE    QTY OH     STORE    QTY OH 
  1           1          2          1         1

and so on... So I wrote the following SQL:

Create or replace view REGION_BALANCES
as
with WAREHOUSES as 
(select A.REGION_CODE, A.REG_DESC, A.WAREHOUSE_NUMBER, A.FACILITY_ID,
        C.ITEM_NUMBER, sum(C.IN_STOCK_QTY) as IN_STOCK_QTY,
        B.ALLOWED_QTY
 from   REG_WHS_STR_ASSOC A                                    join
        ALLOWANCES        B on (A.FACILITY_ID = B.FACILITY_ID) join
        INVENTORIES       C on (A.FACILITY_ID = FACILITY_ID) and (B.ITEM_NUMBER = C.ITEM_NUMBER)
 group  by A.REGION_CODE, A.REG_DESC, A.WAREHOUSE_NUMBER, C.ITEM_NUMBER, B.ALLOWED_QTY), 
STORES as 
(select A.REGION_CODE, A.REG_DESC, A.STORE_NUMBER, A.FACILITY_ID, B.ITEM_NUMBER,
        sum(B.IN_STOCK_QTY) as IN_STOCK_QTY
 from   REG_WHS_STR_ASSOC A                                    join
        INVENTORIES       B on (A.FACILITY_ID = B.FACILITY_ID)
 group  by A.REGION_CODE, A.REG_DESC, A.STORE_NUMBER, B.ITEM_NUMBER
)
select A.REGION_CODE, A.REG_DESC, A.WAREHOUSE_NUMBER, A.FACILITY_ID,
       WAREHOUSES.ITEM_NUMBER, WAREHOUSES.IN_STOCK_QTY, WAREHOUSES.ALLOWED_QTY,
       STORES.STORE_NUMBER, STORES.FACILITY_ID, STORES.ITEM_NUMBER, STORES.IN_STOCK_QTY
from   REG_WHS_STR_ASSOC A                                             join
       WAREHOUSES          on (A.REGION_CODE = WAREHOUSES.REGION_CODE) join
       STORES              on (A.REGION_CODE = STORES.REGION_CODE)
order by 5,1 asc;

I have changed my join around from left joins to right joins to cross joins to inner joins; however, I either get 30 records (and I need hundreds of records) or I get Cartesian products. Can anyone provide advice to what I'm doing wrong?

解决方案

Where is this going? Are you using some programming to pull this data.

SELECT
REGION_CODE, REG_DESC, WAREHOUSE_NUMBER, WAREHOUSE_FACILITY_ID, STORE_NUMBER, STORE_FACILITY_ID,
FACILITY_ID, ITEM_NUMBER, IN_STOCK_QTY

FROM REG_WHS_STR_ASSOC R 
INNER JOIN Inventories I ON I.FACILITY_ID = R.WAREHOUSE_FACILITY_ID OR I.FACILITY_ID = R.STORE_FACILITY_ID
GROUP BY REGION_CODE, REG_DESC, WAREHOUSE_NUMBER, WAREHOUSE_FACILITY_ID, STORE_NUMBER, STORE_FACILITY_ID,
FACILITY_ID, ITEM_NUMBER, IN_STOCK_QTY

I think that is kind of what you are looking for. To squish it more I think you would have to use subqueries - that is fine but maybe not so efficient. But I think this will get you what you are looking for mostly - you will have to dink with it a bit more for your purposes

这篇关于加入和删除笛卡尔产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！