左连接与来自右表mysql的空行

一尘不染

左连接与来自右表mysql的空行

sql

我有餐厅和订单表，订单表我有restaurant_id，status而且date领域-为每一天我都保存在订单表中的一行。如果某天没有订单-
这意味着订单表中该天没有行。

我想根据这两个单独的条件，在日历上显示每个餐厅当月的数据。

1) in first case show only those restaurants that have at least one free 
day during this month(which means for this month at least one date is missing in orders table).

2) in second case show only those restaurants that are free for today 
(which means there is no row for today in orders table)

对于这两种情况，如果条件都满足，我应该获取当月的所有订单-这是棘手的部分。

通常使用左连接或内部连接的反连接无法获得理想的结果。

谢谢。

编辑

输出应该是这样的

1）http://img826.imageshack.us/img826/3114/e6zt.png

2）http://img13.imageshack.us/img13/6397/44l0.png

阅读 166

2021-05-16

共1个答案

一尘不染

这是今天所有免费餐厅的本月所有列表：

SELECT  r.`id`, r.`name`, o.`date`, o.`status`,  o.`id` order_id
FROM    restaurants r
        INNER JOIN orders o 
            ON r.id = o.restaurant_id
        LEFT JOIN
        (   SELECT  DISTINCT o2.Restaurant_ID
            FROM    orders o2
            WHERE   o2.date = DATE(CURRENT_TIMESTAMP)
        ) o2
            ON r.id = o2.restaurant_id
WHERE   o.Date >= DATE_FORMAT(CURRENT_TIMESTAMP ,'%Y-%m-01')
AND     o.Date <= DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01')
AND     o2.Restaurant_ID IS NULL;

这只是获取所有今天预订的餐厅（子查询o2），然后排除以下餐厅：

AND     o2.Restaurant_ID IS NULL;

这是本月所有至少有一天免费的所有餐厅本月的所有列表：

SELECT  r.`id`, r.`name`, o.`date`, o.`status`,  o.`id` order_id
FROM    restaurants r
        INNER JOIN orders o 
            ON r.id = o.restaurant_id
            AND o.date BETWEEN '2013-08-10' AND '2013-08-31' 
        INNER JOIN
        (   SELECT  o2.Restaurant_ID
            FROM    orders o2
            WHERE   o2.Date >= DATE_FORMAT(CURRENT_TIMESTAMP ,'%Y-%m-01')
            AND     o2.Date <= DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01')
            GROUP BY o2.Restaurant_ID
            HAVING COUNT(DISTINCT o2.Date) < DAY(DATE_ADD(DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -1 DAY))
        ) o2
            ON r.id = o2.restaurant_id
WHERE   o.Date >= DATE_FORMAT(CURRENT_TIMESTAMP ,'%Y-%m-01')
AND     o.Date <= DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01');

诀窍是获取本月的天数：

DAY(DATE_ADD(DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -1 DAY))

然后将结果限制为预订数少于此的restaurant_id：

HAVING COUNT(DISTINCT o2.Date) < DAY(DATE_ADD(DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -1 DAY))

SQL Fiddle上的两者的示例

2021-05-16