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

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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了php – 添加一个表来查询和计算数据SQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2589字，纯文字阅读大概需要4分钟。

内容图文

我尝试从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, i.image as user_image, p.image, 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

内容总结

以上是互联网集市为您收集整理的php – 添加一个表来查询和计算数据SQL全部内容，希望文章能够帮你解决php – 添加一个表来查询和计算数据SQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/905546.html

来源：【匿名】