Софтуерно Инженерство
Loading...
+ Нов въпрос
TeodoraL.Kostadinova avatar TeodoraL.Kostadinova 2 Точки

Databases MySQL Retake Exam - 03 Sept 2017 12. Commenting Myself

До тук добре, 3/10

но как да преброим null-вете...
 

SELECT u.id, u.username,self_com.my_comments
FROM users u
LEFT JOIN (SELECT p.user_id,p.id, COUNT(c.id) as `my_comments`
FROM posts p
 JOIN comments c
ON p.id=c.post_id
WHERE c.user_id=p.user_id
GROUP BY p.user_id
) as `self_com`
ON u.id=self_com.user_id
GROUP BY u.id
ORDER BY my_comments DESC, u.id ASC;

 

Тагове:
0
Databases Basics - MySQL
BalioFVFX avatar BalioFVFX 40 Точки
SELECT u.id, u.username, IFNULL(self_com.my_comments, 0)
FROM users u
LEFT JOIN (SELECT p.user_id,p.id, COUNT(c.id) as `my_comments`
FROM posts p
 JOIN comments c
ON p.id=c.post_id
WHERE c.user_id=p.user_id
GROUP BY p.user_id
) as `self_com`
ON u.id=self_com.user_id
GROUP BY u.id
ORDER BY my_comments DESC, u.id ASC;

 

0