Oracle 不同的查询写入 (关系划分)

askTom 2017-04-20

172

问题描述

create table developers (name varchar2(30), skill varchar2(30));

create table projects   (name varchar2(30), skill varchar2(30));

insert into developers values ('SMITH','ORACLE');
insert into developers values ('SMITH','JAVA');
insert into developers values ('ADAMS','JAVA');
insert into developers values ('ADAMS','C++');

insert into projects values ('MISSION MARS','JAVA');
insert into projects values ('MISSION MARS','C++');
insert into projects values ('MISSION IMPOSSIBLE','JAVA');

--QUERY: list out projects and eligible developers, for example Mission Mars 
--  requires knowledge of Java and C++ both, so developers having both skills
--  like Adams is eligible for both the projects and both for MI-project.

-- I have written a query but looking for something better:

select d.name, p.name from developers d, projects p
    minus
    select tmp.d_name, tmp.name -- ineligible developers
        from
            (select * from projects, (select distinct name d_name from developers)) tmp
            left join developers d2 on (d2.skill = tmp.skill and d2.name = tmp.d_name)
        where d2.name is null;复制

专家解答

你如何定义 “更好”？快点？更容易阅读？

不管怎样，你试图的是关系分裂。这里有一种方法可以做到这一点，所以你只需要访问每个表一次，所以应该更快，更容易阅读 :)

它交叉加入表，然后按开发人员，项目和技能分组。你想要从这里的行，开发人员拥有项目的所有技能。表达式:

sum(case when p.skill = d.skill then 1 else 0 end)复制

找到项目和开发人员技能匹配的行。如果这 (按项目和开发) 的总和 = 项目需要的技能数量，你有一个赢家:

create table developers (name varchar2(30), skill varchar2(30));

create table projects   (name varchar2(30), skill varchar2(30));

insert into developers values ('SMITH','ORACLE');
insert into developers values ('SMITH','JAVA');
insert into developers values ('ADAMS','JAVA');
insert into developers values ('ADAMS','C++');

insert into projects values ('MISSION MARS','JAVA');
insert into projects values ('MISSION MARS','C++');
insert into projects values ('MISSION IMPOSSIBLE','JAVA');

select pname, dname, sum(sm), min(ct) from (
  select d.name dname, p.name pname, p.skill, 
         count(*) ct, 
         sum(case when p.skill = d.skill then 1 else 0 end) sm
  from   developers d
  cross  join projects p
  group  by d.name, p.name, p.skill
)
group  by pname, dname
having sum(sm) = min(ct);

PNAME         DNAME  SUM(SM)  MIN(CT)  
MISSION MARS  ADAMS  2        2  复制

您可以采用其他几种方法来实现关系划分。尽管可以说大多数都很难理解。

这是一篇文章，对它们进行了详尽的讨论 (它们使用SQL Server，但我想我们可以原谅它们;):

https://www.simple-talk.com/sql/learn-sql-server/high-performance-relational-division-in-sql-server/

请注意，前几个示例在您的情况下不起作用，因为您有多个项目。转到 “具有多个除数的关系划分” 部分，以获取与您的问题相匹配的解决方案。