SQL：高级查询

Question

我一直在尝试几个小时来解决网页中的练习（自学sql），但我无法正确执行练习。

This is the summary

这是我走到这一步，

SELECT p.name, COUNT(trip_no) 
FROM Pass_in_trip AS pit 
INNER JOIN (
    SELECT r.seat as seat, MAX(r.qty) as qty 
    FROM (
     SELECT place AS seat, COUNT(trip_no) AS qty 
     FROM Pass_in_trip 
     INNER JOIN (
      SELECT place AS seat 
      FROM Pass_in_trip 
      GROUP BY place 
      HAVING COUNT(ID_psg) > 1 
     ) AS s 
     ON s.seat = place 
     GROUP BY ID_psg, place 
    ) as r 
    GROUP BY r.seat 
    HAVING MAX(r.qty) > 1 
) as r 
ON place = r.seat 
INNER JOIN Passenger AS p 
ON p.ID_psg = pit.ID_psg 
GROUP BY p.name, p.ID_psg, place, r.qty 
HAVING COUNT(trip_no) = r.qty 

/* This query gives better results than the previous one, however, 
it doesn't pass all tests */ 

SELECT p.name, COUNT(trip_no) 
FROM Pass_in_trip AS pit 
INNER JOIN (
    SELECT r.seat as seat, MAX(r.qty) as qty 
    FROM (
     SELECT place AS seat, COUNT(trip_no) AS qty 
     FROM Pass_in_trip 
     GROUP BY ID_psg, place 
    ) as r 
    GROUP BY r.seat 
    HAVING MAX(r.qty) > 1 
) as r 
ON place = r.seat 
INNER JOIN Passenger AS p 
ON p.ID_psg = pit.ID_psg 
GROUP BY p.name, r.qty 
HAVING COUNT(trip_no) = r.qty 

一些帮助将非常感激查询。 此致敬礼。

Answer 1

尝试

SELECT p.name, COUNT(trip_no) 
FROM Pass_in_trip AS pit 
INNER JOIN (
    SELECT r.seat as seat, MAX(r.qty) as qty 
    FROM (
     SELECT place AS seat, COUNT(trip_no) AS qty 
     FROM Pass_in_trip 
     INNER JOIN (
      SELECT place AS seat 
      FROM Pass_in_trip 
      GROUP BY place 
      HAVING COUNT(ID_psg) > 1 
     ) AS s 
     ON s.seat = place 
     GROUP BY ID_psg, place 
    ) as r 
    GROUP BY r.seat 
    HAVING MAX(r.qty) > 1 
) as r 
ON place = r.seat 
INNER JOIN Passenger AS p 
ON p.ID_psg = pit.ID_psg 
GROUP BY p.name,trip_no 
HAVING COUNT(trip_no) = r.qty