求助一条sql 语句

一个表
CREATE TABLE room_status (
    sid          INTEGER  GENERATED BY DEFAULT AS IDENTITY(start with 160,increment by 1) ,
    rid          INTEGER,
    cid          INTEGER,
    check_in     VARCHAR(20) NOT NULL,
    check_out    VARCHAR(20) NOT NULL,
    status       VARCHAR(20) NOT NULL,
    CONSTRAINT room_status PRIMARY KEY (sid),
    CONSTRAINT fk_room FOREIGN KEY (rid) REFERENCES room_info (rid),
    CONSTRAINT fk_customer FOREIGN KEY (cid) REFERENCES customer_info (cid),
    CONSTRAINT status_constraint CHECK (status IN ('booked', 'occupied'))
);
插入的元素
Room Number Customer ID Customer Name Check In Date Check Out Date Operation 
105            101       Helen Paik     2010-05-10   2010-05-12     CheckIn 
108            101        Helen Paik    2010-05-10    2010-05-12 CheckIn 
109            101        Helen Paik     2010-05-10   2010-05-12 CheckIn 
301            103         Kay Wood        2010-05-29 2010-06-16 CheckIn 
1702           104         Rud Jin         2010-05-18 2010-05-30 CheckIn 
109            110         Helen Zong     2010-05-13   2010-05-24 CheckIn 当我要 选择 房间 要求是 2010-05-10   2010-05-12 之间 可以入住的   得到的结果 还是有最下面的那个109， 求助sql 要怎么写 才能 避免这个 错误

