mysql多个表联合查询数量

Question

总共有3个表（user表的id与sport、grade表的user_id是关联的）
对应的字段如下。

mysql> select * from user;
+----+-------+
| id | user  |
+----+-------+
|  0 | zhang |
|  1 | li    |
+----+-------+
2 rows in set (0.00 sec)

建表语句如下。

mysql> show create table user;
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(10) NOT NULL,
  `user` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from sport;
+----+---------+------------+
| id | user_id | sport_type |
+----+---------+------------+
|  0 |       0 | football   |
|  1 |       0 | football   |
|  2 |       0 | football   |
|  3 |       1 | basketball |
|  4 |       1 | basketball |
+----+---------+------------+
5 rows in set (0.00 sec)

建表语句如下

mysql> show create table sport;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sport | CREATE TABLE `sport` (
  `id` int(10) NOT NULL,
  `user_id` int(10) NOT NULL,
  `sport_type` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from grade;
+----+---------+-------+
| id | user_id | level |
+----+---------+-------+
|  0 |       0 |     9 |
|  1 |       0 |     9 |
|  2 |       0 |     9 |
|  3 |       0 |     9 |
|  4 |       0 |     9 |
|  5 |       1 |    10 |
|  6 |       1 |    10 |
+----+---------+-------+
7 rows in set (0.00 sec)

建表语句如下。

mysql> show create table grade;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| grade | CREATE TABLE `grade` (
  `id` int(10) NOT NULL,
  `user_id` int(10) NOT NULL,
  `level` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

最终想计算每个用户的sport_type的数量和所在level的数量，但显然如下的联合索引的sql结果是错误的，还请各位大虾协助，谢谢。

mysql> select a.id,a.user,count(b.sport_type) as Sport,count(c.level) as Level from user as a left join sport as b ON a.id = b.user_id left join grade as c ON a.id = c.user_id group by a.id,a.user;
+----+-------+-------+-------+
| id | user  | Sport | Level |
+----+-------+-------+-------+
|  0 | zhang |    21 |    21 |
|  1 | li    |     2 |     0 |
+----+-------+-------+-------+
2 rows in set (0.00 sec)

期望的结果是这样的，zhang的人有3个football、5个9的level，li有2个basketball，2个10的level。（相当于加和）

+----+-------+-------+-------+
| id | user  | Sport | Level |
+----+-------+-------+-------+
|  0 | zhang |     3 |     5 |
|  1 | li    |     2 |     2 |
+----+-------+-------+-------+
2 rows in set (0.00 sec)

Answer 1

select user, t1.count1, t2.count2
from user t
left join (
    select user_id, count(sport_type) as count1
    from sport group by user_id
) t1
on t.id = t1.user_id
left join (
    select user_id, count(level) as count2
    from grade group by user_id
) t2
on t.id = t2.user_id
order by t.id

已实测，结果正确。

Answer 2

sport表和grade表分别group by userid，这样出来两张临时表，和user表联查
最好吧建表语句贴上，方便同志们测试，呵呵

Answer 3

CREATE TEMPORARY TABLE tmp1
SELECT user_id, COUNT(sport_type) AS sport_type_count FROM sport GROUP BY user_id;
CREATE TEMPORARY TABLE tmp2
SELECT user_id, COUNT(`level`) AS level_count FROM grade GROUP BY user_id;

SELECT u.id, u.user, IFNULL(t1.sport_type_count, 0), IFNULL(t2.level_count, 0) FROM `user` u
LEFT JOIN tmp1 t1 ON u.id = t1.user_id
LEFT JOIN tmp2 t2 ON u.id = t2.user_id

这样写不知可行

Answer 4

你想要的结果，是不是这个：
id	user	Sport	Level
+----+-------+-------+-------+
0	zhang	3	66
1	li	2	0

Level字段确定是求的COUNT，而不是SUM？

Answer 5

写成两条语句吧，写成一条的话貌似还不行呢。