create table t(MaterialId nvarchar(10),Material char(1),Consume bit)insert into t select 'THCB1','A','0' union all select 'THCB1','B','1' union all select 'THEA1','B','0' union all select 'THEA1','C','1' union all select 'THA41','C','1' union all select 'THA41','D','0' union all select 'THDE1','D','1' union all select 'THDE1','E','1'go create function f_test(@MaterialId nvarchar(10)) returns @rr table (Material char(1)) as begin declare @tb table (MaterialId nvarchar(10),Material char(1)) declare @tm nvarchar(10) set @tm=@MaterialId insert into @tb select MaterialId,Material from t a where MaterialId=@tm and exists(select 1 from t where MaterialId=a.MaterialId and Consume<>a.Consume) while exists(select 1 from t a where MaterialId not in (select MaterialId from @tb) and Material in(select Material from @tb) and exists(select 1 from t where MaterialId=a.MaterialId and Consume<>a.Consume)) begin select top 1 @tm=t.MaterialId from t where MaterialId not in (select MaterialId from @tb) and Material in(select Material from @tb) insert into @tb select MaterialId,Material from t a where MaterialId=@tm and exists(select 1 from t where MaterialId=a.MaterialId and Consume<>a.Consume) end insert into @rr select distinct Material from @tb where Material>=(select min(Material) from t where MaterialId=@MaterialId) return end goselect * from dbo.f_test('THCB1') select * from dbo.f_test('THA41') drop function f_test drop table t
回复人: chichunhua(無愧與心) ( ) 信誉:100 你说对的,应该是A,B,C,D
哥哥,可以把上面的语句换成 select * from ……………. 我只要能选出来就好了,大恩不德永不忘!谢谢
union all select 'THCB1','B','1'
union all select 'THEA1','B','0'
union all select 'THEA1','C','1'
union all select 'THA41','C','1'
union all select 'THA41','D','0'
union all select 'THDE1','D','1'
union all select 'THDE1','E','1'go
create function f_test(@MaterialId nvarchar(10))
returns @rr table (Material char(1))
as
begin
declare @tb table (MaterialId nvarchar(10),Material char(1))
declare @tm nvarchar(10)
set @tm=@MaterialId
insert into @tb select MaterialId,Material from t a
where MaterialId=@tm
and exists(select 1 from t where MaterialId=a.MaterialId and Consume<>a.Consume)
while exists(select 1 from t a where MaterialId not in (select MaterialId from @tb) and Material in(select Material from @tb)
and exists(select 1 from t where MaterialId=a.MaterialId and Consume<>a.Consume))
begin
select top 1 @tm=t.MaterialId from t where MaterialId not in (select MaterialId from @tb) and Material in(select Material from @tb)
insert into @tb select MaterialId,Material from t a
where MaterialId=@tm
and exists(select 1 from t where MaterialId=a.MaterialId and Consume<>a.Consume)
end
insert into @rr select distinct Material from @tb where Material>=(select min(Material) from t where MaterialId=@MaterialId)
return
end
goselect * from dbo.f_test('THCB1') select * from dbo.f_test('THA41') drop function f_test
drop table t
select * from …………….
我只要能选出来就好了,大恩不德永不忘!谢谢