商品表(Ware) 库存表(Stock) 查询后得到表:
ID Name ParentID SeedCount | ID Number | ID Name Stock
1 Cpu 0 2 | 2 10 | 1 Cpu 40//10+30
2 Cpu1.1 1 0 | 2 20 2 Cpu1.1 30
3 Cup1.2 1 0 3 10 3 Cpu1.2 10
4 内存 0 3 5 30 4 内存 80//30+0+50
5 内存64 4 0 8 20 5 内存64 30
6 内存128 4 0 9 30 6 内存128 0
7 内存256 4 2 7 内存256 50 //20+30
8 内存256D 7 0 8 内存256D 20
9 内存256E 7 0 9 内存256E 30就是商品可以分类,进出库时只是SeedCount = 0的商品可以进出库
然后计算他们库存,不知道怎么计算大类的库存
表达能力有限,不知你们明白不明白.
大家帮帮忙,小弟在此谢谢了
ID Name ParentID SeedCount | ID Number | ID Name Stock
1 Cpu 0 2 | 2 10 | 1 Cpu 40//10+30
2 Cpu1.1 1 0 | 2 20 2 Cpu1.1 30
3 Cup1.2 1 0 3 10 3 Cpu1.2 10
4 内存 0 3 5 30 4 内存 80//30+0+50
5 内存64 4 0 8 20 5 内存64 30
6 内存128 4 0 9 30 6 内存128 0
7 内存256 4 2 7 内存256 50 //20+30
8 内存256D 7 0 8 内存256D 20
9 内存256E 7 0 9 内存256E 30就是商品可以分类,进出库时只是SeedCount = 0的商品可以进出库
然后计算他们库存,不知道怎么计算大类的库存
表达能力有限,不知你们明白不明白.
大家帮帮忙,小弟在此谢谢了
解决方案 »
- 求助谁有glscene的入门级教程啊??
- 那位用Delphi开发过短信客户端程序请进来给小弟提个建议,谢谢了
- 怎么vb中算的对数结果和delphi中的不一样?
- 记录定位问题,在线急等
- 怎样在打印的时候让一条记录打印一页?
- 如何强制终止一个线程函数中为死循环且没有退出代码的线程,而不管线程是否活动?
- 如何将jpg的图片加入到sql server 的 image类型的字段中阿,使用ado
- asstring和asinteger都是干什么用的?
- 一个关于select 的问题!
- 急杀人的问题没有解决!!adodatadet的DELETE能用吗?
- 【请教】Delphi如何同时连接两个数据库?
- DBGrid的列名Column问题
ID Name ParentID SeedCount| ID Number| ID Name Stock
1 Cpu 0 2 | 2 10 | 1 Cpu 40//10+30
2 Cpu1.1 1 0 | 2 20 2 Cpu1.1 30
3 Cup1.2 1 0 3 10 3 Cpu1.2 10
4 内存 0 3 5 30 4 内存 80//30+0+50
5 内存64 4 0 8 20 5 内存64 30
6 内存128 4 0 9 30 6 内存128 0
7 内存256 4 2 7 内存256 50 //20+30
8 内存256D 7 0 8 内存256D 20
9 内存256E 7 0 9 内存256E 30
CPU
/ \
/ \
C1.2 C1.4
/
/
C1.21C1.21 10个
C1.4 20个
查询得到表:
CPU 30
C1.2 10
C1.21 10
C1.4 20
C1.2 C1.4 C1.21 都是什么东西,数据库里看不到呀
40//10+30
30
10
80//30+0+50
30
0
50 //20+30
20
30
什么时候该加,什么时候不加?40//10+30 10和30分别来自那里?
create function GetStockValue(@id int)
Returns numeric
as
begin
declare @result int
declare @Temresult int
declare @Cusor_id int
Set @result = 0
set @Temresult = 0
if exists(select a.id from 商品表 a,库存表 b where a.ParentID = @id)
begin
declare Query_Cusor cursor for
select a.id from 商品表 a where a.ParentID = @id for read only
open Query_Cusor
fetch Query_Cusor into @Cusor_id
while @@fetch_Status=0
begin
--递归调用
select @Temresult= dbo.GetStockValue(a.id) from 商品表 a,库存表 b where a.id=b.id and a.id = @Cusor_id
select @result = @result + @Temresult
fetch Query_Cusor into @Cusor_id
end
close Query_Cusor
deallocate Query_Cusor
end
else
select @result=sum(isnull(b.number,0)) from 商品表 a,库存表 b where a.id=b.id and a.id = @id group by a.id
return @result
end
select id,name,dbo.GetStockValue(id) as Stock from 商品表
Returns numeric
as
begin
declare @result int
declare @Temresult int
declare @Cusor_id int
declare @Sqlstr varchar(8000)
set @result = 0
if exists(select a.id from 商品表 a where a.ParentID = @id)
begin
declare Query_Cusor cursor for
select a.id from 商品表 a where a.ParentID = @id for read only
open Query_Cusor
fetch Query_Cusor into @Cusor_id
while @@fetch_Status=0
begin
--递归调用
set @Temresult = 0
if exists(select a.id from 商品表 a where a.ParentID = @Cusor_id) --是否还有下级节点,局限查两个子节点
begin
select @Temresult= sum(isnull(b.number,0)) from 商品表 a,库存表 b where a.id=b.id and a.id in (select a.id from 商品表 a where a.ParentID = @Cusor_id)
if @Temresult is null
select @Temresult = 0 --null+100=null,加判断
select @result = @result + @Temresult
end
else
begin
select @Temresult= sum(isnull(b.number,0)) from 商品表 a,库存表 b where a.id=b.id and a.id = @Cusor_id
if @Temresult is null
select @Temresult = 0
select @result = @result + @Temresult
end
fetch Query_Cusor into @Cusor_id
end
close Query_Cusor
deallocate Query_Cusor
end
else
begin
select @result=sum(isnull(b.number,0)) from 商品表 a,库存表 b where a.id=b.id and a.id = @id
end
if @result is null
select @result = 0
return @resultend
select id,name,dbo.GetStockValue(id) as Stock from 商品表
--Returns numeric
as
begin
declare @result int
declare @Temresult int
declare @sql varchar(8000)
declare @sqlstr nvarchar(2000)
declare @count numeric
declare @compcount numeric
declare @Cusor_id int
set @result = 0
set @Temresult = 0
Set @count = 0
set @compcount = 0
set @sql = null
set @sqlstr = null
select @sql = 'select a.id from 商品表 a where a.ParentID = '+cast(@id as varchar(20))
if exists(select a.id from 商品表 a where a.ParentID = @id)
begin
Execute(@sql)
select @count = @@ROWCOUNT
while @count > 0
begin
select @sql = @sql + ' and a.ParentID in ('+@sql+')'
Execute(@sql)
select @compcount = @@ROWCOUNT
if @compcount > @count
select @count = @compcount
else
break
end
print @sql
select @sqlstr='select @Temresult = sum(isnull(b.number,0)) from 商品表 a,库存表 b where a.id=b.id and a.id in ( '+ @sql +')'
exec sp_executesql @sqlstr ,N'@Temresult int output',@Temresult output
select @result = @result + @Temresult--exec('select sum(isnull(b.number,0)) from 商品表 a,库存表 b where a.id=b.id and a.id in ( '+ @sql +')' )
end
else
select @result=sum(isnull(b.number,0)) from 商品表 a,库存表 b where a.id=b.id and a.id = @id group by a.id
return @result
end/*测试
declare @result int
execute @result=GetStockValue1 4
print @result结果80
*/select id,name,dbo.GetStockValue1(id) as Stock from 商品表select a.id from 商品表 a where a.ParentID = 4
select a.id from 商品表 a where a.ParentID = 7
select a.id from 商品表 a where a.ParentID = 4
naner_china(naner) 了