MySQL:使用左连接查询结果错误

Question

我遇到了这里描述的问题:https://www.media-division.com/using-mysql-generate-daily-sales-reports-filled-gaps/(按日期分组SUM或COUNT结果时出现空白)。我的第一个解决方案是这样的，查询1:

SELECT
DATE_FORMAT(st.vd, '%Y-%m-%d') as d,
SUM(
CASE
WHEN st.vd IS NULL THEN 0
ELSE 1
END
) AS nrvisits
FROM
(
SELECT v.visit_date vd
FROM `temp_dates_2` t
LEFT JOIN `visits` v ON DATE(v.visit_date) = DATE(t.t_date)
ORDER BY t.t_date
) as st
GROUP BY d
ORDER BY d

(temp_dates_2包含一个唯一的日期列表)。我简化了它，查询2:

SELECT
DATE_FORMAT(td.t_date, '%Y-%m-%d') as d,
(SELECT COUNT(1)
FROM visits as v
WHERE DATE(v.visit_date) = DATE(td.t_date)
) as nrvisits
FROM temp_dates_2 td
GROUP BY d
ORDER BY d

两个查询都没有语法或运行时错误，但结果不同:查询1提供的值要大得多。为了澄清，我对一个日期使用了一个非常简单的查询:

SELECT count(1) FROM visits WHERE DATE_FORMAT(visit_date, '%Y-%m-%d') = '2021-01-21'

提供了与查询2相同的结果。我的问题是:为什么查询1提供了错误的结果?我怀疑具有内部连接的子查询的结果会为同一访问返回多个记录。更新:sql转储表:https://webentwicklung.ulrichbangert.de/temp_dates_2.sql https://webentwicklung.ulrichbangert.de/visits.sql

Answer 1

进一步填写我的评论。我的建议是:

SELECT 
t.t_date, 
COUNT(v.primarykeycolumn)      
FROM 
temp_dates_2 t       
LEFT JOIN visits v ON DATE(v.visit_date) = t.t_date       
GROUP BY t.t_date

基于temp_dates_2只包含日期的假设(因此不需要对它们调用DATE())，也许v.v it_date也包含时间，而DATE()正在剥离

我还建议q1有一个错误，子查询选择访问日期，这可能导致日期合并/丢失。下面是一个例子:

Visits
2020-12-24 12:34:56 --(visit on christmas eve - shop still open
2020-12-24 23:45:00 --(visit on christmas eve - shop still open
--(no visit on christmas day - shop closed)
--(no visit on boxing day - shop closed)
2020-12-27 12:34:56 --(visit - shop reopen)
temp_dates_2
2020-12-24 
2020-12-25 
2020-12-26 
2020-12-27 

Q1中左连接子查询的结果(删除不相关的顺序):

SELECT v.visit_date vd
FROM `temp_dates_2` t
LEFT JOIN `visits` v ON DATE(v.visit_date) = DATE(t.t_date)
2020-12-24 --(visit on christmas eve - shop still open
2020-12-24 --(visit on christmas eve - shop still open
NULL       --(no visit on christmas day - shop closed)
NULL       --(no visit on boxing day - shop closed)
2020-12-27 --(visit - shop reopen)

然后q1分组、求和、格式等，得到:

Date        Count
2020-12-24  2
NULL        0
2020-12-27  1

实际上，它与基本的SELECT date(visit_date), count(*) FROM visits GROUP BY date(visit_date)没有太大的不同，除了它有一个无用的NULL，表示圣诞节和节礼日的合并，以及0计数。你真正想要的是:

2020-12-24  2 --(visits on christmas eve - shop still open
2020-12-25  0 --(no visit on christmas day - shop closed)
2020-12-26  0 --(no visit on boxing day - shop closed)
2020-12-27  1 

由我的第一个查询给定;它利用了COUNT()不计算空值的事实，通过计算参与LEFT JOIN的列或主键列，我们可以确保NULL只发生在这类列中，因为"连接失败";在右表中找不到匹配的行"这并不是因为数据在

行中自然包含一个null。例如:

temp_dates left join visits:
tempdate    visitdate   visitpk  vistorcomment
2020-12-24  2021-12-24  1        null
2020-12-25  null        null
2020-12-26  null        null
2020-12-27  2021-12-27  2        "nice place"

我们应该COUNT()visitpk(主键永远不能为空)或visitdate(如果连接失败，它将为空)，我们不应该计数评论，因为有时它自然是空的(访问者没有留下评论)。如果我们使用visitorcomment作为计数，它会扭曲数字，因为它意味着"访问发生了，访问者留下了评论">

---

所有这些都没有回答"为什么我的数字在X上比y上大";但是，如果没有一个实际的例子，这个问题是无法回答的;创造一个小提琴来复制它，我来告诉你。我目前无法从数据中看到为什么q1的数字会更大的任何原因-你断言temp_dates是唯一的，所以笛卡尔爆炸不应该发生，你似乎正在使用日期数据类型，所以混合dd/mm和m/dd不应该发生，但我可以看到你如何处理数据的错误/结果不是你想要的

这是一个答案，解释如何得到你想要的结果，为什么它工作，并有一个更简单的查询…