select c.人员名称,a.建立日期,b.回复数,b.最后回复日期,c.人员名称 as 最后回复名称
from table1 a left join(select 回复人员编号,回复数=count(*),max(回复日期) from table2)b
on a.人员编号=b.回复人员编号
join table2 c
on a.人员编号=c.人员编号
from table1 a left join(select 回复人员编号,回复数=count(*),max(回复日期) from table2)b
on a.人员编号=b.回复人员编号
join table2 c
on a.人员编号=c.人员编号
from table1 a left join(select 回复人员编号,回复数=count(*),max(回复日期) from table2)b
on a.人员编号=b.回复人员编号
join table3 c
on a.人员编号=c.人员编号有点不对劲,table2有点模糊
create table table2 (fd_rid int,fd_pid int,fd_uid int, fd_rdate varchar (10))
create table table3(fd_uid int,fd_uname varchar (10))insert into table1
select 1,1,'2004-01-02' union
select 2,3,'2004-02-01' union
select 3,1,'2004-02-06'
insert into table2
select 1,1,4,'2004-09-10' union
select 2,2,3,'2004-06-01'
insert into table3
select 1,'张三' union
select 2,'李四' union
select 3,'王五' union
select 4,'小六'
select a.fd_uname 人员名称,a.fd_date 建立日期, isnull (b.num,0) as 回复数,
isnull (b.fd_rdate,'无回复') 最后回复日期, isnull (b.fd_uname,'无回复人') 最后回复人员名称
from
(select a.fd_pid,b.fd_uid,b.fd_uname,a.fd_date
from table1 a ,table3 b
where a.fd_uid=b.fd_uid) a full join
(
select a.num,a.fd_rid,a.fd_pid,a.fd_rdate,fd_uname
from
(select a.num,b.*
from
(select a.fd_rid,a.fd_pid,max(fd_rdate) as fd_rdate,count(*) as num
from table2 a
group by fd_rid,fd_pid) a ,table2 b, table3 c
where a.fd_rid=b.fd_rid and a.fd_pid=b.fd_pid and a.fd_rdate=b.fd_rdate
group by b.fd_rid,b.fd_pid,b.fd_uid,b.fd_rdate,a.num )a,table3 b
where a.fd_uid=b.fd_uid ) b
on a.fd_pid=b.fd_pid
人员名称 建立日期 回复数 最后回复日期 最后回复人员名称
------- -------- ----- ---------- --------------
张三 2004-01-02 2 2004-09-10 小六
王五 2004-02-01 1 2004-06-01 王五
张三 2004-02-06 0 无回复 无回复人
create table table2 (fd_rid int,fd_pid int,fd_uid int, fd_rdate varchar (10))
create table table3(fd_uid int,fd_uname varchar (10))insert into table1
select 1,1,'2004-01-02' union
select 2,3,'2004-02-01' union
select 3,1,'2004-02-06'
insert into table2
select 1,1,4,'2004-09-10' union
select 2,2,3,'2004-06-01' union
select 1,1,3,'2004-01-03'
insert into table3
select 1,'张三' union
select 2,'李四' union
select 3,'王五' union
select 4,'小六'
select a.fd_uname 人员名称,a.fd_date 建立日期, isnull (b.num,0) as 回复数,
isnull (b.fd_rdate,'无回复') 最后回复日期, isnull (b.fd_uname,'无回复人') 最后回复人员名称
from
(select a.fd_pid,b.fd_uid,b.fd_uname,a.fd_date
from table1 a ,table3 b
where a.fd_uid=b.fd_uid) a full join
(
select a.num,a.fd_rid,a.fd_pid,a.fd_rdate,fd_uname
from
(select a.num,b.*
from
(select a.fd_rid,a.fd_pid,max(fd_rdate) as fd_rdate,count(*) as num
from table2 a
group by fd_rid,fd_pid) a ,table2 b, table3 c
where a.fd_rid=b.fd_rid and a.fd_pid=b.fd_pid and a.fd_rdate=b.fd_rdate
group by b.fd_rid,b.fd_pid,b.fd_uid,b.fd_rdate,a.num )a,table3 b
where a.fd_uid=b.fd_uid ) b
on a.fd_pid=b.fd_pid
人员名称 建立日期 回复数 最后回复日期 最后回复人员名称
------- -------- ----- ---------- --------------
张三 2004-01-02 2 2004-09-10 小六
王五 2004-02-01 1 2004-06-01 王五
张三 2004-02-06 0 无回复 无回复人
CREATE PROCEDURE pro_select
as
beginselect a.fd_uname 人员名称,a.fd_date 建立日期, isnull (b.num,0) as 回复数,
isnull (b.fd_rdate,'无回复') 最后回复日期, isnull (b.fd_uname,'无回复人') 最后回复人员名称
from
(select a.fd_pid,b.fd_uid,b.fd_uname,a.fd_date
from table1 a ,table3 b
where a.fd_uid=b.fd_uid ) a full join
(
select a.num,a.fd_rid,a.fd_pid,a.fd_rdate,fd_uname
from
(select a.num,b.*
from
(select a.fd_rid,a.fd_pid,max(fd_rdate) as fd_rdate,count(*) as num
from table2 a
group by fd_rid,fd_pid) a ,table2 b, table3 c
where a.fd_rid=b.fd_rid and a.fd_pid=b.fd_pid and a.fd_rdate=b.fd_rdate
group by b.fd_rid,b.fd_pid,b.fd_uid,b.fd_rdate,a.num )a,table3 b
where a.fd_uid=b.fd_uid ) b
on a.fd_pid=b.fd_pid
end
------测试
exec pro_select
as
select 人员名称=c.fd_uname,建立日期=a.fd_date
,回复数=b1.回复数,最后回复日期=b1.fd_rdate
,最后回复人员名称=c1.fd_uname
from TABLE1 a
left join TABLE3 c on a.fd_uid=c.fd_uid
left join(
select fd_pid,回复数=count(*),fd_rdate=max(fd_rdate)
from TABLE2
group by fd_pid
)b1 on a.fd_pid=b.fd_pid
left join
TABLE2 b2 on b1.fd_pid=b2.fd_pid and b1.fd_rdate=b2.fd_rdate
left join TABLE3 c1 on c1.fd_uid=b2.fd_uid
select (select fd_uname from TABLE3 where TABLE3.fd_uid =TABLE1.fd_uid) 人员名称,fd_date 建立日期,
(select count(*) from TABLE2 where TABLE2.fd_pid=TABLE1.fd_pid) 回复数,
isnull((select max(fd_rdate) from TABLE2 where TABLE2.fd_pid=TABLE1.fd_pid),'没有回复日期') 最后回复日期,
((select fd_uname from (select fd_pid,(select fd_uname from TABLE3 where TABLE3.fd_uid =TABLE2.fd_uid) fd_uname,fd_rdate from TABLE2 )
where fd_pid=TABLE1.fd_pid and fd_rdate=(select max(fd_rdate) from TABLE2 where TABLE2.fd_pid=TABLE1.fd_pid)),'没有回复人') 最后回复人员名称 from TABLE1
select (select fd_uname from TABLE3 where TABLE3.fd_uid =TABLE1.fd_uid) 人员名称,fd_date 建立日期,
(select count(*) from TABLE2 where TABLE2.fd_pid=TABLE1.fd_pid) 回复数,
isnull((select max(fd_rdate) from TABLE2 where TABLE2.fd_pid=TABLE1.fd_pid),'没有回复日期') 最后回复日期,
isnull((select fd_uname from (select fd_pid,(select fd_uname from TABLE3 where TABLE3.fd_uid =TABLE2.fd_uid) fd_uname,fd_rdate from TABLE2 )
where fd_pid=TABLE1.fd_pid and fd_rdate=(select max(fd_rdate) from TABLE2 where TABLE2.fd_pid=TABLE1.fd_pid)),'没有回复人') 最后回复人员名称 from TABLE1