SQL连接,求和,分组而不是0 [英] SQL join, sum, group-by and instead of null 0
本文介绍了SQL连接,求和,分组而不是0的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要一个带有posts-id,posts-title,posts-created_at,users-name和likes-status的查询。如果不存在,likes-status应该为0。我需要喜欢状态的总和。像这样:
id | title | created_at | name | status |
=========================================
1 |你好1 | 2015-07-22 |贝克| 0 | // 1 - 1
2 | Hallo 2 | 2015-07-23 | Tom | 0 | //如果不存在= 0
我的尝试:
SELECT p.id,p.title,p.created_at,u.name,IFNULL(l.status,0)作为状态
FROM帖子p
LEFT OUTER JOIN喜欢l ON l.post_id = p.id
INNER JOIN用户u ON u.id = p.user_id
结果:
id | title | created_at | name | status |
=========================================
1 |你好1 | 2015-07-22 |贝克| 1 | // sum this
1 | Hello 1 | 2015-07-22 | Baker | -1 | // with this
2 | Hallo 2 | 2015-07-23 | Tom | 0 |
用户表
id | name | email | password | created_at |
============================================== ======
1 |贝克| baker@example.com | UHds(& | 2015-07-20 |
2 | Tom |tom@example.com | ihj =)? | 2015-07-21 |
张贴表
id | user_id | title | created_at |
==================================
1 | 1 |你好1 | 2015 -07-22 |
2 | 2 |你好2 | 2015-07-23 |
喜欢桌子
id | user_id | post_id | status | created_at |
===========================================
1 | 1 | 1 | 1 | 2015-07-24 |
2 | 2 | 1 | -1 | 2015-07-25 |
解决方案
> left join ,那么你应该继续它。这实际上并不影响你的查询,它需要 group by ,但它仍然是一个好主意:
<$作为状态
FROM posts p.id,p.title,p.created_at,u.name,COALESCE(sum(l.status),0)LEFT OUTER JOIN
喜欢l
ON l.post_id = p.id LEFT OUTER JOIN
用户u
ON u.id = p.user_id
GROUP BY p.id,p .title,p.created_at,u.name;
I need a query with posts-id, posts-title, posts-created_at, users-name and likes-status. likes-status should be 0 if none exists. And i need the sum of likes-status. Like this:
id |title |created_at |name |status |
=========================================
1 |Hello 1 |2015-07-22 |Baker |0 | // 1 - 1
2 |Hallo 2 |2015-07-23 |Tom |0 | // if not exists = 0
My attempt:
SELECT p.id, p.title, p.created_at, u.name, IFNULL(l.status, 0) as status
FROM posts p
LEFT OUTER JOIN likes l ON l.post_id = p.id
INNER JOIN users u ON u.id = p.user_id
Result:
id |title |created_at |name |status |
=========================================
1 |Hello 1 |2015-07-22 |Baker |1 | // sum this
1 |Hello 1 |2015-07-22 |Baker |-1 | // with this
2 |Hallo 2 |2015-07-23 |Tom |0 |
users table
id |name |email |password |created_at |
====================================================
1 |Baker |baker@example.com |UHds(& |2015-07-20 |
2 |Tom |tom@example.com |ihj=)? |2015-07-21 |
posts table
id |user_id |title |created_at |
==================================
1 |1 |Hello 1 |2015-07-22 |
2 |2 |Hello 2 |2015-07-23 |
likes table
id |user_id |post_id |status |created_at |
===========================================
1 |1 |1 |1 |2015-07-24 |
2 |2 |1 |-1 |2015-07-25 |
解决方案
If you start a query with left join
, then you should to continue it. This doesn't actually affect your query, which needs a group by
, but it is still a good idea:
SELECT p.id, p.title, p.created_at, u.name, COALESCE(sum(l.status), 0) as status
FROM posts p LEFT OUTER JOIN
likes l
ON l.post_id = p.id LEFT OUTER JOIN
users u
ON u.id = p.user_id
GROUP BY p.id, p.title, p.created_at, u.name;
这篇关于SQL连接,求和,分组而不是0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文