Oracle SQL 选择不在另一个表中的行

标签: oracle11g sql Oracle
发布时间: 2017/4/15 18:46:33
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

谁能帮我用我下面的 sql 语句。我试图做的是从那里他们不会出现在另一个表的日期范围内的 1 表中检索行。目前以下查询返回输入的日期范围之间的行。怎么可能我修改,以便从表 1 返回的表 2 中的日期范围之间没有行?表 1 将家园和表 2 将预订

SELECT homes.home_id, homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, homes.price, homes.sqft,
listagg(features.feature_name, '\n') WITHIN GROUP(ORDER BY features.feature_name) features, home_type.type_name
FROM homes
INNER JOIN bookings ON bookings.home_id = homes.home_id
INNER JOIN home_feature ON homes.home_id = home_feature.home_id
INNER JOIN home_type ON home_type.type_code = homes.type_code
INNER JOIN features ON home_feature.feature_id = features.feature_id
WHERE bookings.booking_end < to_date('23-Jan-13')
OR bookings.booking_start > to_date('22-Jan-13')
GROUP BY homes.home_id, homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, homes.price, homes.sqft, home_type.type_name

解决方法 1:

你会想要使用 LEFT JOIN 来返回所有行从 homes ,不会出现在 bookings 。我也会建议你把 bookings.date 从筛选 WHERE 条款 JOIN 条件︰

SELECT homes.home_id, 
    homes.title, 
    homes.description, 
    homes.living_room_count, 
    homes.bedroom_count, 
    homes.bathroom_count, 
    homes.price, homes.sqft,
   listagg(features.feature_name, '\n') WITHIN GROUP(ORDER BY features.feature_name) features, 
    home_type.type_name
FROM homes
LEFT JOIN bookings 
   ON bookings.home_id = homes.home_id
   AND (bookings.booking_end < to_date('23-Jan-13')
    OR bookings.booking_start > to_date('22-Jan-13'))
LEFT JOIN home_feature 
  ON homes.home_id = home_feature.home_id
LEFT JOIN home_type 
  ON home_type.type_code = homes.type_code
LEFT JOIN features 
  ON home_feature.feature_id = features.feature_id
GROUP BY homes.home_id, homes.title, 
    homes.description, homes.living_room_count, 
    homes.bedroom_count, homes.bathroom_count, 
    homes.price, homes.sqft, home_type.type_name

基于您的评论,你可以试试 NOT EXISTS :

SELECT homes.home_id, 
    homes.title, 
    homes.description, 
    homes.living_room_count, 
    homes.bedroom_count, 
    homes.bathroom_count, 
    homes.price, homes.sqft,
   listagg(features.feature_name, '\n') WITHIN GROUP(ORDER BY features.feature_name) features, 
    home_type.type_name
FROM homes
INNER JOIN home_feature 
  ON homes.home_id = home_feature.home_id
INNER JOIN home_type 
  ON home_type.type_code = homes.type_code
INNER JOIN features 
  ON home_feature.feature_id = features.feature_id
WHERE NOT EXISTS (SELECT home_id
                  FROM bookings b
                  WHERE b.home_id = homes.home_id
                    AND (b.booking_end < to_date('23-Jan-13')
                      OR b.booking_start > to_date('22-Jan-13'))
GROUP BY homes.home_id, homes.title, 
    homes.description, homes.living_room_count, 
    homes.bedroom_count, homes.bathroom_count, 
    homes.price, homes.sqft, home_type.type_name
官方微信
官方QQ群
31647020