返回条件为if的查询结果 [英] Return query result with if condition

问题描述

我有一些这样的表结构.

I have some table structure like this.

表property_day

propday_id | date       | property_id | check_in | check_out
------------------------------------------------------
1          | 2017-03-26 | 5           | 0        | 0
2          | 2017-03-27 | 5           | 0        | 0
3          | 2017-03-28 | 5           | 0        | 0
4          | 2017-03-29 | 5           | 0        | 0

表rooms

room_id | name      | property_id
----------------------------
1       | Deluxe    | 5
2       | Superior  | 5
3       | Executive | 5

我想在查询中显示if条件下的所有房间.我显示了带日期签入和日期签出的房间.

I want to show all that room with if condition inside query. I show that room with date check in and date check out.

在表property_day列中的 check_in 和 check_out 用于指示来宾无法在该日期办理入住手续，无法在该日期办理退房手续或无法办理入住手续并进行检查在那个日期.值 0 表示访客可以签入或签出，但值 1 表示访客无法签入或签出.

In table property_day column check_in and check_out is used for indicate guest cannot check in on that date or cannot check out on that date or cannot check in and check out on that date. Value 0 indicate guest can check in or check out but value 1 is indicate guest cannot check in or check out.

这是if条件:

1. 如果我在2017-03-27签入并在2017-03-28签出，并且check_in和check_out列中的值在该日期为 0 ，我想显示所有property_id 5 中的房间.
2. 如果我在2017-03-27签入并在2017-03-28签出，并且2017-03-27的check_in列中的值为 1 ，我不想显示所有房间.
3. 如果我在2017-03-27签入并在2017-03-28签出，并且2017-03-28的check_out列中的值为 1 ，我也不会要显示所有房间.
4. 如果我在2017-03-27签入并在2017-03-29签出，并且2017-03-28中列check_in和check_out的值是 1 ，我要显示所有房间.
5. 如果我在2017-03-27签入并在2017-03-29签出，并且2017-03-28中列check_in和check_out的值是 0 ，我也想展示所有房间.

1. If I check in on 2017-03-27 and check out on 2017-03-28 and value in column check_in and check_out is 0 on that date I want to show all room in property_id 5.
2. If I check in on 2017-03-27 and check out on 2017-03-28 and value in column check_in on 2017-03-27 is 1, I doesn't want to show all room.
3. If I check in on 2017-03-27 and check out on 2017-03-28 and value in column check_out on 2017-03-28 is 1 , I also doesn't want to show all room.
4. If I check in on 2017-03-27 and check out on 2017-03-29 and value in column check_in and check_out in 2017-03-28 is 1, I want to show all room.
5. If I check in on 2017-03-27 and check out on 2017-03-29 and value in column check_in and check_out in 2017-03-28 is 0, I also want to show all room.

这是条件编号1的表结构:

This is table structure for condition number 1 :

propday_id | date       | property_id | check_in | check_out
------------------------------------------------------
1          | 2017-03-26 | 5           | 0        | 0
2          | 2017-03-27 | 5           | 0        | 0
3          | 2017-03-28 | 5           | 0        | 0
4          | 2017-03-29 | 5           | 0        | 0

这是条件编号2的表结构:

This is table structure for condition number 2 :

propday_id | date       | property_id | check_in | check_out
------------------------------------------------------
1          | 2017-03-26 | 5           | 0        | 0
2          | 2017-03-27 | 5           | 1        | 0
3          | 2017-03-28 | 5           | 0        | 0
4          | 2017-03-29 | 5           | 0        | 0

这是条件编号3的表结构:

This is table structure for condition number 3 :

propday_id | date       | property_id | check_in | check_out
------------------------------------------------------
1          | 2017-03-26 | 5           | 0        | 0
2          | 2017-03-27 | 5           | 0        | 0
3          | 2017-03-28 | 5           | 0        | 1
4          | 2017-03-29 | 5           | 0        | 0

这是条件编号4的表结构:

This is table structure for condition number 4 :

propday_id | date       | property_id | check_in | check_out
------------------------------------------------------
1          | 2017-03-26 | 5           | 0        | 0
2          | 2017-03-27 | 5           | 0        | 0
3          | 2017-03-28 | 5           | 1        | 1
4          | 2017-03-29 | 5           | 0        | 0

这是条件编号5的表结构:

This is table structure for condition number 5 :

propday_id | date       | property_id | check_in | check_out
------------------------------------------------------
1          | 2017-03-26 | 5           | 0        | 0
2          | 2017-03-27 | 5           | 0        | 0
3          | 2017-03-28 | 5           | 0        | 0
4          | 2017-03-29 | 5           | 0        | 0

这是我已经尝试过的查询，但是没有给出我想要的结果.

This is the query which I already try but doesn't give result what I want.

SELECT p.*, r.*
FROM property_day p
JOIN rooms r on p.property_id = r.property_id
WHERE p.property_id = 5 AND p.check_in = 0 OR p.check_out = 0

这是简单的查询，因为我不知道是否可以使用条件查询.

That's simple query because I doesn't know to make some query with that if condition.

请任何人帮助我做到这一点.谢谢.

Please anyone help me to do that. Thank you.

推荐答案

IF() mysql语法如下:

The IF() mysql syntax goes as this:

IF(expression，true，false);

您可以创建一个类似于以下示例的语句.我还没有测试过，但是如果它能正常工作，那就好了.如果您想出理想的结果的最佳方法，则可以重新修改它.我没有为值1和0包括check_in和check_out的条件，因为它们很混乱并且结果不知道(以理解您的if条件为我的观点)，但是如果您愿意，可以添加它们坚持:

You can create a statement like the example below. I haven't tested it but if it works, then good. You can re-rwite it though if you figured out a best way for your desired result. I didn't include the condition of check_in and check_out for value 1 and 0 because they are confusing and results to I don't know (in my pov of understanding your if conditions), but you can add them if you insist:

SELECT p.property_id, 
    IF(p.date = '2017-03-27' OR p.date = '2017-03-28',
        IF(p.property_id = 5, 
            r.name,
            '---'
        ), 
        IF(p.date = '2017-03-29', 
            r.name,
            '---'
        )
    ) AS your_result
FROM property_day p
JOIN rooms r on p.property_id = r.property_id
WHERE p.date >= '2017-03-27' AND p.date <= '2017-03-29'

这篇关于返回条件为if的查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！