急求sql语句

[ajoy]

A表字段：步骤号代表每条记录的审批步骤

buzhou_hao bianhao
1 10198
1 10199
2 10198
3 10198
1 10200
B表

bianhao name
10198 张三
10199 李四
10200 王五

我在页面上选择的值是 buzhou_hao 值为2 现在我需要取出在B表中，buzhou_hao应该已经做完第1步，应该做第2步的记录.
bianhao name
10199 李四
10200 王五




以此类推！

[小童]

SELECT * INTO #A FROM (
	SELECT 1 AS BUZHOU_HAO,10198 AS BIANHAO UNION ALL
	SELECT 1, 10199 UNION ALL
	SELECT 2, 10198 UNION ALL
	SELECT 3, 10198 UNION ALL
	SELECT 1, 10200
) T

SELECT * INTO #B FROM (
	SELECT 10198 AS BIANHAO, '张三' AS [NAME] UNION ALL
	SELECT 10199, '李四' UNION ALL
	SELECT 10200, '王五' 
)T

SELECT #B.BIANHAO,#B.NAME FROM #B
INNER JOIN #A ON #B.BIANHAO = #A.BIANHAO AND #A.BUZHOU_HAO = 1
LEFT JOIN #A A2 ON #B.BIANHAO = A2.BIANHAO AND A2.BUZHOU_HAO = 2
WHERE A2.BUZHOU_HAO IS NULL

DROP TABLE #A
DROP TABLE #B

不知道意思理解对不？

[q806294478]

SELECT * INTO #A FROM (
    SELECT 1 AS BUZHOU_HAO,10198 AS BIANHAO UNION ALL
    SELECT 1, 10199 UNION ALL
    SELECT 2, 10198 UNION ALL
    SELECT 3, 10198 UNION ALL
    SELECT 1, 10200
) T

SELECT * INTO #B FROM (
    SELECT 10198 AS BIANHAO, '张三' AS [NAME] UNION ALL
    SELECT 10199, '李四' UNION ALL
    SELECT 10200, '王五' 
)T

select bianhao,name from #b where bianhao not IN(select bianhao from #a where buzhou_hao<>1)
结果为：
bianhao	name
10199	李四
10200	王五

[叶子]

declare @A表 table (buzhou_hao int,bianhao int)
insert into @A表
select 1,10198 union all
select 1,10199 union all
select 2,10198 union all
select 3,10198 union all
select 1,10200

declare @B表 table (bianhao int,name varchar(4))
insert into @B表
select 10198,'张三' union all
select 10199,'李四' union all
select 10200,'王五'

declare @i int set @i=2 --参数

select b.* from @A表 t,@B表 b 
where t.bianhao=b.bianhao
and (select max(buzhou_hao) from @A表 where bianhao=t.bianhao)=1
/*
bianhao     name
----------- ----
10199       李四
10200       王五
*/

[叶子]

declare @i int set @i=2 --参数

select b.* from A表 t,B表 b 
where t.bianhao=b.bianhao
and (select max(buzhou_hao) from A表 where bianhao=t.bianhao)=@i-1