左连接与来自右表mysql的空行 [英] left join with empty rows from right table mysql

问题描述

我有餐厅和订单表，在订单表中有restaurant_id，status和date字段-每天我在订单表中保存一行.如果某天没有订单-这意味着订单表中当天没有行.

I have restaurants and orders tables, in orders table I have restaurant_id, status and date fields - for each day I save one row in orders table. If for some day there is no order - it means the is no row for that day in orders table.

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

I want to show on the calendar the data for the current month for each restaurant, according to these 2 separate conditions.

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)

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

for both cases, if the condition is satisfied, I should fetch all the orders for the current month - this is the tricky part.

通常使用左连接或内部连接的反连接无法获得预期的结果.

The usual anti-join with left, or inner join do not give the desired result.

谢谢.

修改

输出应该像这样

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

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

推荐答案

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

This is all the listings for this month for all restaurants that are free today:

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)，然后排除这些餐厅:

This simply gets all the restaurants with bookings today (subquery o2), then excludes these restaurants:

AND     o2.Restaurant_ID IS NULL;

---

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

---

This is all the listings for this month for all restaurants that have at least one free day this month:

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');

诀窍是获取该月的天数:

The trick is to get the number of days in this month:

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

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

Then limit the results to restaurant_id's that have less bookings than this:

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上的两者示例

Example of Both on SQL Fiddle

这篇关于左连接与来自右表mysql的空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！