三表关联一对多时sum函数的使用 demo

来源：互联网发布：2016年云计算政策编辑：程序博客网时间：2024/05/18 15:23

create tableCREATE TABLE `user` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert into user (id, name) values(1,'haiyang');insert into user (id, name) values(2,'test');

create tableCREATE TABLE `user_cost` (
   `id` int(11) DEFAULT NULL,
   `t` time DEFAULT NULL,
   `cost` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
create tableCREATE TABLE `user_sale` (
   `id` int(11) DEFAULT NULL,
   `u_name` varchar(10) DEFAULT NULL,
   `age` int(11) DEFAULT NULL,
   `time` time DEFAULT NULL,
   `sale` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

idname1haiyang2test
idsal111111212131
idcost11112121

SELECT a.id,a.name,SUM(b.sale),c.sumcost,(SUM(b.sale)-c.sumcost) money FROM USER a , user_sale b, (SELECT id id, SUM(cost) sumcost FROM user_cost GROUP BY id) c WHERE a.id=b.id=c.id GROUP BY a.id,a.name;

SELECT a.id,a.name,SUM(b.sale),c.sumcost,(SUM(b.sale)-c.sumcost) money FROM USER a JOIN user_sale b ON a.id=b.id JOIN (SELECT id id, SUM(cost) sumcost FROM user_cost GROUP BY id) c ON a.id=c.id GROUP BY a.id,a.name;

查询结果如下表：

idnamesum(b.salesumcostmoney1haiyang3212test321

0 0