MySQL 除法等效不起作用

Question

我正在尝试在 MySQL 中实现关系代数除法的等价。

create table tham_gia(
  MaNV int unsigned not null ,
  MaDA int unsigned not null ,
  So_Gio int unsigned not null default 0,
  primary key (MaNV, MaDA)
);

现在我想找出 table 上所有可用的 MaDA 中的哪一个 MaNV 。这需要除法，但不支持除法，因此我计划使用关系代数中的 5 个基本运算来使用它的等价物，如下所述: https://en.wikipedia.org/wiki/Relational_algebra#Division_.28.C3.B7.29 除法 (÷)

The division is a binary operation that is written as R ÷ S. The result consists of the restrictions of tuples in R to the attribute names unique to R, i.e., in the header of R but not in the header of S, for which it holds that all their combinations with tuples in S are present in R.

有关示例，请参阅表 Completed、DBProject 及其划分:

Completed Student   Task
Fred    Database1
Fred    Database2
Fred    Compiler1
Eugene  Database1
Eugene  Compiler1
Sarah   Database1
Sarah   Database2

DBProject Task
Database1
Database2

Completed ÷ DBProject Student
Fred
Sarah

If DBProject contains all the tasks of the Database project, then the result of the division above contains exactly the students who have completed both of the tasks in the Database project.

More formally the semantics of the division is defined as follows:

    R ÷ S = { t[a1,...,an] : t \in R \wedge \foralls \in S ( (t[a1,...,an] \cup s) \in R) }

where {a1,...,an} is the set of attribute names unique to R and t[a1,...,an] is the restriction of t to this set. It is usually required that the attribute names in the header of S are a subset of those of R because otherwise the result of the operation will always be empty.

The simulation of the division with the basic operations is as follows. We assume that a1,...,an are the attribute names unique to R and b1,...,bm are the attribute names of S. In the first step we project R on its unique attribute names and construct all combinations with tuples in S:

    T := πa1,...,an(R) × S

In the prior example, T would represent a table such that every Student (because Student is the unique key / attribute of the Completed table) is combined with every given Task. So Eugene, for instance, would have two rows, Eugene -> Database1 and Eugene -> Database2 in T.

In the next step we subtract R from T relation:

    U := T − R

Note that in U we have the possible combinations that "could have" been in R, but weren't. So if we now take the projection on the attribute names unique to R then we have the restrictions of the tuples in R for which not all combinations with tuples in S were present in R:

    V := πa1,...,an(U)

So what remains to be done is take the projection of R on its unique attribute names and subtract those in V:

    W := πa1,...,an(R) − V

这是我的代码:

select MaNV as EmpCode1
from tham_gia 
where EmpCode1 not in(
    select MaNV as EmpCode  
    from (  
        select MaNV as ECode, MaDA as PrCode
        from (
            select MaNV as E1Code
            from tham_gia) 
        cross join (
            select MaDA as Pr1Code
            from tham_gia)

        where ECode, PrCode not in(
            select MaNV as E2Code, MaDA as Pr2Code
            from tham_gia) 
        )  
    ) ; 

但是它不起作用!请帮助我，非常感谢!

Answer 1

实际上，tham_gia 是 Participate 的别名，MaNV 是 EmpCode 的别名，MaDA 是 PrjCode(项目代码)的别名。基本上我想要的是找到参与参与中所有可用项目的所有员工(抱歉，伙计们，奇怪的别名!)我刚刚从这个链接找到了答案: https://faculty.utpa.edu/lianx/old_courses/CSCI4333_2014fall/MySQL-set-operators.pdf基本上它使用了与我相同的原理，但不太明显(它有一个带有“x，y”列的表“a”，一个带有“x”列的表“b”，它希望将 a 除以 b):

SELECT DISTINCT c1.y AS y 
FROM c c1 
WHERE NOT EXISTS 
 (SELECT d.x FROM d  
  WHERE d.x NOT IN (SELECT c2.x FROM c c2 WHERE c2.y = c1.y));

在此基础上，我做了一些修改:

select Par1.EmpCode
from Participate as Par1
where not exists (
    select Par2.PrjCode
    from Participate as Par2
    where Par2.PrjCode not in (
        select Par3.PrjCode
        from Participate as Par3
        where Par3.EmpCode = Par1.EmpCode));

它成功了!不管怎样，谢谢:)