不知道意思理解对不？
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
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
