MYSQL:像现在一样空置/占用的房间 [英] MYSQL: Vacant / occupied rooms as now

问题描述

我有三张表 room_charges、room_category 和patient_detail

I have three tables room_charges, room_category and patient_detail

room_category
id room_category
1      twin
2      classic
3      Deluxe

room_charges
id room_name room_category
1    tw1        1
2    tw2        1
3    cl1        2
4    cl2        2
5    dl1        3

patient_detail

id patient_name room_name room_category admission_date_time discharge_date_time discharged
1   Mr x             1        1          10-12-14 10:40        12-12-14 08:40      1
2   Mr y             1        1          12-12-14 11:40        15-12-14 13:10      1
3   mr z             1        1          15-12-14 14:40             null          null

我正在使用查询来查找 vacant_beds 的详细信息，但根据patient_detail 表，room_id 1 已被占用，但我得到了它的空缺.我使用的查询是这样的:

I am using the query to find the vacant_beds detail, but as per the patient_detail table room_id 1 is occupied, but I am getting it vacant. The query I am using is like this:

select `rc`.`id` AS `id`,
        `rct`.`room_category` AS `room_category`,
        group_concat(`rc`.`room_name` separator ',') AS `vacant_beds` 
from ((`room_charges` `rc` 
       left join `patient_detail` `pd` on((`rc`.`id` = `pd`.`room_name`))) 
      join `room_category` `rct` on((`rc`.`room_category` = `rct`.`id`))) 
where ((`pd`.`discharged` = 1)  or    isnull(`pd`.`admission_date_time`))  
group by `rc`.`room_category` 
having max(admission_date_time)

我正在创建一个视图来获取空床信息，不能使用子查询.非常感谢您提供合适的解决方案.

I am creating a view to get the vacant bed information and cannot use sub-query. Thanks a lot for a suitable solution on this.

好的，我将低于我想要的结果，如下所示:

Ok I am putting below my desired result as below:

room_category room_name
twin            tw2
classic         cl1,cl2
deluxe          dl1

这意味着根据患者详细信息表房间 tw1 仍然被占用，不应显示在查询结果中.

This means that as per the patient detail table room tw1 is still occupied and should not show in the query result.

推荐答案

您的问题很难理解，因为您的查询中的列与示例数据中的列不匹配.此外，您没有想要的结果.

Your question is quite hard to follow because the columns in your query do not match the columns in the sample data. Also, you have no desired results.

我认为您需要将日期比较移到 on 子句中，然后在 where 中查找不匹配:

I think you need to move the date comparisons into an on clause and then look for non-matches in the where:

select rct.room_category AS room_category,
       group_concat(rc.room_name separator ',') AS vacant_beds
from room_charges rc join
     room_category rct
     on rc.room_category = rct.id left join
     patient_detail pd
     on rc.id = pd.room_name and
        (pd. discharge_date_time is null or
         curdate() between admission_date_time and discharge_date_time)
where pd.id is null
group by rc.room_category ;

这篇关于MYSQL:像现在一样空置/占用的房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！