左连接不起作用

Question

我写了以下查询

SELECT 
   us.Id as Id, us.Name as Name,
   SUM(CASE WHEN c.isPublish = 0 THEN 1 ELSE 0 END) AS PendingCoupons,
   SUM(CASE WHEN c.isPublish = 1 and convert(date,c.PublishedDate,101) >=  convert(date, GETDATE(), 101) THEN 1 ELSE 0 END) AS ApprovedCouponsToday,
   SUM(CASE WHEN c.isPublish = 0 and convert(date,c. CreateDate, 101) = convert(date, GETDATE(), 101) THEN 1 ELSE 0 END) AS PendingCouponsToday,
   SUM(CASE WHEN c.isPublish = 1 THEN 1 ELSE 0 END) AS ApprovedCoupons,
   SUM(CASE WHEN c.isPublish = 1 and c.Userid = us.Id and convert(date, c.PublishedDate, 101) >= convert(date, GETDATE(), 101) THEN 1 ELSE 0 END) AS ApprovedByUserToday,
   SUM(CASE WHEN c.isPublish = 1 and c.Userid = us.Id THEN 1 ELSE 0 END) AS ApprovedByUser,
   SUM(CASE WHEN c.ReviewVerify = 1 and convert(date, c.PublishedDate, 101) >= convert(date, GETDATE(), 101) THEN 1 ELSE 0 END) AS ProcessToday,
   COUNT(*) AS Total
FROM 
   Users AS us
LEFT JOIN 
   Coupon c ON Userid = us.Id
GROUP BY 
   us.Name , us.Id

我有以下两个表

​

​

在运行上面的查询之后，结果总是这样

​

​

在这个查询中是否有任何错误，因为它总是返回给我计数“0”，并且我有几乎100个用户的优惠券，但它没有显示

Answer 1

虽然LEFT JOIN根本不会给出任何与优惠券相关的结果，但似乎由于RIGHT JOIN可以工作并给出正确的答案(可能还有一个伪造的用户id)，优惠券是在一个不存在的用户上注册的。

LEFT JOIN要求数据存在于表的左侧，而RIGHT JOIN则相反。换句话说，数据存在于您最右侧的表(优惠券)中，与左侧(用户)没有任何连接

Answer 2

我认为您可能需要使用RIGHT JOIN从Coupon表中获取所有记录。

Answer 3

您没有使用正确的联接。您想获得用户id的优惠券吗？使用此联接。FROM Users AS us左加入us.Id = c.Userid上的优惠券c如果这不起作用，则使用: FROM Users AS us左外部加入优惠券c ON us.Id = c.Userid