SQL连接，求和，分组而不是0 [英] SQL join, sum, group-by and instead of null 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 |贝克| [e-mail] | UHds（& | 2015-07-20 | 
 2 | Tom |[e-mail] | 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 |[e-mail] |UHds(&   |2015-07-20 |
2  |Tom   |[e-mail]   |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屋！