1.有两个表
表一 AAA
种类mc 库存总量s1
A 997
B 1234
表二 BBB
种类mc 出库数量s1
A 105
A 213
B 116
B 211
B 303
用一条SQL语句求出A,B各剩下多少?
select 种类mc,库存总量s1 -出库数量s1 as 剩余数量 from AAA inner join
(select 种类mc ,sum( 出库数量s1) 出库数量s1 from BBB group 种类mc) CCC
on AAA.种类mc=CCC .种类mc
2.强烈推荐动态纵向转为横向求和
表T_spec
F_ID F_code F_name F_sprice
1 001 电脑 2000
2 002 打印机 300
3 003 扫描仪 200
4 004 内存 200
表T_Books
F_ID F_name F_address F_code F_nums F_totalsprice
1 aaa aaaaaaa 001 10 20000
2 bbb bbbbbbb 002 10 3000
3 ccc ccccccc 003 10 2000
4 aaa aaaaaaa 004 10 2000
5 bbb bbbbbbb 003 20 4000
要按表T_spec中的项目来统计用户购买的合计数,关联字段是F_code,统计结果如下表:
F_name F_address 电脑 打印机 扫描仪 内存 合计 F_totalsprice
aaa aaaaaaa 10 0 0 10 20 22000
bbb bbbbbbb 0 10 20 0 30 7000
ccc ccccccc 0 0 10 0 10 2000
select b.F_ID,b.F_name as [Name],b.F_address,b.F_code,b.F_nums,b.F_totalsprice,a.F_name,a.F_sprice into ## from tb a,tt b where a.F_code=b.F_code order by a.F_code
declare @sql varchar(8000)
set @sql='select Name,F_address'
select @sql=@sql+',['+F_name+']=sum(case F_name when '''+F_name+''' then F_nums else 0 end)' from ## group by F_name
exec(@sql+',sum(F_nums) as 合计,sum(F_totalsprice) as F_totalsprice from ## group by Name,F_address')
drop table ##
3.3