mysql联表查询总数满_mysql多个表联合查询数量

总共有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)