SQL查询-按分组后连接

Question

我有两个表：

1)passenger - with passenger_id,passenger_name and passenger_city
2)flight - with flight_id,flight_name and passenger_id.

问题是：

列出乘客的详细信息和航班编号，他们已经乘坐了超过一次的航班。 （此功能将显示乘客的详细信息及其航班编号，他们已经乘坐了超过一次的航班。）

我使用了这个查询：

select * from passenger_1038299 
where passengerid in(select passengerid from flight_1038299 
                     group by passengerid 
                     having count(passengerid)>1);

但是它没有给我航班ID。请告诉我如何检索航班ID。 感谢您，对于 SQL 新手的愚蠢问题我们深表歉意。

- Surbhi Jain

---

你需要将航班表与乘客表进行内连接。 - Wyatt Shipman

Answer 1

加入航班表以获取乘客的航班信息

select * from passenger_1038299 p
join flight_1038299 f on f.passenger_id = p.passenger_id
where p.passengerid in(
    select passengerid from flight_1038299 group by passengerid having count(passengerid)>1
);

我喜欢使用 exists 来检查多个内容。如果在 passenger_id 上建立索引，则可能比上面的查询运行更快。

select * from passenger_1038299 p
join flight_1038299 f on f.passenger_id = p.passenger_id
where exists (
  select 1 from flight_1038299 f2 
  where f2.passenger_id = f.passenger_id
  and f2.flight_id <> f.flight_id
)

编辑

使用 count 窗口函数的另一种方法：

select * from (
    select *, 
        count() over (partition by p.passenger_id) cnt
    from passenger_1038299 p
    join flight_1038299 f on f.passenger_id = p.passenger_id
) t where cnt > 1

- FuzzyTree

Answer 2

另一种使用分析函数的方法：

SELECT * FROM (
    SELECT p.*, f.flight_id,
           count(*) OVER (PARTITION BY f.passenger_id ) As number_of_flights
    FROM passenger p
    JOIN flight f
    ON p.passenger_id = f.passenger_id
)
WHERE number_of_flights > 1

演示：http://sqlfiddle.com/#!4/dab21/11

- krokodilko

Answer 3

尝试这种方式

航班ID应该是多个，因此应该用逗号分隔列。

select a.*,b.flightid from passenger_1038299 a
join (select passengerid,Stuff((SELECT ', ' + s.flight_id 
         FROM flight_1038299 l
         where c.passengerid = l.passengerid
         FOR XML PATH('')),1,1,'') flightid from flight_1038299 as c group by c.passengerid having count(c.passengerid)>1) b on a.passengerid=b.passengerid

- Mukesh Kalgude