求更新统计的sql语句

[daodaoyu222]

是discuz的主贴表和回复表 将贴子的回复数量，最后回复时间，最后回复的用户 更新到贴子主表

如有 回复表A 数据
pid tid first posttime message poster
1 1 1 1280000000 x user1
2 1 0 1280000001 x user2
3 1 0 1280000002 x user3
4 1 0 1280000003 x user4
5 1 0 1280000004 x user5
6 1 0 1280000005 x user6
7 2 1 1280000006 x user7
8 2 0 1280000007 x user8
9 2 0 1280000008 x user9
10 2 0 1280000009 x user10


first=0的为回复

主贴表 数据
tid lastposttime lastposter replies（回复数量）
1 0 0 0
2 0 0 0


执行更新统计后得到以下数据
tid lastposttime lastposter replies
1 1280000005 user6 5
2 1280000009 user10 3


求这样的更新统计的 的sql语句

[daodaoyu222]

上面的不太好看清啊，我再发一下看看这样能不能看得清楚些。

是discuz的主贴表和回复表    将贴子的回复数量，最后回复时间，最后回复的用户  更新到贴子主表

如有  回复表A  数据
pid	tid	first	posttime	       message     poster
1	1	1	1280000000	x	user1
2	1	0	1280000001	x	user2
3	1	0	1280000002	x	user3
4	1	0	1280000003	x	user4
5	1	0	1280000004	x	user5
6	1	0	1280000005	x	user6
7	2	1	1280000006	x	user7
8	2	0	1280000007	x	user8
9	2	0	1280000008	x	user9
10	2	0	1280000009	x	user10


first=0的为回复

主贴表 数据  
tid	lastposttime	lastposter         replies（回复数量）
1	0	           0                 0
2	0	           0                 0


执行更新统计后得到以下数据
tid	lastposttime	lastposter	replies
1	1280000005	user6	          5
2	1280000009	user10	          3


求这样的更新统计的  的sql语句  

[ACMAIN_CHM]

最好你能直接给出create table / insert into 语句，这样别人可以直接在机器上测试并找到你需要的方案。

[rucypli]

select C.tid,D.posttime,D.poster,C.num
from 
(
select A.tid,count(*)-1 as num
from A,B
where A.tid = B.tid
group by A.tid
)C,(
select A1.tid,A1.posttime,A1.poster
from A A1
where not exits (select 1 from A A2 where A1.tid=A2.tid and A1.posttime<B.posttime)
)D
where C.tid = D.tid

[daodaoyu222]

[Quote=引用 2 楼 acmain_chm 的回复:]
最好你能直接给出create table / insert into 语句，这样别人可以直接在机器上测试并找到你需要的方案。
[/Quote] 说得不错，我以后一定注意了。

CREATE TABLE `z_a` (
`pit` int(10) unsigned NOT NULL auto_increment,
`tid` int(10) default NULL,
`first` int(10) default NULL,
`posttime` int(10) default NULL,
`message` varchar(255) default NULL,
`poster` varchar(255) default NULL,
PRIMARY KEY (`pit`),
KEY `a` (`first`),
KEY `b` (`posttime`),
KEY `c` (`message`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;



insert into `z_a`(`pit`,`tid`,`first`,`posttime`,`message`,`poster`) values (1,1,1,1280000000,'x','user1'),(2,1,0,1280000001,'x','user2'),(3,1,0,1280000002,'x','user3'),(4,1,0,1280000003,'x','user4'),(5,1,0,1280000004,'x','user5'),(6,1,0,1280000005,'x','user6'),(7,2,1,1280000006,'x','user7'),(8,2,0,1280000007,'x','user8'),(9,2,0,1280000008,'x','user9'),(10,2,0,1280000009,'x','user10');





CREATE TABLE `z_b` (
`tid` int(10) unsigned NOT NULL auto_increment,
`lastposttime` int(10) default NULL,
`lastposter` varchar(255) default NULL,
`replies` int(10) default NULL,
PRIMARY KEY (`tid`),
KEY `b` (`lastposttime`),
KEY `c` (`lastposter`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;



insert into `z_b`(`tid`,`lastposttime`,`lastposter`,`replies`) values (1,0,'',0),(2,0,'',0);

[ACMAIN_CHM]

mysql> select * from z_a;
+-----+------+-------+------------+---------+--------+
| pit | tid  | first | posttime   | message | poster |
+-----+------+-------+------------+---------+--------+
|   1 |    1 |     1 | 1280000000 | x       | user1  |
|   2 |    1 |     0 | 1280000001 | x       | user2  |
|   3 |    1 |     0 | 1280000002 | x       | user3  |
|   4 |    1 |     0 | 1280000003 | x       | user4  |
|   5 |    1 |     0 | 1280000004 | x       | user5  |
|   6 |    1 |     0 | 1280000005 | x       | user6  |
|   7 |    2 |     1 | 1280000006 | x       | user7  |
|   8 |    2 |     0 | 1280000007 | x       | user8  |
|   9 |    2 |     0 | 1280000008 | x       | user9  |
|  10 |    2 |     0 | 1280000009 | x       | user10 |
+-----+------+-------+------------+---------+--------+
10 rows in set (0.00 sec)

mysql> select * from z_b;
+-----+--------------+------------+---------+
| tid | lastposttime | lastposter | replies |
+-----+--------------+------------+---------+
|   1 |            0 |            |       0 |
|   2 |            0 |            |       0 |
+-----+--------------+------------+---------+
2 rows in set (0.00 sec)

mysql> update z_b
    -> set lastposttime=(select max(posttime) from z_a where tid=z_b.tid),
    ->  lastposter=(select poster from z_a where tid=z_b.tid and posttime=(select max(posttime) from z_a where tid=z_b.tid)),
    ->  replies=(select count(*) from z_a where tid=z_b.tid);
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from z_b;
+-----+--------------+------------+---------+
| tid | lastposttime | lastposter | replies |
+-----+--------------+------------+---------+
|   1 |   1280000005 | user6      |       6 |
|   2 |   1280000009 | user10     |       4 |
+-----+--------------+------------+---------+
2 rows in set (0.02 sec)

mysql>

[ACMAIN_CHM]

update z_b
set lastposttime=(Select max(posttime) From z_a where tid=z_b.tid),
	lastposter=(Select poster from z_a where tid=z_b.tid and posttime=(select max(posttime) from z_a where tid=z_b.tid)),
	replies=(Select count(*) from z_a where tid=z_b.tid);

[zmjsg]

呵呵。。。

update z_b as d inner join (select a.tid,a.pit,a.posttime,a.poster, b.cnt from z_a as a ,(select max(pit) as pit,count(*) as cnt from z_a where first=0 group by tid) as b  where a.pit=b.pit) as c on(d.tid=c.tid) set lastposter=c.poster,lastposttime=c.posttime,replies=c.cnt 

不知道与6楼比哪个性能好点？

[cfd406635982]

跟着楼上学习一下。

mysql> update z_b set lastposttime=(select max(posttime) from z_a where z_b.tid
= z_a.tid),lastposter=(select poster from z_a where z_b.tid= z_a.tid order by po
sttime desc limit 0,1),replies=(select count(*) from z_a where z_b.tid= z_a.tid
group by tid);