有两个表:申请表 A, 字段是 id ,content ,state(审核级别);审核表 B ,字段是 id ,aId(申请表id), UserId(审核人id), ApproveDate(审核日期)。 A:
id ,content ,state
1 aaa 3
2 b 4
3 c 4 B:
id ,aId, UserId ApproveDate
1 1 王五 2008-9-19 15:09:57
2 2 王五 2008-9-19 15:10:57
3 2 张三 2008-9-19 15:12:57
4 3 王五 2008-9-19 15:8:57
4 3 李四 2008-9-19 15:11:57
找出state是4并且最后审核人不是张三的记录
id ,content ,state
1 aaa 3
2 b 4
3 c 4 B:
id ,aId, UserId ApproveDate
1 1 王五 2008-9-19 15:09:57
2 2 王五 2008-9-19 15:10:57
3 2 张三 2008-9-19 15:12:57
4 3 王五 2008-9-19 15:8:57
4 3 李四 2008-9-19 15:11:57
找出state是4并且最后审核人不是张三的记录
SELECT B.* FROM TA A ,TB B WHERE A.ID=B.AID AND A.STATE=4 AND B.UserId<>'张三'
(select * from b t where ApproveDate = (select max(ApproveDate) from b where aid = t.aid)) m
where a.id = m.aid and a.state = 4 and b.UserId <> '张三'
--如果是的话。就是:
--> 测试数据: @A
declare @A table (id int,content varchar(3),state int)
insert into @A
select 1,'aaa',3 union all
select 2,'b',4 union all
select 3,'c',4
--> 测试数据: @B
declare @B table (id int,aId int,UserId varchar(4),ApproveDate datetime)
insert into @B
select 1,1,'王五','2008-9-19 15:09:57' union all
select 2,2,'王五','2008-9-19 15:10:57' union all
select 3,2,'张三','2008-9-19 15:12:57' union all
select 4,3,'王五','2008-9-19 15:8:57' union all
select 5,3,'李四','2008-9-19 15:11:57'select a.* from @A a,@B b
where a.id=b.aid and a.state=4 and not exists(select 1 from @b where aid=b.aid and approvedate>b.approvedate)
and b.userid<>'张三'
--:结果:id content state
----------- ------- -----------
3 c 4
insert into a values(1 , 'aaa' , 3 )
insert into a values(2 , 'b' , 4 )
insert into a values(3 , 'c' , 4 )
create table b(id int,aId int, UserId varchar(10), ApproveDate datetime)
insert into b values(1 , 1 , '王五' , '2008-9-19 15:09:57')
insert into b values(2 , 2 , '王五' , '2008-9-19 15:10:57')
insert into b values(3 , 2 , '张三' , '2008-9-19 15:12:57')
insert into b values(4 , 3 , '王五' , '2008-9-19 15:8:57')
insert into b values(4 , 3 , '李四' , '2008-9-19 15:11:57')
goselect a.* , m.* from a,
(select * from b t where ApproveDate = (select max(ApproveDate) from b where aid = t.aid)) m
where a.id = m.aid and a.state = 4 and m.UserId <> '张三'drop table a, b/*
id content state id aId UserId ApproveDate
----------- ---------- ----------- ----------- ----------- ---------- ------------------------------------------------------
3 c 4 4 3 李四 2008-09-19 15:11:57.000(所影响的行数为 1 行)
*/
(select id , UserId , ApproveDate .content ,state from B inner join A
on
B.aid=a.id
)aa
where state=4 and UserId !='张三'
select b.* from b,(select aid,max(approvedate) currentdate from (select b.* from a,b where a.id=b.aid and a.state=4) m group by m.aid) n
where b.aid=n.aid and b.userid<>'张三' and b.approvedate=n.currentdate