我正在尝试获取Facebook通知样式。我很困惑。我怎样才能让所有对同一帖子发表评论的用户都清楚。
我的数据库如下:
通知表
id | post_id | to_user_id | from_user_id | type | datetime | type
1 45 101 107 comment 2016-3-10 11:03:06 ads
2 45 101 106 comment 2016-3-10 12:05:06 ads
3 45 101 105 comment 2016-3-10 12:08:05 ads
4 55 101 106 comment 2016-3-10 12:05:06 ads
5 55 101 105 comment 2016-3-10 12:08:05 ads
寄存器表
id | name
101 John
105 Jack
106 Anna
107 Leo
Post_ads表
id | title
45 Some text
55 other text
现在我想加入来自 post_id
的 3 个表格post_ads
和来自 from_user_id
的 register
并确认所有在同一post_id
发表评论的用户,如果有超过 2 个用户在同一帖子上发表评论,那么我想显示 2 个用户名后的用户计数或数量。
我应该像:
Leo, Anna and 1 other commented on post Some text.
Anna, Jack commented on post Other text.
上面Leo,Anna和其他用户名来自注册表和帖子,喜欢some text, other text
来自post_ads表
我可以连接所有表并显示三行结果,但是我如何像上面那样查询结果。这可以通过 mysql 查询来实现吗?我的查询如下:
SELECT * FROM notification INNER JOIN `register` ON `register`.id = `notification`.from_user_id INNER JOIN `post_ads` ON `post_ads`.id = `notification`.post_id WHERE `notification`.to_user_id = $userid"
请首先检查查询是否适用于您的情况。如果需要,稍后我可以解释。
查询:
SELECT
CONCAT(
substring_index(group_concat(finalTable.name SEPARATOR ','), ',', 2),
IF((finalTable.total - 2) > 0,CONCAT(' and ',(finalTable.total - 2),' others'),''),
CONCAT(' commented on post ',finalTable.title,'')
) AS output
FROM
(
SELECT
DISTINCT n.post_id,
n.from_user_id,
post_ads.title,
register.`name`,
t.total
FROM notification n
INNER JOIN
(
SELECT
post_id,
COUNT(DISTINCT from_user_id) total
FROM notification
GROUP BY post_id ) t
ON n.post_id = t.post_id
INNER JOIN register ON register.id = n.from_user_id
INNER JOIN post_ads ON post_ads.id = n.post_id
) finalTable
GROUP BY finalTable.post_id;
输出:
Leo,Anna and 1 others commented on post Some text
Anna,Jack commented on post Other text
SQL 小提琴演示