时间戳

首页 > 解决方案 > MySql如何根据查询类型创建一行并添加子表？

mysql - MySql如何根据查询类型创建一行并添加子表？

问题描述

所以我有三张桌子：

客人可以进行预订，在预订表中，您可以根据主键“pID”查看客人进行的预订类型。我的数据库中没有“类型”行。我想显示酒店的可用房间，所以基本上是预订中不会出现的豪华房间和小房间的 pID、类型和城市。如何使用 IN 运算符执行此操作？

预订：

pID              |begindate    | enddate     |   
------------------------------------------------------
COD12            | 2014-07-15  | 2014-07-18  |
COD400           | 2014-07-20  | 2014-07-21  |
KOD12            | 2014-07-01  | 2014-07-07  |
COD600           | 2014-07-04  | 2014-07-12  |
MOD10            | 2014-08-10  | 2014-08-16  |

豪华房桌：


pID              |city         |    
---------------------------------
COD12            | Corona      | 
COD400           | Corona      |   
KHMED12          | Kansas      |
KHMED14          | Kansas      |
KOD12            | Kentucky    |
KOD30            | Kentucky    |

Small room table:

pID              |city         |    
---------------------------------
COD600           | Corona      |
MOD10            | Madrid      |
KOD20            | Kentucky    |

我想要的： L = 豪华房 S = 小房间

pID              |type         | city   
-----------------------------------------
KHMED12          | L           | Kansas
KHMED14          | L           | Kansas
KOD20            | S           | Kentucky
KOD30            | L           | Kentucky

标签： mysqlsql

解决方案

我相信这应该有效：

SELECT 
    -- pID
    res.pID,
    -- If there is a bigger count in the lux, than lux has the order, else: small has it
    IF(res.lux_count > res.small_count, "L", "S") as type,
    -- Same logic, but for getting the city from the right table by order pID
    IF(res.lux_count > res.small_count, 
        (SELECT lt.city FROM LUX_ROOM_TABLE lr WHERE lr.pID = res.pID),
        (SELECT lt.city FROM SMALL_ROOM_TABLE sr WHERE sr.pID = res.pID)) as city
FROM 
(
    SELECT  
        -- Reservation ID
        r.pID,
        -- Will give 1 if contains the order, 0 if not: for Luxe
        (SELECT count(*) from LUX_ROOM_TABLE LRT where LRT.pID=R.pID) as lux_count,
        -- Will give 1 if contains the order, 0 if not: for small
        (SELECT count(*) from SMALL_ROOM_TABLE SRT where SRT.pID=R.pID) as small_count,
    FROM RESERVATIONS R
    WHERE
        -- Where ID is in the list of your orders.
        pID IN ("","")
) res;

推荐阅读