douxin8610 2014-07-13 21:44
浏览 749
已采纳

mysql使用LEFT JOIN从多个表中复用多个COUNT()

I want to show the conclusion of all users.

I have 3 tables.

table post

post_id(index)     user_id
1                  1
2                  3
3                  3
4                  4

table photo

photo_id(index)     user_id
1                   2
2                   4
3                   1
4                   1

table video

photo_id(index)     user_id
1                   4
2                   4
3                   3
4                   3

and in table user

user_id(index)     user_name
1                  mark
2                  tommy
3                  john
4                  james

in fact, it has more than 4 rows for every tables.

I want the result like this.

id     name      post    photo   videos
1      mark      1       2       0
2      tommy     0       1       0
3      john      2       0       2
4      james     1       1       2
5      ..        ..      ..      ..

Code below is SQL that can work correctly but very slow, I will be true appreciated if you help me how it using LEFT JOIN for it. Thanks.

SQL

"select user.*, 
(select count(*) from post where post.userid = user.userid) postCount,
(select count(*) from photo where photo.userid = user.userid) photoCount,
(select count(*) from video where video .userid = user.userid) videoCount
from user order by user.id"

(or ORDER BY postCount, photoCount or videoCount ASC or DESC as i want )

I done researched before but no any helped me.

  • 写回答

3条回答 默认 最新

  • doushi8599 2014-07-13 21:49
    关注
        SELECT u.user_id, 
               u.user_name,
               COUNT(DISTINCT p.post_id) AS `postCount`,
               COUNT(DISTINCT ph.photo_id) AS `photoCount`,
               COUNT(DISTINCT v.video_id) AS `videoCount`
          FROM user u
     LEFT JOIN post p
            ON p.user_id = u.user_id
     LEFT JOIN photo ph
            ON ph.user_id = u.user_id
     LEFT JOIN video v
            ON v.user_id = u.user_id
      GROUP BY u.user_id
      ORDER BY postCount;
    

    Live DEMO

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 ecplise在连接数据库时显示加载驱动成功但是数据库连接失败
  • ¥15 visionmaster启动失败,提示为“机器不满足授权而被禁用”
  • ¥50 用logisim设计16位单时钟周期cpu
  • ¥15 IDEA中圈复杂度如何具体设置
  • ¥50 labview采集不了数据
  • ¥15 Multisim红外倒车雷达仿真中距离问题
  • ¥15 请上面代码做什么处理或什么混淆
  • ¥15 英雄联盟自定义房间置顶
  • ¥15 W5500网线插上无反应
  • ¥15 如何用字典的Key,显示在WPF的xaml中