解决方案 »

  1.   

    貼出SQL語句
    另外room_status這個表CREATE有問題
    CONSTRAINT room_status PRIMARY KEY (sid)這一句去掉CONSTRAINT room_status
    變成
    CREATE TABLE room_status (
      sid INTEGER GENERATED BY DEFAULT AS IDENTITY(start with 160,increment by 1) ,
      rid INTEGER,
      cid INTEGER,
      check_in VARCHAR(20) NOT NULL,
      check_out VARCHAR(20) NOT NULL,
      status VARCHAR(20) NOT NULL,
      PRIMARY KEY (sid),
      CONSTRAINT fk_room FOREIGN KEY (rid) REFERENCES room_info (rid),
      CONSTRAINT fk_customer FOREIGN KEY (cid) REFERENCES customer_info (cid),
      CONSTRAINT status_constraint CHECK (status IN ('booked', 'occupied'))
    );
    最好貼出room_info,customer_info的CREATE語句,不然沒法本地模擬你的環境
      

  2.   

    CREATE TABLE customer_info (
        cid          INTEGER   GENERATED BY DEFAULT AS IDENTITY(start with 110,increment by 1) ,   
        firstname    VARCHAR(20) NOT NULL,
        lastname     VARCHAR(20),
        passport_num VARCHAR(20) NOT NULL,
        email        VARCHAR(30) NOT NULL,
        credit_num   VARCHAR(18) NOT NULL,
        phone_num    VARCHAR(10),
        address      VARCHAR(50),
        CONSTRAINT customer_info PRIMARY KEY (cid),
        CONSTRAINT cid_constraint CHECK (cid > 0)
    );
    CREATE TABLE room_info (
        rid          INTEGER,
        room_type    VARCHAR(20)   NOT NULL,
        price        INTEGER       NOT NULL,
        CONSTRAINT room_info PRIMARY KEY (rid),
        CONSTRAINT type_constraint CHECK (room_type IN 
        ('Single Room', 'Twin Bed', 'Queen', 'Executive', 'Suite')),
        CONSTRAINT price_constraint CHECK (price > 100 AND price <= 10000),
        CONSTRAINT room_num_constraint CHECK (rid >= 100 AND rid <= 1900)
    );
    CREATE TABLE room_status (
        sid          INTEGER  GENERATED BY DEFAULT AS IDENTITY(start with 160,increment by 1) ,
        rid          INTEGER,
        cid          INTEGER,
        check_in     VARCHAR(20) NOT NULL,
        check_out    VARCHAR(20) NOT NULL,
        status       VARCHAR(20) NOT NULL,
        CONSTRAINT room_status PRIMARY KEY (sid),
        CONSTRAINT fk_room FOREIGN KEY (rid) REFERENCES room_info (rid),
        CONSTRAINT fk_customer FOREIGN KEY (cid) REFERENCES customer_info (cid),
        CONSTRAINT status_constraint CHECK (status IN ('booked', 'occupied'))
    );
      

  3.   

    Room Number Customer ID Customer Name Check In Date Check Out Date Operation  
    这个字段对应你表 room_status  中的什么字段？ 当然我可以猜，但猜错了反而浪费双方时间。 (不要高估你的汉语表达能力或者我的汉语理解能力)
       建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
       参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
       
       1. 你的 create table xxx .. 语句
       2. 你的 insert into xxx ... 语句
       3. 结果是什么样，（并给以简单的算法描述）
       4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
       
       这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。   
      

  4.   

    1 我的 create table 语句CREATE TABLE customer_info (
      cid INTEGER GENERATED BY DEFAULT AS IDENTITY(start with 110,increment by 1) ,   
      firstname VARCHAR(20) NOT NULL,
      lastname VARCHAR(20),
      passport_num VARCHAR(20) NOT NULL,
      email VARCHAR(30) NOT NULL,
      credit_num VARCHAR(18) NOT NULL,
      phone_num VARCHAR(10),
      address VARCHAR(50),
      CONSTRAINT customer_info PRIMARY KEY (cid),
      CONSTRAINT cid_constraint CHECK (cid > 0)
    );
    CREATE TABLE room_info (
      rid INTEGER,
      room_type VARCHAR(20) NOT NULL,
      price INTEGER NOT NULL,
      CONSTRAINT room_info PRIMARY KEY (rid),
      CONSTRAINT type_constraint CHECK (room_type IN  
      ('Single Room', 'Twin Bed', 'Queen', 'Executive', 'Suite')),
      CONSTRAINT price_constraint CHECK (price > 100 AND price <= 10000),
      CONSTRAINT room_num_constraint CHECK (rid >= 100 AND rid <= 1900)
    );
    CREATE TABLE room_status (
      sid INTEGER GENERATED BY DEFAULT AS IDENTITY(start with 160,increment by 1) ,
      rid INTEGER,
      cid INTEGER,
      check_in VARCHAR(20) NOT NULL,
      check_out VARCHAR(20) NOT NULL,
      status VARCHAR(20) NOT NULL,
      CONSTRAINT room_status PRIMARY KEY (sid),
      CONSTRAINT fk_room FOREIGN KEY (rid) REFERENCES room_info (rid),
      CONSTRAINT fk_customer FOREIGN KEY (cid) REFERENCES customer_info (cid),
      CONSTRAINT status_constraint CHECK (status IN ('booked', 'occupied'))
    );2  我的insert 语句
    DELETE FROM room_info;
    DELETE FROM customer_info;
    DELETE FROM room_status;
    insert into customer_info values (101, 'Helen', 'Paik', 'S32123', '[email protected]', '09234581920348', '0387293476','sad st');
    insert into customer_info values (102, 'Jacky', 'Chen', 'HJ87212', '[email protected]', '9812038487183', '0342445432','happe st');
    insert into customer_info values (103, 'Kay', 'Wood', 'WD87342', '[email protected]', '18938183983912', '032345433','happe st');
    insert into customer_info values (104, 'Rud', 'Jin', 'LK87192', '[email protected]', '23233432434', '04324534535','happe st');
    insert into customer_info values (105, 'Lam', 'Jun', 'LP29302', '[email protected]', '29819328191212', '2901993223','happe st');
    insert into room_info values (101, 'Single Room', '300');
    insert into room_info values (102, 'Single Room', '300');
    insert into room_info values (103, 'Single Room', '300');
    insert into room_info values (104, 'Single Room', '300');
    insert into room_info values (105, 'Single Room', '300');
    insert into room_info values (106, 'Single Room', '300');
    insert into room_info values (107, 'Single Room', '300');
    insert into room_info values (108, 'Single Room', '300');
    insert into room_info values (109, 'Single Room', '300');
    insert into room_info values (110, 'Single Room', '300');
    insert into room_info values (501, 'Single Room', '300');
    insert into room_info values (502, 'Single Room', '300');
    insert into room_info values (503, 'Single Room', '300');
    insert into room_info values (504, 'Single Room', '300');
    insert into room_info values (505, 'Single Room', '300');
    insert into room_info values (506, 'Single Room', '300');
    insert into room_info values (507, 'Single Room', '300');
    insert into room_info values (508, 'Single Room', '300');
    insert into room_info values (509, 'Single Room', '300');
    insert into room_info values (510, 'Single Room', '300');
    insert into room_info values (201, 'Twin Bed', '500');
    insert into room_info values (202, 'Twin Bed', '500');
    insert into room_info values (203, 'Twin Bed', '500');
    insert into room_info values (204, 'Twin Bed', '500');
    insert into room_info values (205, 'Twin Bed', '500');
    insert into room_info values (206, 'Twin Bed', '500');
    insert into room_info values (207, 'Twin Bed', '500');
    insert into room_info values (208, 'Twin Bed', '500');
    insert into room_info values (209, 'Twin Bed', '500');
    insert into room_info values (210, 'Twin Bed', '500');
    insert into room_info values (301, 'Queen', '700');
    insert into room_info values (302, 'Queen', '700');
    insert into room_info values (303, 'Queen', '700');
    insert into room_info values (304, 'Queen', '700');
    insert into room_info values (305, 'Queen', '700');
    insert into room_info values (306, 'Queen', '700');
    insert into room_info values (307, 'Queen', '700');
    insert into room_info values (308, 'Queen', '700');
    insert into room_info values (309, 'Queen', '700');
    insert into room_info values (310, 'Queen', '700');
    insert into room_info values (401, 'Executive', '1400');
    insert into room_info values (402, 'Executive', '1400');
    insert into room_info values (403, 'Executive', '1400');
    insert into room_info values (404, 'Executive', '1400');
    insert into room_info values (405, 'Executive', '1400');
    insert into room_info values (406, 'Executive', '1400');
    insert into room_info values (1801, 'Suite', '3200');
    insert into room_info values (1802, 'Suite', '3200');
    insert into room_info values (1701, 'Suite', '3200');
    insert into room_info values (1702, 'Suite', '3200');insert into room_status values (151, 105, 101, '2010-05-10', '2010-05-12', 'booked');
    insert into room_status values (152, 108, 101, '2010-05-10', '2010-05-12', 'booked');
    insert into room_status values (153, 109, 101, '2010-05-10', '2010-05-12', 'booked');
    insert into room_status values (154, 210, 102, '2010-04-29', '2010-06-30', 'occupied');
    insert into room_status values (155, 301, 103, '2010-05-29', '2010-06-16', 'booked');
    insert into room_status values (156, 1702, 104, '2010-05-18', '2010-05-30', 'booked');
    insert into room_status values (157, 406, 105, '2010-04-27', '2010-05-24', 'occupied');
    2.2  插入一条insert into room_status ( rid , cid ,check_in ,check_out , status  )values (109, 110, '2010-05-13', '2010-05-24', 'booked')
    3  select 语句
    3.1
    select *
    from room_status;结果
     151  105 101 2010-05-10 2010-05-12 booked
     152  108 101 2010-05-10 2010-05-12 booked
     153  109 101 2010-05-10 2010-05-12 booked
     154  210 102 2010-04-29 2010-06-30 occupied
     155  301 103 2010-05-29 2010-06-16 booked
     156 1702 104 2010-05-18 2010-05-30 booked
     157  406 105 2010-04-27 2010-05-24 occupied
     160  109 110 2010-05-13 2010-05-24 booked
    3.2
    select DISTINCT R.rid
    from  room_info AS R, room_status AS RS
    where ((RS.rid = R.rid AND (RS.check_in > '2010-05-12' OR RS.check_out < '2010-05-10')) 
          OR R.rid NOT IN (select RSS.rid from room_status AS RSS)) AND R.room_type ='Single Room' 
    group by R.rid;
    得到结果 101
     102
     103
     104
     106
     107
     109
     110
     501
     502
     503
     504
     505
     506
     507
     508
     509
     510问题 如何 修改 第二段 语句   使得 109 这个id 不会 选上
    本人用的是 hsql  和 mysql 类似的一个小型 数据库 
      

  5.   

    我的 要求 是  在 '2010-05-10'和 '2010-05-12'之间 available 的 房间号码 都列出来  room_status 的 是列出 已经被入住 或者booking的 房间 号码 以及其 时间段， 不在这个表中的房间均可视为 是没有 order的房间
      

  6.   

    因为 109 的房间  在 10 和 12 之间 也是有被订的 153 109 101 2010-05-10 2010-05-12 booked所以我的语句 一开始 是 无视了 这条 
    但是 在下一个数据中160 109 110 2010-05-13 2010-05-24 booked这个又是符合了我的条件所以 109 又被选上了，
    请教 各位 我要怎么避免这个 错误了？
    求助
      

  7.   

    select * from room_info where rid not in (select rid from room_status WHERE check_in >= '2010-05-10' and check_out <= '2010-05-12') and room_type = 'Single Room';
      

  8.   

    我是用mysql 的,可能還要根據你用的數據庫做適當的修改