php – 添加一个表来查询和计算数据SQL

发布时间：2020-12-13 17:10:32 所属栏目：PHP教程来源：网络整理

导读：我尝试从4个表中选择数据(最后一个表需要计算数据) 我的MySQL表结构用户 idusername 图片 iduser_idimage user_follow iduser_idfollow_id 评论 iduser_idimage_idtext 我有这个SQL查询： $sql = "SELECT u.username as user,i.image as user_image,p.image

我尝试从4个表中选择数据(最后一个表需要计算数据)

我的MySQL表结构

用户

id
username

图片

id
user_id
image

user_follow

id
user_id
follow_id

id
user_id
image_id
text

我有这个SQL查询：

$sql = "SELECT u.username as user,i.image as user_image,p.image,p.date
            FROM users u              
            LEFT JOIN user_follow f ON u.id = f.follow_id
            LEFT JOIN images p ON p.user_id = u.id
            LEFT JOIN images i ON i.id = (SELECT b.id FROM images AS b where p.user_id = b.user_id ORDER BY b.id DESC LIMIT 1)
            WHERE f.user_id = 3 OR p.user_id = 3       
            ORDER BY p.date DESC";

此行返回用户当前图像(最后一张图像)

LEFT JOIN images i ON i.id = (SELECT b.id FROM images AS b where p.user_id = b.user_id ORDER BY b.id DESC LIMIT 1)

它返回我和我的朋友的所有图像

[0] => Array
    (
        [user] => 8888
        [user_image] => second.jpg
        [image] => second.jpg
        [date] => 2012-01-24 14:42:27
    )

[1] => Array
    (
        [user] => 8888
        [user_image] => second.jpg
        [image] => first.jpg
        [date] => 2012-01-24 14:42:27
    )

[2] => Array
    (
        [user] => 3333
        [user_image] => ax46l7v7vugnesk10whk_339.jpg
        [image] => ax46l7v7vugnesk10whk_339.jpg
        [date] => 2012-01-24 01:54:19
    )

[3] => Array
    (
        [user] => 3333
        [user_image] => ax46l7v7vugnesk10whk_339.jpg
        [image] => aaaaaaaa.jpg
        [date] => 2012-01-24 01:49:57
    )

我试着补充一下

left join commentaries c ON c.user_id = u.id

结果是

[2] => Array
    (
        [user] => 3333
        [user_image] => ax46l7v7vugnesk10whk_339.jpg
        [image] => ax46l7v7vugnesk10whk_339.jpg
        [date] => 2012-01-24 01:54:19
        [id] => 1
    )

[3] => Array
    (
        [user] => 3333
        [user_image] => ax46l7v7vugnesk10whk_339.jpg
        [image] => ax46l7v7vugnesk10whk_339.jpg
        [date] => 2012-01-24 01:54:19
        [id] => 2
    )

[4] => Array
    (
        [user] => 3333
        [user_image] => ax46l7v7vugnesk10whk_339.jpg
        [image] => aaaaaaaa.jpg
        [date] => 2012-01-24 01:49:57
        [id] => 1
    )

[5] => Array
    (
        [user] => 3333
        [user_image] => ax46l7v7vugnesk10whk_339.jpg
        [image] => aaaaaaaa.jpg
        [date] => 2012-01-24 01:49:57
        [id] => 2
    )

如果有评论,则重复用户(顺便说一句[用户] => 3333在示例中有2条评论)

我正在尝试添加一个表“评论”并计算每个图片(来自我和我的朋友)有多少评论如果没有这样的$user_id的评论然后返回0

解决方法

您需要使用GROUP BY来计算组中的行数(在您的情况下,每个图像的注释).这个查询应该做的伎俩：

SELECT u.username as user,p.date,COALESCE ( imgcount.cnt,0 ) as comments
            FROM users u              
            LEFT JOIN user_follow f ON u.id = f.follow_id
            LEFT JOIN images p ON p.user_id = u.id
            LEFT JOIN images i ON i.id = (SELECT b.id FROM images AS b where p.user_id = b.user_id ORDER BY b.id DESC LIMIT 1)
            LEFT JOIN 
            ( SELECT image_id,COUNT(*) as cnt FROM
                 commentaries 
              GROUP BY image_id  ) imgcount
            ON p.id = imgcount.image_id
            WHERE f.user_id = 3 OR p.user_id = 3       
            ORDER BY p.date DESC

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!