如何从表左联接中获取列？
如何从表左联接中获取列？
提问于 2014-01-08 14:36:21
SqlFidle在这里。
Mysql查询：
SELECT
n.Type as Type,
n.UserIdn as UserIdn,
u.Username as Username,
n.NewsIdn as NewsIdn,
n.Header as Header,
n.Text as Text,
n.Tags as Tags,
n.ImageLink as ImageLink,
n.VideoLink as VideoLink,
n.DateCreate as DateCreate,
l.Uplikes as Uplikes,
ld.DownLikes as DownLikes
FROM News n
left join (Select LikeIdn,NewsIdn,Count(*) as UpLikes FROM Likes WHERE Type='up') as l ON l.NewsIdn=n.NewsIdn
left join (Select LikeIdn,NewsIdn,Count(*) as DownLikes FROM Likes WHERE Type='down') as ld ON ld.NewsIdn=n.NewsIdn
left join Users u on u.UserIdn = n.UserIdn
WHERE (l.Uplikes - ld.DownLikes) < 20 AND DateCreate < STR_TO_DATE('2014-01-01 17:23:41', '%Y-%m-%d %H:%i:%s')
ORDER BY n.DateCreate Desc LIMIT 0, 10在它的查询中，我无法获得l.Uplikes as Uplikes和ld.DownLikes as DownLikes。
怎么弄到？
P.S.：如果删除行(l.Uplikes - ld.DownLikes) < 20 AND结果查询有4行。
回答 2
Stack Overflow用户
回答已采纳
发布于 2014-01-08 14:41:30
问题是left outer join正在为likes和dislikes插入NULL值。因此，使用coalesce()将它们转换为0：
WHERE (coalesce(l.Uplikes, 0) - coalesce(ld.DownLikes, 0)) < 20 AND
DateCreate < STR_TO_DATE('2014-01-01 17:23:41', '%Y-%m-%d %H:%i:%s')顺便说一句，写这个查询有很多种方法。然而，你的版本是不正确的。在两个子查询中需要group by NewsIdn。否则，你会得到总体上的喜欢和不喜欢。但是，两个子查询也是不必要的。您可以使用条件聚合一步而不是两步计算likes和dislikes：
FROM News n left join
(Select NewsIdn, sum(Type = 'up') as UpLikes, sum(Type = 'down') as dislikes
FROM Likes
group by NewsIdn
) ud
ON ud.NewsIdn = n.NewsIdn left join
Users u
on u.UserIdn = n.UserIdnStack Overflow用户
发布于 2014-01-08 14:44:57
是的，正如您已经注意到的，这是因为您在where子句中使用了Uplikes和DownLikes的值，并且在您喜欢的表中没有相应的行(因此获得null而不是0)。
如果您在where子句中与0合并(我猜在您的选择中)，您将得到您提到的这四个结果返回：
SELECT
n.Type as Type,
n.UserIdn as UserIdn,
u.Username as Username,
n.NewsIdn as NewsIdn,
n.Header as Header,
n.Text as Text,
n.Tags as Tags,
n.ImageLink as ImageLink,
n.VideoLink as VideoLink,
n.DateCreate as DateCreate,
COALESCE(l.Uplikes,0) as Uplikes,
COALESCE(ld.DownLikes,0) as DownLikes
FROM News n
left join (Select LikeIdn,NewsIdn,Count(*) as UpLikes FROM Likes WHERE Type='up' Group by NewsIdn) as l ON l.NewsIdn=n.NewsIdn
left join (Select LikeIdn,NewsIdn,Count(*) as DownLikes FROM Likes WHERE Type='down' Group by NewsIdn) as ld ON ld.NewsIdn=n.NewsIdn
left join Users u on u.UserIdn = n.UserIdn
WHERE (COALESCE(l.Uplikes,0) - COALESCE(ld.DownLikes,0) < 20) AND DateCreate < STR_TO_DATE('2014-01-01 17:23:41', '%Y-%m-%d %H:%i:%s')
ORDER BY n.DateCreate Desc LIMIT 0, 10或者如果更容易：
Sql小提琴
编辑： +1给Gordon，因为它注意到子查询中缺少的分组-更新了这个答案以反映这一点。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20998564
复制相关文章
相似问题
腾讯云开发者
