时间戳

首页 > 解决方案 > 如何在不使用变量的情况下选择在不同日期一列值相同的行？

sql - 如何在不使用变量的情况下选择在不同日期一列值相同的行？

问题描述

我以 DB Fiddle 为例：https ://www.db-fiddle.com/f/vX7yFVk8j6FPTSm2QVoPNE/2

我试图弄清楚如何查找在不同日期多次预订一个房间的客人的所有预订。我不能使用静态值/变量之类的bookings.check_in_date = '2018-03-05'，也不能bookings.guest_id = 001使用子查询。

我试过了：

SELECT bookings.guest_id, bookings.room_number, bookings.check_in_date, bookings.check_out_date
FROM bookings
GROUP BY bookings.guest_id, bookings.room_number, bookings.check_in_date, bookings.check_out_date
HAVING count(distinct bookings.check_in_date) >= 2;

但是，它并没有给我我正在寻找的结果，而是我正在寻找这个结果：

guest_id, room_number, check_in_date, check_out_date
1, 203, 2018-03-01T00:00:00.000Z, 2018-03-07T00:00:00.000Z
1, 203, 2018-03-14T00:00:00.000Z, 2018-03-21T00:00:00.000Z

架构 (PostgreSQL v10.0)

CREATE TABLE guests (
  id integer,
  name text,
  address text,
  city text,
  state text,
  postal_code integer,
  phone_number numeric(10)
);

CREATE TABLE rooms (
  room_number integer,
  floor_number integer,
  view boolean,
  bed_type text
);

CREATE TABLE bookings (
  id integer,
  guest_id integer,
  room_number integer,
  order_date date,
  check_in_date date,
  check_out_date date
);

INSERT INTO guests (id, name, address, city, state, postal_code, phone_number)
VALUES
(1, 'John', '5 8th Avenue', 'New York', 'New York', 10001, 2021110190),
(2, 'Jane', '13 Wall St', 'New York', 'New York', 10001, 1112020190),
(3, 'Jim', '12 Water St', 'New York', 'New York', 10001, 3332224444),
(4, 'Fang', '12 Broadway', 'New York', 'New York', 10001, 1234567890),
(5, 'Bruce', '123 1st Ave', 'New York', 'New York', 10001, 9871112222);

INSERT INTO rooms (room_number, floor_number, view, bed_type)
VALUES
(101, 1, FALSE, 'King'),
(102, 1, FALSE, 'Queens'),
(203, 2, FALSE, 'Queens'),
(204, 2, TRUE, 'King'),
(409, 4, TRUE, 'King');

INSERT INTO bookings (id, guest_id, room_number, order_date, check_in_date, check_out_date)
VALUES
(18001, 001, 203, '2018-03-05', '2018-03-01', '2018-03-07'),
(18002, 001, 203, '2018-03-05', '2018-03-14', '2018-03-21'),
(18003, 001, 409, '2018-03-05', '2018-11-27', '2018-11-30'),
(18004, 001, 204, '2018-03-05', '2018-11-27', '2018-11-30'),

(18007, 002, 409, '2018-05-05', '2018-05-05', '2018-05-12'),
(18002, 002, 203, '2018-03-05', '2018-03-14', '2018-03-21'),
(18006, 002, 409, '2018-06-05', '2018-07-04', '2018-07-07'),

(18005, 003, 409, '2018-06-05', '2018-08-05', '2018-08-12'),

(18008, 004, 409, '2018-09-05', '2018-09-05', '2018-09-12');

标签： sqlpostgresql

解决方案

仅计算每个 guest_id 的预订数量应该可以让您的客人拥有多个预订

select a.*
from bookings a
join (
       select guest_id,room_number,count(*)
         from bookings
      group by guest_id,room_number
        having count(*)>1
      )b
  on a.guest_id=b.guest_id
And a.room_ number=b.room_number

推荐阅读