MySQL查询缺少行

Question

所以我需要一点帮助。我似乎无法找到正确的JOIN或任何我需要的东西，以便获得我正在寻找的所有结果。

我的当前查询如下：

SELECT 
    COUNT(hd.value) as total_rows, 
    ROUND(AVG(hd.value), 2) as OEE_AVG, 
    dl.line_id, 
    hd.shift, 
    ol.description 
FROM 
    dashboard_to_dashboard_lines ddl 
JOIN dashboard_lines dl ON dl.id = ddl.dashboard_line_id 
LEFT OUTER JOIN operation_lines ol ON (dl.line_id = ol.line_id) 
JOIN historical_data hd ON dl.gauge_tag_id = hd.tag_id 
WHERE 
    hd.timestamp BETWEEN '2016-01-04 00:00:00' AND '2016-01-04 23:59:59' 
    AND ddl.dashboard_id = 1 
    AND hd.value < 200 
GROUP BY 
    dl.line_id, 
    hd.shift 
ORDER BY 
    hd.shift, 
    dl.line_id ASC 

期望的结果将如下所示：

________________________________________________________ 
| total rows | oee_avg | line_id | shift | description | 
++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
| 858  | 80.24 | 1  | 1 | Line Desc | 
| 432  | 60.21 | 2  | 1 | Line Desc | 
| 234  | 70.75 | 3  | 1 | Line Desc | 
| 865  | 10.45 | 1  | 2 | Line Desc | 
| 864  | 20.26 | 2  | 2 | Line Desc | 
| 823  | 30.44 | 3  | 2 | Line Desc | 
| 423  | 56.14 | 1  | 3 | Line Desc | 
| 234  | 12.74 | 2  | 3 | Line Desc | 
| 534  | 43.64 | 3  | 3 | Line Desc | 

基本上，我得到用于与通过分开的仪表盘相关联的每个线路的值平均转移。

我主要得到我需要的数据，除了1行之外，所提供的时间范围为1班。 （其他时间框架提供所有班次的所有线路的预期结果）。所以我假设没有数据在那里导致行被遗漏的计算。

我试着做一个外部联接（混合并在整个查询中匹配），没有任何运气。

我知道没有SQL小提琴很难，但我希望有人可能有一些洞察力，可以帮助我。

在此先感谢。

SQL Fiddle

编辑：新增SQL小提琴链接

Answer 1

没有我无法测试样本数据，但这应该指向你在正确的方向，如果它完全不起作用。重要的部分是你需要所有可能的线/移位组合的来源。在这种情况下，我使用了一个子查询来对付你的表，而不受日期等的限制，但你可能有更好的方法来获取这些。此外，在类似情况下，您仍然希望在报表上查看某些值的数据可能根本不存在。例如，如果我报告的是白天的销售情况，并且商店在周日关闭了，但我仍然希望在当天看到零。在这种情况下，除了从现有表格中进行选择外，还需要以其他方式创建值。

SELECT 
    COUNT(hd2.value) AS total_rows, 
    ROUND(AVG(hd.value), 2) AS OEE_AVG, 
    SQ.line_id, 
    SQ.shift, 
    OL.description 
FROM 
    (
     SELECT DISTINCT dl.line_id, hd.shift 
     FROM dashboard_lines dl 
     INNER JOIN historical_data hd ON hd.tag_id = dl.gauge_tag_id 
    ) SQ 
INNER JOIN dashboard_to_dashboard_lines ddl ON ddl.dashboard_id = 1 
LEFT OUTER JOIN dashboard_lines dl2 ON 
    dl2.id = ddl.dashboard_line_id AND 
    dl2.line_id = SQ.line_id 
LEFT OUTER JOIN historical_data hd2 ON 
    hd2.tag_id = dl.gauge_tag_id AND 
    hd2.shift = SQ.shift AND 
    hd2.timestamp BETWEEN '2016-01-04 00:00:00' AND '2016-01-04 23:59:59' AND 
    hd2.value < 200 
LEFT OUTER JOIN operation_lines ol ON ol.line_id = dl2.line_id 
GROUP BY 
    SQ.line_id, 
    SQ.shift 
ORDER BY 
    SQ.shift, 
    SQ.line_id ASC 

而且，请注意，您的时间戳列的检查可能会导致错误的结果如果MySQL是保持超过第二的精度。更好地使用hd2.timestamp >= '2016-01-04 00:00:00' AND hd2.timestamp < '2016-01-05 00:00:00'