使用Oracle SQL作为匹配引擎

我有一个位置列表,到门的距离和他们所在的区域(例如区域A =冷藏,区域B =环境)：

Location    Zone    Distance
A1          A       1
A2          A       3
A3          A       5
A4          A       7
B1          B       2
B2          B       4
B3          B       6
B4          B       8

我还有一个产品清单,它们被挑选的次数和区域(区域A =需要冷藏的产品,区域B =需要冷藏的产品,区域A / B =没有限制的产品)

Product     Zone    Pick per day
Milk        A       8
Lettuce     A/B     7
Bread       B       6
Chocolate   B       5
Tomatoes    A/B     4
Dry Pasta   B       3
Beef        A       2
Chicken     A       1

如果我只对优化选择产品的距离感兴趣,我会把最挑选的最接近门.我通过他们的ROW_NUMBER和ORDER BY Distance和Pick加入两个表来完成这个.

但是,我真的非常关心产品区域,因为我不能将面包存放在冷藏区域(它可能会变得潮湿).此外,我知道生菜可以存放在冷藏或非冷藏区域.

此外,应该有条件确保所有产品都分配一个空间(8个空格8个产品)

手动完成我会得到.

       
Location    Zone    Distance    Product     Zone    Pick per day
A1          A       1           Milk        A       8
B1          B       2           Lettuce     A/B     7
A2          A       3           Tomatoes    A/B     4
B2          B       4           Bread       B       6
A3          A       5           Beef        A       2
B3          B       6           Chocolate   B       5
A4          A       7           Chicken     A       1
B4          B       8           Dry Pasta   B       3

我看过SQL模式匹配但是没有成功.此外,我编写了一个迭代VBA函数,该函数在区域中使用“保留”空间,但这对于家用PC来说是慢的.

最后,谢谢！我一直在阅读stackoverflow中的所有问题,但这个我无法解决！

选择 *
从产品p左外连接位置l开(INSTR(p.zone,l.zone)> 0)
p.pickperday desc,l.distance asc;

湾在你的地方写一个存储过程

>创建一个哈希集
>在结果集上启动循环
>获取当前元素,如果它不是全部已经在hashset中,并且位置也不是全部已经在hashset中插入到hashset中
> return hashsetlist

如何制定这样的程序：http://docs.oracle.com/cd/B28359_01/java.111/b31225/cheight.htm#CHDCDHJD

用于检测：

CREATE TABLE "PRODUCT" (    "PRODUCT" VARCHAR2(20),"ZONE" VARCHAR2(20),"PICKPERDAY" VARCHAR2(20));
CREATE TABLE "LOCATION" (   "LOCATION" VARCHAR2(20),"DISTANCE" VARCHAR2(20));

Insert into LOCATION (LOCATION,ZONE,DISTANCE) values ('A1','A','1');
Insert into LOCATION (LOCATION,DISTANCE) values ('B1','B','2');
Insert into LOCATION (LOCATION,DISTANCE) values ('A3','5');
Insert into LOCATION (LOCATION,DISTANCE) values ('B2','4');
Insert into LOCATION (LOCATION,DISTANCE) values ('A2','3');
Insert into LOCATION (LOCATION,DISTANCE) values ('B3','6');
Insert into LOCATION (LOCATION,DISTANCE) values ('B4','8');
Insert into LOCATION (LOCATION,DISTANCE) values ('A4','2');

Insert into PRODUCT (PRODUCT,PICKPERDAY) values ('Milk','8');
Insert into PRODUCT (PRODUCT,PICKPERDAY) values ('Tomatos','A/B','4');
Insert into PRODUCT (PRODUCT,PICKPERDAY) values ('Bread','6');
Insert into PRODUCT (PRODUCT,PICKPERDAY) values ('Dry Pasta','3');
Insert into PRODUCT (PRODUCT,PICKPERDAY) values ('Lettuce','7');
Insert into PRODUCT (PRODUCT,PICKPERDAY) values ('Beef','2');
Insert into PRODUCT (PRODUCT,PICKPERDAY) values ('Chocolate','5');
Insert into PRODUCT (PRODUCT,PICKPERDAY) values ('Chicken','1');

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!