A表 (销售)
id price number, saledate
1 2.0 3 2007-7-8
2 3.0 4 2007-7-8
3 3.0 5 2007-8-9
4 1.0 6 2007-9-10B表 (开支)
id amount occurdate
1 20.0 2007-6-3
2 10.0 2007-7-8
3 30.0 2007-8-9
4 10.0 2007-8-9现在就是查处每天的总销售额和总开支(按日期排序),要得到以下结果,该怎么写Sql?谢谢各位了sales amount statdate
0 20 2007-6-3
18.0 10 2007-7-8
15.0 40.0 2007-8-9
6.0 0 2007-9-10
id price number, saledate
1 2.0 3 2007-7-8
2 3.0 4 2007-7-8
3 3.0 5 2007-8-9
4 1.0 6 2007-9-10B表 (开支)
id amount occurdate
1 20.0 2007-6-3
2 10.0 2007-7-8
3 30.0 2007-8-9
4 10.0 2007-8-9现在就是查处每天的总销售额和总开支(按日期排序),要得到以下结果,该怎么写Sql?谢谢各位了sales amount statdate
0 20 2007-6-3
18.0 10 2007-7-8
15.0 40.0 2007-8-9
6.0 0 2007-9-10
create table sales(
id int not null AUTO_INCREMENT PRIMARY key,
price DECIMAL(2,1),
number int,
saledate date
) engine = myisam;
create table amount(
id int not null AUTO_INCREMENT PRIMARY key,
amount decimal(3,1),
occurdate date
) engine = myisam;
insert into sales(price,number,saledate) values
(2.0,3,20070708),
(3.0,4,20070708),
(3.0,5,20070809),
(1.0,6,20070910);
insert into amount(amount,occurdate) values
(20.0,20070603),
(10.0,20070708),
(30.0,20070809),
(10.0,20070809);
select sum(sales) sales,sum(amount) amount,saledate from
(
select sum(price*number) sales,0 amount,saledate from sales group by saledateunion allselect 0 sales,sum(amount),occurdate from amount group by occurdate
) T group by saledate order by saledate asc;
结果:query result(4 records)
sales amount saledate
0.0 20.0 2007-06-03
18.0 10.0 2007-07-08
15.0 40.0 2007-08-09
6.0 0.0 2007-09-10