代码的意思如下:--Case when t1.[NO] = 1 Then
/*------------------------------------------------------------------------------------------------*/
Select t1.* From (
Select b.Zcsqid,b.Zcsquid,b.RTableID,b.Deptid,b.Userid,b.RMoney,b.Usefor,b.Sqdate,b.IsSh,b.Demo,
a.DutyId,a.RoleId,a.SprId,a.SpDate,a.SpResult,a.[No]
--b.Usefor,b.RMoney,b.IsSH
From SplcInfo a Left Join ZcsqInfo b On a.ZcsqID = b.ZcsqID
Where b.IsSH ='审批中'
and a.RoleID in (Select RoleID From UserRoleInfo Where UserID ='lihou' )
and a.SprID is Null and a.SpResult='未审批'
and b.DeptID in (Select DeptID From RoleAuditingPower Where RoleID ='lihou')
) t1
/*------------------------------------------------------------------------------------------------*/
-- Else t1.[NO]不等于1时
/*------------------------------------------------------------------------------------------------*/
Select t1.* From (
Select b.Zcsqid,b.Zcsquid,b.RTableID,b.Deptid,b.Userid,b.RMoney,b.Usefor,b.Sqdate,b.IsSh,b.Demo,
a.DutyId,a.RoleId,a.SprId,a.SpDate,a.SpResult,a.[No]
--b.Usefor,b.RMoney,b.IsSH
From SplcInfo a Left Join ZcsqInfo b On a.ZcsqID = b.ZcsqID
Where b.IsSH ='审批中'
and a.RoleID in (Select RoleID From UserRoleInfo Where UserID ='lihou' )
and a.SprID is Null and a.SpResult='未审批'
and b.DeptID in (Select DeptID From RoleAuditingPower Where RoleID ='lihou')
) t1
//下面这一段是较上面多出来的
Where
exists (Select 1 From SplcInfo c
Where
c.ZcsqID = t1.ZcsqID
and c.[NO] = t1.[NO]-1
and c.SpResult='审批通过'
)
/*------------------------------------------------------------------------------------------------*/
--End
如上所示内容,要求用一个SQL语句,完成,或是存储过程完成,lihou这个值作为输入值
/*------------------------------------------------------------------------------------------------*/
Select t1.* From (
Select b.Zcsqid,b.Zcsquid,b.RTableID,b.Deptid,b.Userid,b.RMoney,b.Usefor,b.Sqdate,b.IsSh,b.Demo,
a.DutyId,a.RoleId,a.SprId,a.SpDate,a.SpResult,a.[No]
--b.Usefor,b.RMoney,b.IsSH
From SplcInfo a Left Join ZcsqInfo b On a.ZcsqID = b.ZcsqID
Where b.IsSH ='审批中'
and a.RoleID in (Select RoleID From UserRoleInfo Where UserID ='lihou' )
and a.SprID is Null and a.SpResult='未审批'
and b.DeptID in (Select DeptID From RoleAuditingPower Where RoleID ='lihou')
) t1
/*------------------------------------------------------------------------------------------------*/
-- Else t1.[NO]不等于1时
/*------------------------------------------------------------------------------------------------*/
Select t1.* From (
Select b.Zcsqid,b.Zcsquid,b.RTableID,b.Deptid,b.Userid,b.RMoney,b.Usefor,b.Sqdate,b.IsSh,b.Demo,
a.DutyId,a.RoleId,a.SprId,a.SpDate,a.SpResult,a.[No]
--b.Usefor,b.RMoney,b.IsSH
From SplcInfo a Left Join ZcsqInfo b On a.ZcsqID = b.ZcsqID
Where b.IsSH ='审批中'
and a.RoleID in (Select RoleID From UserRoleInfo Where UserID ='lihou' )
and a.SprID is Null and a.SpResult='未审批'
and b.DeptID in (Select DeptID From RoleAuditingPower Where RoleID ='lihou')
) t1
//下面这一段是较上面多出来的
Where
exists (Select 1 From SplcInfo c
Where
c.ZcsqID = t1.ZcsqID
and c.[NO] = t1.[NO]-1
and c.SpResult='审批通过'
)
/*------------------------------------------------------------------------------------------------*/
--End
如上所示内容,要求用一个SQL语句,完成,或是存储过程完成,lihou这个值作为输入值
Select t1.* From (
Select b.Zcsqid,b.Zcsquid,b.RTableID,b.Deptid,b.Userid,b.RMoney,b.Usefor,b.Sqdate,b.IsSh,b.Demo,
a.DutyId,a.RoleId,a.SprId,a.SpDate,a.SpResult,a.[No]
--b.Usefor,b.RMoney,b.IsSH
From SplcInfo a Left Join ZcsqInfo b On a.ZcsqID = b.ZcsqID
Where b.IsSH ='审批中'
and a.RoleID in (Select RoleID From UserRoleInfo Where UserID ='lihou' )
and a.SprID is Null and a.SpResult='未审批'
and b.DeptID in (Select DeptID From RoleAuditingPower Where RoleID ='lihou')
) t1
Where
exists (Select 1 From SplcInfo c
Where (t1.[NO] <> 1 and
c.ZcsqID = t1.ZcsqID
and c.[NO] = t1.[NO]-1
and c.SpResult='审批通过')
or (t1.[NO] = 1 and 1 = 1)
)
declare @no int
set @no = isnull(@no , 0)
select @no = t1.[NO] from t1
if(@no = 1)
begin
Select t1.* From (
Select b.Zcsqid,b.Zcsquid,b.RTableID,b.Deptid,b.Userid,b.RMoney,b.Usefor,b.Sqdate,b.IsSh,b.Demo,
a.DutyId,a.RoleId,a.SprId,a.SpDate,a.SpResult,a.[No]
--b.Usefor,b.RMoney,b.IsSH
From SplcInfo a Left Join ZcsqInfo b On a.ZcsqID = b.ZcsqID
Where b.IsSH ='审批中'
and a.RoleID in (Select RoleID From UserRoleInfo Where UserID ='lihou' )
and a.SprID is Null and a.SpResult='未审批'
and b.DeptID in (Select DeptID From RoleAuditingPower Where RoleID ='lihou')
) t1
end
if(@no <> 1)
begin
Select t1.* From (
Select b.Zcsqid,b.Zcsquid,b.RTableID,b.Deptid,b.Userid,b.RMoney,b.Usefor,b.Sqdate,b.IsSh,b.Demo,
a.DutyId,a.RoleId,a.SprId,a.SpDate,a.SpResult,a.[No]
--b.Usefor,b.RMoney,b.IsSH
From SplcInfo a Left Join ZcsqInfo b On a.ZcsqID = b.ZcsqID
Where b.IsSH ='审批中'
and a.RoleID in (Select RoleID From UserRoleInfo Where UserID ='lihou' )
and a.SprID is Null and a.SpResult='未审批'
and b.DeptID in (Select DeptID From RoleAuditingPower Where RoleID ='lihou')
) t1
//下面这一段是较上面多出来的
Where
exists (Select 1 From SplcInfo c
Where
c.ZcsqID = t1.ZcsqID
and c.[NO] = t1.[NO]-1
and c.SpResult='审批通过'
)end