如何从嵌套查询中检查返回的元组中的每个条目是否相同？

发布于 2025-01-23 07:48:01 字数 846 浏览 3 评论 0原文

嘿，我是SQL的新手，我似乎找不到一种解决问题的方法。我有以下实体，

passengers (`id` INT, `name` VARCHAR(45), `surname` VARCHAR(45),
`year_of_birth` INT) 

flights (`id` INT, `routes_id` INT, `date` DATE, `airplanes_id` INT) 

flights_has_passengers (`flights_id` INT, `passengers_id` INT)

airplanes (`id` INT, `number` VARCHAR(45), `manufacturer`
VARCHAR(45), `model` VARCHAR(45)).

我想制作一个脚本，找到所有乘客的名字，至少在飞行中，如果登上了多个飞机，那些飞机必须是使用同一架飞机进行的。一个所有的声明，要检查典型中的每个条目是否相同。

编辑：我认为以下解决方案，但我不知道它是否正确：

     select p1.name, p1.surname
    from passengers p1, airplanes a1, flights_has_passengers, flights
    where
        p1.id = flights_has_passengers.passengers_id and
        flights_has_passengers.flights_id = flights.id and
        flights.airplanes_id = a1.id
        group by p1.id
        having count(a1.id) = 1;

原文

Hey i'm quite new to sql and i can't seem to find a good of a way to solve a problem.I have the following entities

passengers (`id` INT, `name` VARCHAR(45), `surname` VARCHAR(45),
`year_of_birth` INT) 

flights (`id` INT, `routes_id` INT, `date` DATE, `airplanes_id` INT) 

flights_has_passengers (`flights_id` INT, `passengers_id` INT)

airplanes (`id` INT, `number` VARCHAR(45), `manufacturer`
VARCHAR(45), `model` VARCHAR(45)).

I want to make a script that finds the names of all the passengers , that have boarded in atleast on flight and if boarded more than one,those flights must have been made using the same airplane.I tried to return a tuple containing the planes, that each one of the passengers had used,using a nested query.Then i tried to use an all statement, to check if every entry in the typle is the same.I got no luck though .Any advice would be appriciated.

EDIT : I thought the following solution but i don't know if it is correct:

     select p1.name, p1.surname
    from passengers p1, airplanes a1, flights_has_passengers, flights
    where
        p1.id = flights_has_passengers.passengers_id and
        flights_has_passengers.flights_id = flights.id and
        flights.airplanes_id = a1.id
        group by p1.id
        having count(a1.id) = 1;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

做个少女永远怀春 2025-01-30 07:48:01

更新：根据OP对答案的评论，这是另一种使用不存在的方式

嘿，谢谢您的回答。是否有另一种方法可以在没有加入语句的情况下实施此操作？例如，不存在类似的东西？

select p.id
from passengers p 
join flights_has_passengers  fp 
  on p.id= fp.passengers_id
join flights f
  on f.id=fp.flights_id
 where not exists 
 (select 1 from join flights f1 
 where f1.id=f.id
 and f1.airplanes_id<>f.airplanes_id)

也许使用下面的加入

select p.id
from passengers p 
join flights_has_passengers  fp 
  on p.id= fp.passengers_id
join flights f
  on f.id=fp.flights_id
group by p.id
having count(f.airplanes_id)=1

UPDATE: Based on OP's comment on the answer, here's another way using NOT EXISTS

Hey thanks for your answer.Is there another way to implement this without a join statement ?For example something like not exists or all ?

select p.id
from passengers p 
join flights_has_passengers  fp 
  on p.id= fp.passengers_id
join flights f
  on f.id=fp.flights_id
 where not exists 
 (select 1 from join flights f1 
 where f1.id=f.id
 and f1.airplanes_id<>f.airplanes_id)

Maybe use a JOIN like below

select p.id
from passengers p 
join flights_has_passengers  fp 
  on p.id= fp.passengers_id
join flights f
  on f.id=fp.flights_id
group by p.id
having count(f.airplanes_id)=1

回复收藏 0 原文

~没有更多了~