一个SQL 问题,请大家帮忙

[dabster1]

如下表是一个bom展开的表,我想取最新版本一般(数字/或字母)高的,为新版本了
level item version qty

1 |--SFG001 B 1.00
1 |--SFG002 A 1.00
1 |--SFG002 B 2.00
1 |--SFG003 A 1.00
1 |--SFG003 B 3.00
2 |--WIP001 A 2.00
2 |--WIP001 B 4.00
2 |--WIP002 B 2.00
2 |--WIP003 A 3.00
2 |--WIP003 B 4.00
2 |--WIP003 A 6.00
2 |--WIP003 B 8.00
2 |--WIP004 001 3.00
2 |--WIP004 002 5.00
2 |--WIP004 001 6.00
2 |--WIP004 002 10.00
2 |--WIP005 A 2.00
2 |--WIP005 B 3.00
2 |--WIP005 A 4.00
2 |--WIP005 B 6.00
如我这样写是可以
select level,item,max(version)
from t1
group by level,item

1 |--SFG001 B
1 |--SFG002 B
1 |--SFG003 B
2 |--WIP001 B
2 |--WIP002 B
2 |--WIP003 B
2 |--WIP004 002
2 |--WIP005 B

但是实际上我还需要显示数量,假如Group by 后有QTY,则达不到我想要的效果,刚入门,请帮忙

[liangCK]

create table t
(parent varchar(10),
 child varchar(10),qty numeric(9,2)
)

insert into t
select 'FG001',  'SFG001',     1  union all
select 'FG001' , 'SFG002',     1 union all
select 'FG001'  ,'SFG003',     1 union all
select 'SFG001', 'WIP001',     2 union all
select 'SFG001' ,'WIP002',     2 union all
select 'SFG002' ,'WIP003',     3 union all
select 'SFG002' ,'WIP004',     3 union all
select 'SFG002' ,'WIP005',     2 union all
select 'SFG003' ,'WIP006',     3 union all
select 'WIP001' ,'RAW001',     2.66 union all
select 'WIP001' ,'RAW002'  ,   2.33 union all
select 'WIP002' ,'RAW003'  ,   3.21 union all
select 'WIP003' ,'RAW004'  ,   1.89 union all
select 'WIP003' ,'RAW005'  ,   1.86 union all
select 'RAW001','KKK001',      3.25 union all
select 'RAW004','KKK003',      4.26 union all
select 'KKK001','WWW005',      5.23
 

二:创建函数（a:树型结构显示）

 

 


create function test(@parent VARCHAR(10))
returns @t table(parent Nvarchar(10),child Nvarchar(10),qty numeric(9,2),
                level int,sort Nvarchar(1000)collate Latin1_General_BIN  ) 
as
begin
     declare @level int
     set @level=1
     insert into @t
     select parent,child,qty,@level,parent+child 
     from t
     where parent=@parent collate Latin1_General_BIN 
     while @@rowcount>0
     begin
         set @level=@level+1
         insert @t
         select a.parent,a.child,a.qty*b.qty,@level,b.sort+a.child
         from   t a ,@t b
         where  a.parent=b.child collate Latin1_General_BIN
                and b.level=@level-1   
    end
return
end

--调用函数
select
    level,
    space(level*2)+'|--' + child as 'product',
    qty
from 
    dbo.test('FG001')
order by 
    sort 


--结果
/**//*
level       product                         qty         
1             |--SFG001                     1.00
2               |--WIP001                   2.00
3                 |--RAW001                 5.32
4                   |--KKK001               17.29
5                     |--WWW005             90.43
3                 |--RAW002                 4.66
2               |--WIP002                   2.00
3                 |--RAW003                 6.42
1             |--SFG002                     1.00
2               |--WIP003                   3.00
3                 |--RAW004                 5.67
4                   |--KKK003               24.15
3                 |--RAW005                 5.58
2               |--WIP004                   3.00
2               |--WIP005                   2.00
1             |--SFG003                     1.00
2               |--WIP006                   3.00

(17 row(s) affected)
*/

[dawugui]

select t.* from tb t where version = (select max(version ) from tb where level = t.level and item = t.item )