to:bzszp大虾
序号是指一条记录的主键,用户名称和最后回复人名称这两者都是用户,举个例子就是我发了这个帖子,bzszp大虾最后回复了一条,那么我的用户名就是用户名称,bzszp大虾就是最后回复人了:)多谢帮忙啊~!
序号是指一条记录的主键,用户名称和最后回复人名称这两者都是用户,举个例子就是我发了这个帖子,bzszp大虾最后回复了一条,那么我的用户名就是用户名称,bzszp大虾就是最后回复人了:)多谢帮忙啊~!
tb2中的id 和tb1中的id 对应
from tb3,(select userid,max(date) zcrq from tb1 group by userid) tb,
(select userid,count(*) hfsl,max(rdate) zhhfrq from tb1,tb2 where tb1.id=tb2.id group by userid) t,
(select tb1.userid,tb3.username from tb1,tb2,tb3
where tb3.userid=tb1.userid and tb1.id=tb2.id where (tb1.userid,tb2.rdate) in
(
select tb1.userid,max(tb2.rdate) from tb1,tb2 where tb1.id=tb2.id group by tb1.userid
)) tt
where tb3,userid=tb.userid and tb3.userid=t.userid and tb3.userid=tt.userid;没有测试,你的表结构有点混乱
select tb3,username,tb.zcrq,t.hfsl,t.zhhfrq,tt.username
from tb3,(select userid,max(date) zcrq from tb1 group by userid) tb,
(select userid,count(*) hfsl,max(rdate) zhhfrq from tb1,tb2 where tb1.id=tb2.id group by userid) t,
(select tb1.userid,tb3.username from tb1,tb2,tb3
where tb3.userid=tb1.userid and tb1.id=tb2.id where (tb1.userid,tb2.rdate) in
( |
这里是不是有问题?这个where做什么用的?select tb1.userid,max(tb2.rdate) from tb1,tb2 where tb1.id=tb2.id group by tb1.userid
)) tt
where tb3,userid=tb.userid and tb3.userid=t.userid and tb3.userid=tt.userid;
(select fd_uid,max(fd_date) zcrq from table1 group by fd_uid) tb,
(select table1.fd_uid,count(*) hfsl,max(fd_rdate) zhhfrq from table1,table2 where table1.fd_pid=table2.fd_pid(+) group by table1.fd_uid) t,
(select table1.fd_uid,table3.fd_uname from table1,table2,table3 where table3.fd_uid=table1.fd_uid and table1.fd_pid=table2.fd_pid(+) and (table1.fd_uid,table2.fd_rdate) in(select table1.fd_uid,max(table2.fd_rdate) from table1,table2 where table1.fd_pid=table2.fd_pid(+) group by table1.fd_uid)) tt
where table3.fd_uid=tb.fd_uid and table3.fd_uid=t.fd_uid and table3.fd_uid=tt.fd_uid;
这个是现在可以执行的
(select fd_uid,max(fd_date) zcrq from table1 group by fd_uid) tb,
(select table1.fd_uid,count(table2.fd_pid) hfsl,max(fd_rdate) zhhfrq from table1,table2 where table1.fd_pid=table2.fd_pid(+) group by table1.fd_uid) t,
(select table1.fd_uid,table3.fd_uname from table1,table2,table3 where table3.fd_uid=table1.fd_uid and table1.fd_pid=table2.fd_pid and (table1.fd_uid,table2.fd_rdate) in(select table1.fd_uid,max(table2.fd_rdate) from table1,table2 where table1.fd_pid=table2.fd_pid group by table1.fd_uid)) tt
where table3.fd_uid=tb.fd_uid and table3.fd_uid=t.fd_uid(+) and table3.fd_uid=tt.fd_uid(+);试一下
fd_pid fd_uid fd_date
1 01 2004-9-2
2 02 2004-9-3
3 03 2004-9-4
4 01 2004-9-5 table2
fd_rid fd_pid fd_uid fd_rdate
1 2 01 2004-9-13
2 1 02 2004-9-16
3 1 02 2004-9-9
4 3 01 2004-9-10
5 3 02 2004-9-17table3
fd_uid fd_uname
01 测试1
02 测试2
03 测试3SQL
select table3.fd_uname 人员名称,tb.zcrq 建立日期,t.hfsl 回复数,t.zhhfrq 最后回复日期,tt.fd_uname 最后回复人员名称 from table3,
(select fd_uid,max(fd_date) zcrq from table1 group by fd_uid) tb,
(select table1.fd_uid,count(table2.fd_pid) hfsl,max(fd_rdate) zhhfrq from table1,table2 where table1.fd_pid=table2.fd_pid(+) group by table1.fd_uid) t,
(select table1.fd_uid,table3.fd_uname from table1,table2,table3 where table3.fd_uid=table1.fd_uid and table1.fd_pid=table2.fd_pid and (table1.fd_uid,table2.fd_rdate) in(select table1.fd_uid,max(table2.fd_rdate) from table1,table2 where table1.fd_pid=table2.fd_pid group by table1.fd_uid)) tt
where table3.fd_uid=tb.fd_uid and table3.fd_uid=t.fd_uid(+) and table3.fd_uid=tt.fd_uid(+);结果测试1 2004-9-5 2 2004-9-16 测试1
测试2 2004-9-3 1 2004-9-13 测试2
测试3 2004-9-4 2 2004-9-17 测试3
---------- ---------- -------------------
1 01 2004-09-02 00:00:00
2 02 2004-09-03 00:00:00
3 03 2004-09-04 00:00:00
4 01 2004-09-05 00:00:00已用时间: 00: 00: 00.31
16:19:09 SQL> select * from table2; FD_RID FD_PID FD_UID FD_RDATE
---------- ---------- ---------- -------------------
1 2 01 2004-09-13 00:00:00
2 1 02 2004-09-16 00:00:00
3 1 02 2004-09-09 00:00:00
4 3 01 2004-09-10 00:00:00
5 3 02 2004-09-17 00:00:00已用时间: 00: 00: 00.47
16:19:14 SQL> select * from table3;FD_UID FD_UNAME
---------- ----------
01 测试1
02 测试2
03 测试3已用时间: 00: 00: 00.47
16:19:19 SQL> select table3.fd_uname 人员名称,tb.zcrq 建立日期,t.hfsl 回复数,t.zhhfrq 最后回复日期,
16:19:25 2 tt.fd_uname 最后回复人员名称 from table3,
16:19:25 3 (select fd_uid,max(fd_date) zcrq from table1 group by fd_uid) tb,
16:19:25 4 (select table1.fd_uid,count(table2.fd_pid) hfsl,max(fd_rdate) zhhfrq
16:19:25 5 from table1,table2
16:19:25 6 where table1.fd_pid=table2.fd_pid(+) group by table1.fd_uid) t,
16:19:25 7 (
16:19:25 8 select table1.fd_uid,table3.fd_uname from table1,table2,table3 where
16:19:25 9 table3.fd_uid=table2.fd_uid and table1.fd_pid=table2.fd_pid and
16:19:25 10 (table1.fd_uid,table2.fd_rdate) in
16:19:25 11 (select table1.fd_uid,max(table2.fd_rdate) from
16:19:25 12 table1,table2 where table1.fd_pid=table2.fd_pid group by table1.fd_uid)
16:19:25 13 ) tt
16:19:25 14 where table3.fd_uid=tb.fd_uid and table3.fd_uid=t.fd_uid(+) and table3.fd_uid=tt.fd_ui
d(+); 人员名称 建立日期 回复数 最后回复日期 最后回复人
---------- ------------------- ---------- ------------------- ----------
测试1 2004-09-05 00:00:00 2 2004-09-16 00:00:00 测试2
测试2 2004-09-03 00:00:00 1 2004-09-13 00:00:00 测试1
测试3 2004-09-04 00:00:00 2 2004-09-17 00:00:00 测试2已用时间: 00: 00: 00.32
16:19:27 SQL>
想要得结果是
人员名称 建立日期 回复数 最后回复日期 最后回复人
---------- ------------------- ---------- ------------------- ----------
测试1 2004-09-03 00:00:00 2 2004-09-16 00:00:00 测试2
测试1 2004-09-05 00:00:00 0
测试2 2004-09-03 00:00:00 1 2004-09-13 00:00:00 测试1
测试3 2004-09-04 00:00:00 2 2004-09-17 00:00:00 测试2应该是类似这样的结果
17:17:56 2 decode(hfr,null,'',(select fd_uname from table3 where fd_uid=substr(t.hfr,17))) 最后回
复人员名称
17:17:56 3 from (
17:17:56 4 select fd_uid,fd_date,
17:17:56 5 (select count(1) from table2 where table2.fd_pid=table1.fd_pid) 回复数,
17:17:56 6 (select max(fd_rdate) from table2 where table2.fd_pid=table1.fd_pid) 最后回复日期,
17:17:56 7 (select max(to_char(fd_rdate,'yyyymmddhh24miss')||fd_uid) from table2
17:17:56 8 where table2.fd_pid=table1.fd_pid) hfr
17:17:56 9 from table1) t,table3 tb3
17:17:56 10 where t.fd_uid=tb3.fd_uid(+);FD_UNAME FD_DATE 回复数 最后回复日期 最后回复人
---------- ------------------- ---------- ------------------- ----------
测试1 2004-09-02 00:00:00 2 2004-09-16 00:00:00
测试1 2004-09-05 00:00:00 0
测试2 2004-09-03 00:00:00 1 2004-09-13 00:00:00
测试3 2004-09-04 00:00:00 2 2004-09-17 00:00:00已用时间: 00: 00: 00.47
17:17:57 SQL>
这句是什么意思?
17:32:57 2 decode(hfr,null,'',(select fd_uname from table3 where fd_uid=substr(t.hfr,15)))
17:32:57 3 最后回复人员名称
17:32:57 4 from (
17:32:57 5 select fd_uid,fd_date,
17:32:57 6 (select count(1) from table2 where table2.fd_pid=table1.fd_pid) 回复数,
17:32:57 7 (select max(fd_rdate) from table2 where table2.fd_pid=table1.fd_pid) 最后回复日期
17:32:57 8 (select max(to_char(fd_rdate,'yyyymmddhh24miss')||fd_uid) from table2
17:32:57 9 where table2.fd_pid=table1.fd_pid) hfr
17:32:57 10 from table1) t,table3 tb3
17:32:57 11 where t.fd_uid=tb3.fd_uid(+);FD_UNAME FD_DATE 回复数 最后回复日期 最后回复人
---------- ------------------- ---------- ------------------- ----------
测试1 2004-09-02 00:00:00 2 2004-09-16 00:00:00 测试2
测试1 2004-09-05 00:00:00 0
测试2 2004-09-03 00:00:00 1 2004-09-13 00:00:00 测试1
测试3 2004-09-04 00:00:00 2 2004-09-17 00:00:00 测试2已用时间: 00: 00: 00.16