主表一条记录子表多条记录,怎样合计 主表: ID NAME 1 张三 2 李四3 小五子表:ID ZID VALUE1 1 52 1 53 2 34 2 4结果:====================ID NAME VALUE(子表)1 张三 102 李四 73 小五 0 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 mysql> select * from ccc;+------+------+| id | name |+------+------+| 1 | 张三 || 2 | 李四 || 3 | 小五 |+------+------+3 rows in set (0.00 sec)mysql> select * from cccc;+------+------+-------+| id | zid | value |+------+------+-------+| 1 | 1 | 5 || 2 | 1 | 5 || 3 | 2 | 3 || 4 | 2 | 4 |+------+------+-------+4 rows in set (0.00 sec)mysql> select ccc.*,sum(cccc.value) from ccc left join cccc -> on ccc.id=cccc.zid -> group by ccc.name -> order by id;+------+------+-----------------+| id | name | sum(cccc.value) |+------+------+-----------------+| 1 | 张三 | 10 || 2 | 李四 | 7 || 3 | 小五 | NULL |+------+------+-----------------+3 rows in set (0.00 sec) ORselect ccc.*,if(ccc.name is not null,sum(cccc.value),NULL) as value from ccc left join ccccon ccc.id=cccc.zidgroup by ccc.nameorder by id select a.ID,a.NAME,sum(b.VALUE)from 主表 a left join 子表 b on a.ID=b.ZIDgroup by a.ID select a.ID,a.NAME,sum(b.VALUE)from 主表 a left join 子表 b on a.ID=b.ZIDgroup by a.ID,a.name mysql> select c1.id, c1.name, ifnull(sum(c2.value),0) from c1 left join c2 on c1.id=c2.zid group byc1.id;+----+------+-------------------------+| id | name | ifnull(sum(c2.value),0) |+----+------+-------------------------+| 1 | 张三 | 10 || 2 | 李四 | 7 || 3 | 小五 | 0 |+----+------+-------------------------+3 rows in set (0.03 sec) mysql无法插入中文字符 mysql的突然变成本地不能用密码,远程要用密码才能登录,这是神马情况???求解 想学mysql了 大家帮我分析一下mysql的问题 Found block with too small length(求救) 有条件随机获取记录的sql语句 有没有mysql的分页存储过程!! 真的没分了 java Mysql连接错误 大侠帮 有个概念想问下:什么是mysql的断口 PHP,MYSQL 见不到 存储过程怎么修改当前游标列值 纯粹的MyISAM纯粹引擎表,内存计算有疑问?
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 小五 |
+------+------+
3 rows in set (0.00 sec)mysql> select * from cccc;
+------+------+-------+
| id | zid | value |
+------+------+-------+
| 1 | 1 | 5 |
| 2 | 1 | 5 |
| 3 | 2 | 3 |
| 4 | 2 | 4 |
+------+------+-------+
4 rows in set (0.00 sec)
mysql> select ccc.*,sum(cccc.value) from ccc left join cccc
-> on ccc.id=cccc.zid
-> group by ccc.name
-> order by id;
+------+------+-----------------+
| id | name | sum(cccc.value) |
+------+------+-----------------+
| 1 | 张三 | 10 |
| 2 | 李四 | 7 |
| 3 | 小五 | NULL |
+------+------+-----------------+
3 rows in set (0.00 sec)
select ccc.*,if(ccc.name is not null,sum(cccc.value),NULL) as value from ccc left join cccc
on ccc.id=cccc.zid
group by ccc.name
order by id
from 主表 a left join 子表 b on a.ID=b.ZID
group by a.ID
from 主表 a left join 子表 b on a.ID=b.ZID
group by a.ID,a.name
c1.id;
+----+------+-------------------------+
| id | name | ifnull(sum(c2.value),0) |
+----+------+-------------------------+
| 1 | 张三 | 10 |
| 2 | 李四 | 7 |
| 3 | 小五 | 0 |
+----+------+-------------------------+
3 rows in set (0.03 sec)