mysql - 数据库SQL查询、比较值

Question

CUSTOMER(cid, cname, ccity)
TICKET(tid, cid, pricepaid, milespaid, date)          - cid references CUSTOMER.cid
FLIGHT(fid, deptapc, arrapc, dept, arr, milesgiven) 
deptapc and arrapc reference AIRPORT.apc
FLIGHTTICKET(tid, fid, date) - tid references TICKET.tid and fid references FIGHT.fid
AIRPORT(apc, aname, acity)

输出去过数据库中每个机场的任何客户的 cid。 (转机时经过一个机场就足够了，所以不一定是最终目的地)。

我写这个是为了显示每个客户去过的机场数量:

Select cid,count(cid)
From(SELECT cid, deptapc
FROM CUSTOMER 
Natural join TICKET Natural join FLIGHTTICKET Natural join FLIGHT
UNION
SELECT cid, arrapc
FROM CUSTOMER 
Natural join TICKET Natural join FLIGHTTICKET Natural join FLIGHT)
Group by cid;

但我不知道如何将每个值与机场总数进行比较，看看它们是否相同

Select count(apc) 
From AIRPORT

Answer 1

很简单，您只需使用子查询，就像您在 FROM 中所做的那样:

SELECT cid, 
       Count(cid), 
       ( Count(cid) = (SELECT Count(apc) 
                       FROM   airport) ) AS result 
FROM  (SELECT cid, 
              deptapc 
       FROM   customer 
              natural JOIN ticket 
              natural JOIN flightticket 
             natural JOIN flight 
       UNION 
       SELECT cid, 
              arrapc 
       FROM   customer 
              natural JOIN ticket 
              natural JOIN flightticket 
              natural JOIN flight) 
GROUP  BY cid;