问题是这样的
我有基本信息表A包含字段
code(主键) name
00001 商品名称1
00002 商品名称2
00003 商品名称3
00004 商品名称4
00005 商品名称5进货J包含字段
id(主键) code num price money
1 00001 10.00 22.00 220.0000
2 00002 30.00 10.00 300.0000
3 00004 10.00 10.00 100.0000
4 00001 10.00 24.00 240.0000退货表T字段
id(主键) code num price money
1 00001 5.00 22.00 110.0000
2 00002 20.00 10.00 200.0000
3 00001 5.00 20.00 100.0000
销售表X字段
id(主键) code num price money
1 00001 5.00 30.00 150.0000
2 00002 2.00 20.00 40.0000
3 00004 5.00 20.00 100.0000
4 00001 5.00 40.00 200.0000销售表XT字段
id(主键) code num price money
1 00001 1.00 30.00 30.0000
2 00002 1.00 20.00 20.0000
3 00004 2.00 20.00 40.0000我现在想得到一个总表
条码 商品名称 进货数量 平均进价 退数量 平均退价 销售数量 平均售价 销售退货数量 平均销退价格 库存
| | | | | | | | | | |
code(主键) name NJ PJ NT PT NX PX NXT PXT KC
00001 商品名称1 20.00 23.00 10.00 21.00 10.00 35.00 1.00 30.00 1.00
00002 商品名称2 30.00 10.00 20.00 10.00 2.00 20.00 1.00 20.00 9.00
00004 商品名称4 10.00 10.00 5.00 10.00 5.00 20.00 2.00 20.00 2.00
用SQL语句怎么实现呀,实际上我还想把最高和最低的价格放进去,不过字段太多了不好看,只要能实现上面的,我那把最高和最低的价格放上去也不难。还有一个疑问就是如果想做到,没有进货也给退货或者销售那样能不能做到。我只能发100分的帖,分不够我等下再加。
我有基本信息表A包含字段
code(主键) name
00001 商品名称1
00002 商品名称2
00003 商品名称3
00004 商品名称4
00005 商品名称5进货J包含字段
id(主键) code num price money
1 00001 10.00 22.00 220.0000
2 00002 30.00 10.00 300.0000
3 00004 10.00 10.00 100.0000
4 00001 10.00 24.00 240.0000退货表T字段
id(主键) code num price money
1 00001 5.00 22.00 110.0000
2 00002 20.00 10.00 200.0000
3 00001 5.00 20.00 100.0000
销售表X字段
id(主键) code num price money
1 00001 5.00 30.00 150.0000
2 00002 2.00 20.00 40.0000
3 00004 5.00 20.00 100.0000
4 00001 5.00 40.00 200.0000销售表XT字段
id(主键) code num price money
1 00001 1.00 30.00 30.0000
2 00002 1.00 20.00 20.0000
3 00004 2.00 20.00 40.0000我现在想得到一个总表
条码 商品名称 进货数量 平均进价 退数量 平均退价 销售数量 平均售价 销售退货数量 平均销退价格 库存
| | | | | | | | | | |
code(主键) name NJ PJ NT PT NX PX NXT PXT KC
00001 商品名称1 20.00 23.00 10.00 21.00 10.00 35.00 1.00 30.00 1.00
00002 商品名称2 30.00 10.00 20.00 10.00 2.00 20.00 1.00 20.00 9.00
00004 商品名称4 10.00 10.00 5.00 10.00 5.00 20.00 2.00 20.00 2.00
用SQL语句怎么实现呀,实际上我还想把最高和最低的价格放进去,不过字段太多了不好看,只要能实现上面的,我那把最高和最低的价格放上去也不难。还有一个疑问就是如果想做到,没有进货也给退货或者销售那样能不能做到。我只能发100分的帖,分不够我等下再加。
a.code as 条码,
a.name as 商品名称,
isnull(b.num ,0) as 进货数量,
isnull(b.price,0) as 平均进价,
isnull(c.num ,0) as 退货数量,
isnull(c.price,0) as 平均退价,
isnull(d.num ,0) as 销售数量,
isnull(d.price,0) as 平均售价,
isnull(e.num ,0) as 销售退货数量,
isnull(e.price,0) as 平均销退价格,
(isnull(b.num,0)-isnull(c.num,0)-isnull(d.num,0)+isnull(e.num,0)) as 库存
from
基本信息表 a
left join
(select code,sum(num) as num,sum(money)/sum(num) as price from 进货 group by code) b
on
a.code=b.code
left join
(select code,sum(num) as num,sum(money)/sum(num) as price from 退货 group by code) c
on
a.code=c.code
left join
(select code,sum(num) as num,sum(money)/sum(num) as price from 销售 group by code) d
on
a.code=d.code
left join
(select code,sum(num) as num,sum(money)/sum(num) as price from 销退 group by code) e
on
a.code=e.code
order by
a.code
[退数量]=c.num,[平均退价] = c.price,
[销售数量]=d.num,[平均售价]= d.price,
[销售退货数量]= e.num,[平均销退价格] = e.price
[库存] = b.num - c.num - d.num + e.numfrom tablea a
left join (select code,sum(num) as num,avg(price) as price from J group by code) b on a.code = b.code
left join (select code,sum(num) as num,avg(price) as price from T group by code) c on a.code = c.code
left join (select code,sum(num) as num,avg(price) as price from x group by code) d on a.code = d.code
left join (select code,sum(num) as num,avg(price) as price from xt group by code) e on a.code = e.code
[退数量]=isnull(c.num,0),[平均退价] = isnull(c.price,,0)
[销售数量]=isnull(d.num,0),[平均售价]= isnull(d.price,,0)
[销售退货数量]= isnull(e.num,0),[平均销退价格] = isnull(e.price,0)
[库存] = isnull(b.num,0) - isnull(c.num,0) - isnull(d.num,0) + isnull(e.num,0)from tablea a
left join (select code,sum(num) as num,avg(price) as price from J group by code) b on a.code = b.code
left join (select code,sum(num) as num,avg(price) as price from T group by code) c on a.code = c.code
left join (select code,sum(num) as num,avg(price) as price from x group by code) d on a.code = d.code
left join (select code,sum(num) as num,avg(price) as price from xt group by code) e on a.code = e.code
您好,已经基本实现,有一个问题是,00003和00005他不应该出现在最后的总表中。但是他们也出现了,我要怎么不让他们出现呀
a.code as 条码,
a.name as 商品名称,
isnull(b.num ,0) as 进货数量,
isnull(b.price,0) as 平均进价,
isnull(c.num ,0) as 退货数量,
isnull(c.price,0) as 平均退价,
isnull(d.num ,0) as 销售数量,
isnull(d.price,0) as 平均售价,
isnull(e.num ,0) as 销售退货数量,
isnull(e.price,0) as 平均销退价格,
(isnull(b.num,0)-isnull(c.num,0)-isnull(d.num,0)+isnull(e.num,0)) as 库存
from
基本信息表 a
left join
(select code,sum(num) as num,sum(money)/sum(num) as price from 进货 group by code) b
on
a.code=b.code
left join
(select code,sum(num) as num,sum(money)/sum(num) as price from 退货 group by code) c
on
a.code=c.code
left join
(select code,sum(num) as num,sum(money)/sum(num) as price from 销售 group by code) d
on
a.code=d.code
left join
(select code,sum(num) as num,sum(money)/sum(num) as price from 销退 group by code) e
on
a.code=e.code
where
(b.num is not null) or (c.num is not null) or (d.num is not null) or (e.num is not null)
order by
a.code
您好,已经基本实现,有一个问题是,00003和00005他不应该出现在最后的总表中。但是他们也出现了,我要怎么不让他们出现呀
_______________________________________________________________________________________________________如上,对结果集做一个过滤处理
例如
select code,sum(num) as num,sum(money)/sum(num) as price from 进货 group by code中sum(money)/sum(num)这里计算的是什么好像是一次交易后这个产品平均价格,而不是整个在‘进货表’中同类产品的平均价格。你认为??