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 王五
以此类推!
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
) TSELECT * INTO #B FROM (
SELECT 10198 AS BIANHAO, '张三' AS [NAME] UNION ALL
SELECT 10199, '李四' UNION ALL
SELECT 10200, '王五'
)TSELECT #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 NULLDROP TABLE #A
DROP TABLE #B
不知道意思理解对不?
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
) TSELECT * INTO #B FROM (
SELECT 10198 AS BIANHAO, '张三' AS [NAME] UNION ALL
SELECT 10199, '李四' UNION ALL
SELECT 10200, '王五'
)Tselect 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,10200declare @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 王五
*/
where t.bianhao=b.bianhao
and (select max(buzhou_hao) from A表 where bianhao=t.bianhao)=@i-1