不同表中的 SQL Server 计数(如果不是所有行都存在)

Question

我试图从 2 个表中获取结果。第一个表是带有信息的 ID，我想从第二个表中根据某些条件计算第一个表中每个 id 的现有值。这是我的代码：

SELECT p.stId, count(att.adate)
FROM payments p
LEFT JOIN Attendence att ON att.stId = p.stId
where p.cyear = 2018 and p.cmonth = 3 and p.cId = 10
and att.adate between '2018/03/01' and '2018/03/30 23:59:59.999' and att.cid 
= 10
GROUP BY p.stId

从第一个表(p(我有3个id。 但是当我运行它时，它只返回 1 个 id。 第二个表 (t( 中不存在的 ID 它不会给出任何结果。 我想做的是，如果第二个表中没有 id，我想为计数返回一个 0(count(att.adate((

谢谢。

Answer 1

将右侧表条件从WHERE移动到ON以获得真正的结果left join：

SELECT p.stId, count(att.adate)
FROM payments p
LEFT JOIN Attendence att ON att.stId = p.stId
and att.adate between '2018/03/01' and '2018/03/30 23:59:59.999' and att.cid 
= 10
where p.cyear = 2018 and p.cmonth = 3 and p.cId = 10
GROUP BY p.stId

(在WHERE，你会得到常规inner join结果。

Answer 2

您可能缺少结果，因为在 where 子句中，您有出勤表中列的条件。对于与付款表不匹配的情况，这些值将为 NULL。所以我会有这样的查询：

SELECT p.stId, count(att.adate)
FROM payments p
LEFT JOIN Attendence att ON att.stId = p.stId
where p.cyear = 2018 and p.cmonth = 3 and p.cId = 10
and (att.adate between '2018/03/01' and '2018/03/30 23:59:59.999' OR att.adate IS NULL) and (att.cid 
= 10 or att.cid IS NULL)
GROUP BY p.stId

Answer 3

您的问题是where将left join变成inner join。 我还想简化日期算术：

SELECT p.stId, count(a.adate)
FROM payments p LEFT JOIN
Attendence a
ON a.stId = p.stId AND a.cid = p.cid AND 
a.adate >= '2018-03-01' AND 
a.adate < '2018-04-01
WHERE p.cyear = 2018 and p.cmonth = 3 and p.cId = 10
GROUP BY p.stId;

我所做的更改是什么：

• 将第二个表上的筛选条件移动到ON子句。 这就是LEFT JOIN的工作方式。
• 我在cid上添加了一个JOIN条件，因此与10的比较只发生一次(在WHERE子句中(。
• 将日期格式更改为 YYYY-MM-DD。 这与ISO 8601标准日期格式一致。
• 将BETWEEN更改为>=和<- 我只是不想在月份级别构建日期范围时考虑毫秒。

了解为什么不使用日期/时间BETWEEN的好地方是Aaron Bertrand的博客What Do BETWEEN and the Devil Have In Common。