暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle 将表与排除标志进行比较

Oracle 将表与排除标志进行比较

askTom 2017-04-11
281

问题描述

嗨，汤姆，

有一张表，里面有所有的图书馆和书籍。
还有其他带有书壳的表。这本书可以包括在shell中或明确排除在shell中。

我必须用库和shell之间的连接填充lib_shell表

create table lib_book (
  lib_name VARCHAR2(7 CHAR) not null,
  book_name VARCHAR2(7 CHAR) not null  
);

create table shell_book (
  shell_id number not null,
  book_name VARCHAR2(7 CHAR) not null,
  is_excluded number not null -- 0 - included, 1 - excluded
);

insert into lib_book values ('lib1', 'book1');
insert into lib_book values ('lib1', 'book2');
insert into lib_book values ('lib2', 'book1');
insert into lib_book values ('lib2', 'book2');
insert into lib_book values ('lib2', 'book3');
--- ca 300 000 000 rows (6 000 000 libraries with 1-100 books on each)

insert into shell_book values (1, 'book2', 0);
insert into shell_book values (2, 'book1', 0);
insert into shell_book values (2, 'book2', 0);
insert into shell_book values (2, 'book4', 1);
insert into shell_book values (3, 'book1', 0);
insert into shell_book values (3, 'book3', 1);
-- ca 60 000 rows (1000 shells with 1-60 books on each)

create table lib_shell (
  lib_name VARCHAR2(7 CHAR) not null,
  shell_id number not null
)

-- pupulate lib_shell here

-- results in this case:
--'lib1',1
--'lib1',2
--'lib1',3
--'lib2',1
--'lib2',2


非常感谢

专家解答

对我来说，听起来就像您只需要加入，过滤掉is_externed = 1的行。

然后，您可以将查询插入到表中:

create table lib_book (
  lib_name VARCHAR2(7 CHAR) not null,
  book_name VARCHAR2(7 CHAR) not null  
);

create table shell_book (
  shell_id number not null,
  book_name VARCHAR2(7 CHAR) not null,
  is_excluded number not null -- 0 - included, 1 - excluded
);

insert into lib_book values ('lib1', 'book1');
insert into lib_book values ('lib1', 'book2');
insert into lib_book values ('lib2', 'book1');
insert into lib_book values ('lib2', 'book2');
insert into lib_book values ('lib2', 'book3');

insert into shell_book values (1, 'book2', 0);
insert into shell_book values (2, 'book1', 0);
insert into shell_book values (2, 'book2', 0);
insert into shell_book values (2, 'book4', 1);
insert into shell_book values (3, 'book1', 0);
insert into shell_book values (3, 'book3', 1);

create table lib_shell (
  lib_name VARCHAR2(7 CHAR) not null,
  shell_id number not null
);

insert into lib_shell
select lib_name, shell_id 
from   lib_book l
join   shell_book s
on     s.book_name = l.book_name
where  is_excluded = 0;

select * from lib_shell;

LIB_NAME  SHELL_ID  
lib1      3         
lib1      2         
lib1      2         
lib1      1         
lib2      3         
lib2      2         
lib2      2         
lib2      1


如果要排除重复项，请在选择后使用distinct:

select distinct lib_name, shell_id

oracleasktom
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论