|
|
|
 当前位置：首页 > > 将二个表中不同类型、不同编号的合计数合并到另一个表中 |
统计：将二个表中不同类型、不同编号的合计数合并到另一个表中
第一个表GOODSINMX(select addtime,huohao,title,type,shulian,price,price*shulian as je,0 as slout,0.0 as jeout from GOODSINMX)
| addtime |
huohao |
title |
type |
shulian |
price |
je |
slout |
jeout |
| 2011-04-21 13:58:00 |
GOO201104180001 |
产品一 |
1 |
123 |
11.00 |
1353.00 |
0 |
.0 |
| 2011-04-21 14:00:00 |
GOO201104180001 |
产品一 |
1 |
10 |
11.00 |
110.00 |
0 |
.0 |
| 2011-04-21 14:42:00 |
GOO201104180004 |
产品三 |
3 |
60 |
70.00 |
4200.00 |
0 |
.0 |
第二个表GOODSOUTMX(select addtime,huohao,title,type,0 as shulian,0.0 as je,shulian as slout,price,price*shulian as jeout from GOODSOUTMX)
| addtime |
huohao |
title |
type |
shulian |
je |
slout |
price |
jeout |
| 2011-05-12 10:46:00 |
GOO201104180001 |
产品一 |
1 |
0 |
.0 |
1 |
70.00 |
70.00 |
| 2011-05-12 14:17:00 |
GOO201104180004 |
产品三 |
3 |
0 |
.0 |
1 |
12.00 |
12.00 |
结果(select huohao,title,type,sum(shulian) as shulian1,avg(price) as price1,sum(je) as je1,sum(slout) as shulian2,avg(price) as price2,sum(jeout) as je2 from (select addtime,huohao,title,type,shulian,price,price*shulian as je,0 as slout,0.0 as jeout from GOODSINMX union all select addtime,huohao,title,type,0 as shulian,0.0 as je,shulian as slout,price,price*shulian as jeout from GOODSOUTMX ) aaa where addtime BETWEEN '2001-06-01 00:00:00' AND '2011-06-30 23:59:59' AND type =1 group by huohao,title,type order by huohao desc）
| huohao |
title |
type |
shulian1 |
price1 |
je1 |
shulian2 |
price2 |
je2 |
| GOO201104180001 |
产品一
|
1 |
133 |
11.00 |
1463.00 |
1 |
70.00 |
70.00 |
| GOO201104180004 |
产品三 |
3 |
60 |
70.00 |
4200.00 |
1 |
12.00 |
12.00 |
|
|
