sql查询中的否定动词
我的问题很简单。我如何编写一个查询，在其中我否定了一些东西？我指的不是not条件。例如，我必须选择所有预订了至少一个评分大于9的养老金(Acc_type_code)的游客，但没有预订评分低于9的三星级酒店？
我的表：
旅游者
id (pk)
name
email预订
id_tourist (pk)
id_accomodation (pk)住宿
id_accomodation (pk)
acc_type_code (fk)
nr_of_stars
rating
price住宿类型
acc_type_cde (pk)
acc_type_name我尝试过的：
SELECT t.id as id_tourist, t.name, t.email from Tourist t,
JOIN Booking b on (t.id = b.id_tourist)
JOIN Accomodation a on (b.id_accomodation = a.id_accomodation)
JOIN AccType at on (a.acc_type_code = at.acc_type_code)
WHERE a.rating > 9 AND .. ?在这里我被封堵了。有什么建议吗？
回答 3
Stack Overflow用户
发布于 2017-06-29 05:31:21
您可以使用LEFT OUTER JOIN查找要从查询中排除的记录，然后在WHERE条件中将其过滤掉
SELECT t.id as id_tourist, t.name, t.email
FROM Tourist t
INNER JOIN Booking b on (t.id = b.id_tourist)
INNER JOIN Accomodation a on (b.id_accomodation = a.id_accomodation)
INNER JOIN AccType at on (a.acc_type_code = at.acc_type_code)
LEFT OUTER JOIN Accomodate a2 on (b.id_accomodation = a2.id_accomodation) AND a2.nr_of_stars = 3 AND a2.rating < 9
WHERE a.rating > 9 AND a2.id_accomodation IS NULL注意:上面的语法假设MSSQL是您的SQL风格。
更新
根据OP在评论中的进一步信息，NOT EXISTS可能会提供一种更简单的方法。概念类似:找到要排除的记录，然后将其包装在NOT EXISTS子句中
SELECT t.id as id_tourist, t.name, t.email
FROM Tourist t
INNER JOIN Booking b on (t.id = b.id_tourist)
INNER JOIN Accomodation a on (b.id_accomodation = a.id_accomodation)
INNER JOIN AccType at on (a.acc_type_code = at.acc_type_code)
WHERE a.rating > 9
AND NOT EXISTS (
SELECT b2.id_tourist FROM Booking b2
INNER JOIN Accomodation a2 on (b2.id_accomodation = a2.id_accomodation)
WHERE a2.nr_of_stars = 3 AND a2.rating < 9
)注意:您可能需要在NOT EXISTS子句中添加更多联接，以定位应该排除的记录。我不知道你的数据。
Stack Overflow用户
发布于 2017-06-29 06:29:54
应该像使用NOT EXISTS一样简单
SELECT t.id as id_tourist, t.name, t.email from Tourist t,
JOIN Booking b on (t.id = b.id_tourist)
JOIN Accomodation a on (b.id_accomodation = a.id_accomodation)
JOIN AccType at on (a.acc_type_code = at.acc_type_code)
WHERE a.rating > 9 AND
NOT EXISTS (
your query to get the same tourist_Id and has booked a 3 start hotel and under 9 ratings
)Stack Overflow用户
发布于 2017-06-29 05:34:51
下面是一个LEFT JOIN反联接方法：
SELECT t.id as id_tourist, t.name, t.email
FROM Tourist t,
JOIN Booking b ON t.id = b.id_tourist
JOIN Accomodation a ON b.id_accomodation = a.id_accomodation
AND a.rating > 9
JOIN AccType at ON a.acc_type_code = at.acc_type_code
LEFT JOIN (SELECT t.id
FROM Tourist t
JOIN Booking b ON t.id = b.id_tourist
JOIN Accomodation a ON b.id_accomodation = a.id_accomodation
AND a.rating < 9
AND a.nr_of_stars = 3
) a2 ON a2.id = t.id
WHERE a2.id IS NULL外部查询只查找预订了9级以上酒店的游客(t.id)。内部查询只查找预订了低于9级的三星级酒店的游客(t.id)。
通过在它们之间进行LEFT JOIN，并将我们的结果限制在那些不匹配(WHERE a2.id IS NULL)的酒店，我们只剩下那些预订了评级高于9的酒店，而没有预订评级低于9的三星级酒店的酒店。
https://stackoverflow.com/questions/44812679
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号