MySQL 左连接与日期范围

Question

希望使用多个左联接运行查询。以下查询有效。

SELECT directory.location, sales.covers, labor.hours
FROM t_directory directory
LEFT JOIN t_sales sales
ON sales.id = directory.id
AND sales.business_date = '2011-11-14'
LEFT JOIN t_labor labor
ON labor.id = directory.id
AND labor.business_date = '2011-11-14'
ORDER BY directory.id ASC

如果我尝试在日期范围之间查询，就会出现麻烦。添加 Group By 子句会重复行。现在我实际上正在使用两个单独的查询，这可能是更好的实践，也是更好的解决方案。目前这只是出于纯粹的好奇心。

这是我到目前为止所拥有的。

 SELECT directory.location, sales.covers, labor.hours
 FROM t_directory directory
 LEFT JOIN t_sales sales
 ON sales.id = directory.id
 AND sales.business_date BETWEEN '2011-11-13' AND '2011-11-14'
 LEFT JOIN t_labor labor
 ON labor.id = directory.id
 AND labor.business_date BETWEEN '2011-11-13' AND '2011-11-14'
 GROUP BY directory.id, sales.business_date, labor.business_date
 ORDER BY directory.id ASC

显然我的 GROUP BY 子句出了问题，也许还有其他错误。

我正在寻找这样的结果：

| location | covers | labor |
=============================
  loc1     |  300   | 99.40
  loc1     |  325   | 100.50
  loc2     |  250   | 89.00
  loc2     |  275   | 90.20
  loc3     |  400   | 100.00
  loc3     |  500   | 122.90

当然，我实际得到的是：

| location | covers | labor |
=============================
  loc1     |  300   | 99.40
  loc1     |  300   | 100.50  
  loc1     |  325   | 99.40
  loc1     |  325   | 100.50   
  loc2     |  250   | 89.00
  loc2     |  250   | 90.20
  loc2     |  275   | 89.00
  loc2     |  275   | 90.20
  loc3     |  400   | 100.00
  loc3     |  400   | 122.90
  loc3     |  500   | 100.00
  loc3     |  500   | 122.90

任何有助于获得预期结果的帮助将不胜感激。

编辑： 以下是我想要加入的包含示例数据的表。他们都有一个共同的 id。

t_directory
| *id | location | 
|100  |   loc1   |
|101  |   loc2   |
|102  |   loc3   |

t_sales
| business_date | id | sales    | covers |
|  2011-11-13   | 103| 4000.00  | 300    |
|  2011-11-14   | 103| 4050.00  | 325    |

t_labor
|business_date | id | hours | 
| 2011-11-13   | 103| 99.40 |
| 2011-11-14   | 103| 100.50|

Answer 1

你的预期结果对我来说真的没有意义。 sales 和 labor 行之间没有关系，那么为什么 covers 值 300 应该放在 labor 旁边值 99.40？是什么将这两个价值观联系在一起？这就是为什么您发现很难编写查询来生成结果：您试图输出两列，其中每列中的数据不相关。

我可能还没有解释得很好。我相信其他人也可以！

Answer 2

预先查询按销售/劳动力明确分组，然后将其左连接。如果一个表中有多行，是否会错误地弄乱您的返回记录计数，从而导致结果......尝试

select STRAIGHT_JOIN
      PreQualifiedIDAndDate.ID,
      PreQualifiedIDAndDate.business_date,
      directory.location, 
      PreQuerySales.TotalCovers, 
      PreQueryLabor.TotalHours
   FROM 
      ( select distinct 
              sales.ID,
              sales.Business_Date
           from t_sales sales
           where sales.business_date between  '2011-11-13' AND '2011-11-14'
        UNION
        select labor.ID,
              labor.Business_Date
           from t_labor labor
           where labor .business_date between  '2011-11-13' AND '2011-11-14' )
         as PreQualifiedIDAndDate

         JOIN t_directory directory
            on PreQualifiedIDAndDate.ID = directory.ID

         LEFT JOIN
         ( select sales.ID,
                  sales.business_Date,
                  sum(sales.covers) TotalCovers
              from t_sales sales
              where sales.business_date between  '2011-11-13' AND '2011-11-14'
              group by sales.ID, sales.Business_Date ) PreQuerySales
            on PreQualifiedIDAndDate.ID = PreQuerySales.ID
            AND PreQualifiedIDAndDate.Business_Date = PreQuerySales.Business_Date

         LEFT JOIN 
         ( select labor.ID,
                  labor.Business_Date,
                  sum( labor.hours ) TotalHours
              from t_labor labor
              where labor.business_date between  '2011-11-13' AND '2011-11-14'
              group by labor.ID, labor.Business_Date ) PreQueryLabor
            on PreQualifiedIDAndDate.ID = PreQueryLabor.ID
            AND PreQualifiedIDAndDate.Business_Date = PreQueryLabor.Business_Date

   GROUP BY 
      PreQualifiedIDAndDate.id,
      PreQualifiedIDAndDate.Business_Date
   ORDER BY 
      directory.id