mysql - 如何在子查询中获取外列值

Question

我正在使用 php 和 mysql 实现一个社交网站，但我遇到了一个获取通知的查询。

我的通知表有以下列:

user_id,post_id,type(defines weather its a comment(value 2) or a post(1)),

status(0 read 1 not read),msg(for optional message)

评论表:

comment_id , user_id , post_id, comment , time , status

喜欢表格:

user_id , post_id(can store either post_id from post table or comment_id from comment table) , type(same as type of notification) all three columns makes a unique combo

现在我想得到这样的通知输出:

notification_id | message

1 | 3 peoples Liked your post

2 | 2 peoples Commented on your post

我创建了这个查询

SELECT `notification_id`,
    CASE 
    WHEN `msg` IS NOT NULL THEN msg
    WHEN `type`=1 THEN concat((SELECT count(user_id) from `likes` WHERE `post_id`=post_id AND `type`=1 AND `user_id`!='8'),' peoples Liked your post')
    WHEN `type`=2 THEN concat((SELECT count(*) from (SELECT DISTINCT `user_id` FROM `comments` WHERE `post_id`=post_id AND `user_id`!='8') AS commenters),' peoples Commented on your post')
    END AS message
FROM
    `notifications` WHERE `user_id`='8' -- 8 is just a user_id I was testing on.

它似乎工作正常，直到我意识到这些值不正确并且我很快意识到我的错误在于

`post_id`=post_id

始终为真，因此所有行都被计算在内。

我搜索了获取外部列值但找不到解决方案，请问您能帮我实现吗？

Answer 1

您应该能够在查询中使用别名。FROM 别名问题很可能来自您的 Comments Count 子查询。您可以将其重写为 Select count(distinct user_id)

SELECT n.notification_id,
    CASE 
    WHEN n.msg IS NOT NULL THEN n.msg
    WHEN type=1 THEN concat((SELECT count(user_id) from likes AS l WHERE l.post_id=n.post_id AND l.type=1 AND l.user_id != n.user_id),' peoples Liked your post')
    WHEN type=2 THEN concat((SELECT count(DISTINCT c.user_id) FROM comments AS c WHERE c.post_id=n.post_id AND c.USER_ID != n.user_id),' peoples Commented on your post')
    END AS message
FROM
    notifications AS n 
WHERE 
    n.user_id='8' -- 8 is just a user_id I was testing on.

这是一个有效的 sql fiddle 示例 DEMO