单据编号 审核层级 用户ID 审核状态
Bill-08100001 1 8002 0
Bill-08100001 1 8003 0
Bill-08100001 2 8004 0
Bill-08100001 2 8005 0
Bill-08100001 3 8006 0
Bill-08100001 3 8007 0
Bill-08100001 3 8008 0
Bill-08100001 4 8009 0
Bill-08100001 4 8010 0
Bill-08100001 4 8011 0
........如上数据
说明:审核层级无限制,每一层需要多用户审批,8002...8011为用户ID,审核状态默认为0,审核通过后状态修改为1要求:用户如8005登录系统后如何检测出有8005需要审批的单据,有则提示,此时8005提示的前提是8002、8003的审核状态都为1
如何用SQL把8005需要审批的记录查出来
Bill-08100001 1 8002 0
Bill-08100001 1 8003 0
Bill-08100001 2 8004 0
Bill-08100001 2 8005 0
Bill-08100001 3 8006 0
Bill-08100001 3 8007 0
Bill-08100001 3 8008 0
Bill-08100001 4 8009 0
Bill-08100001 4 8010 0
Bill-08100001 4 8011 0
........如上数据
说明:审核层级无限制,每一层需要多用户审批,8002...8011为用户ID,审核状态默认为0,审核通过后状态修改为1要求:用户如8005登录系统后如何检测出有8005需要审批的单据,有则提示,此时8005提示的前提是8002、8003的审核状态都为1
如何用SQL把8005需要审批的记录查出来
select * from tb a where exists(select 1 from tb where (单据编号=a.单据编号 and 用户ID=8002 and
审核状态 = 1) AND (单据编号=a.单据编号 and 用户ID=8003 and
审核状态 = 1) )AND 用户ID=8005 AND 审核状态 = 0
select * from tb a where exists(select 1 from tb where 单据编号=a.单据编号 and 用户ID=8002 and
审核状态 = 1) AND exists(select 1 from tb where 单据编号=a.单据编号 and 用户ID=8003 and
审核状态 = 1)
AND 用户ID=8005
AND 审核状态 = 0
where
a.用户id = b.id and a.用户id=8005 b.审核层级<a.审核层级 and b.审核状态=1
from tb a
where userid=8005
and state=0
and exists (select 1 from tb b
where b.userid=8002
and state=1
and number=a.number)
and exists (select 1 from tb b
where b.userid=8003
and state=1
and number=a.number)
from tb a
where userid=8005
and state=0
and not exists (select 1 from tb b
where b.审核层级<a.审核层级
and state=0
and number=a.number)
from tb a
where userid=8005
and state=0
and not exists (select 1 from tb b
where b.审核层级<a.审核层级
and state=0
and number=a.number)这个SQL当前面的审核状态都为0,userid=8009时,也查询出记录了,应该是8009所处第三层,需要第一层与第二层的审核状态都为1了,才查询出记录来
where 用户ID=8005 and 审核状态=0
and not exists (select 1 from tb where 审核层级=a.审核层级-1 and 审核状态=0)
where 用户ID=8005 and 审核状态=0
and not exists (select 1 from tb where 单据编号=a.单据编号 and 审核层级<a.审核层级 and 审核状态=0)
declare @table table (id varchar(20),cc char,userid varchar(4),status char)
insert @table select 'Bill-08100001', '1', '8002', '0'
insert @table select 'Bill-08100002', '1', '8003' ,'0'
insert @table select 'Bill-08100003', '1', '8003' ,'1'
insert @table select 'Bill-08100004', '2', '8002', '1'
insert @table select 'Bill-08100005' ,'2', '8005', '0'
insert @table select 'Bill-08100006' ,'3', '8006', '0'select id from @table where id in (select id from @table where userid in ('8003','8002') and status = '1')
select * from tb a where 用户ID=@id and 审核状态 = 0 and not exists
(select 1 from tb where 单据编号=a.单据编号 and 审核层级<a.审核层级 and 审核状态 = 0)
where 用户ID=8005 and 审核状态=0
and not exists (select 1 from tb where 单据编号=a.单据编号 and 审核层级<a.审核层级 and 审核状态=0)
这个好像可以了,还有其它好方法吗?一天内,没有就结帐了
select * from tb a,tb b
where a.用户ID=8005 and a.审核状态=0
and a.单据编号=b.单据编号 and b.审核层级<a.审核层级 and b.审核状态=1
select 1 from tb a
where 单据编号='Bill-08100001'
and 审核层级=(select max(审核层级) from tb a0 where a0.单据编号=a.单据编号)
and 审核状态=0
) begin
print 'All processed.'
end
else begin
print 'This need to be process:'
select * from tb a
where 单据编号='Bill-08100001'
and 审核层级=(select max(审核层级) from tb a0 where a0.单据编号=a.单据编号)
and 审核状态=0
end