mysql - 如何将关系代数除法转换为sql除法查询？

Question

表格:

Flights(Flno, Origin, Destination, Distance, departs, Arrives) <br>
Flight Instance(Flno, Day, Aid)<br>
Aircraft(Aid, Make, Model, CrusingRange)<br>
Certied(Eid, Make, Model)<br>
Employee(Eid, Ename, Salary)<br>
Fight Attendant(Flno, Day, Eid, Role)<br> <br>

我知道如何用关系代数来写它:

∏Eid，目的地(σeid，目的地(Flight_Attendant flno=flno * 航类))/∏目的地(σDestination(航类))

如何在 sql 中转换它？所以结果将是去过所有城市的员工列表。

Answer 1

也许是这样的:

SET @TotalDestinations = (SELECT COUNT(AUX.Destination) FROM
                   (select distinct Destination from Flights) AS AUX);

SELECT A.eid, Employee.Ename -- select only those who traveled to all destinations
FROM
(
    -- know all the destinations for all assistant
    SELECT DISTINCT Flight_attendant.eid, Flights.Destination
    FROM Flight_attendant 
    INNER JOIN Flights ON Flight_attendant.flno=Flights.flno
) AS A
INNER JOIN Employee ON Employee.Eid = A.Eid
GROUP BY A.eid, Employee.Ename
HAVING COUNT(*) = @TotalDestinations