总共有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)
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
已实测,结果正确。
写成两条语句吧,写成一条的话貌似还不行呢。
sport表和grade表分别group by userid,这样出来两张临时表,和user表联查
最好吧建表语句贴上,方便同志们测试,呵呵
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
这样写不知可行
你想要的结果,是不是这个: | |||
---|---|---|---|
id | user | Sport | Level |
+----+-------+-------+-------+ | |||
0 | zhang | 3 | 66 |
1 | li | 2 | 0 |
Level字段确定是求的COUNT,而不是SUM?