是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语句
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语句
解决方案 »
- MySQL中有没有类似@@ROWCOUNT的系统变量(存储过程)
- mysql启动后台服务时占用磁盘使用率100% 如何解决?
- 这个关系模式应该怎样拆分?
- 关于 left join 关联的问题
- mysql命令问题
- federeate引擎大表创建成功不能出数据
- 在mysql命令行执行的命令几个小时后才生效,怎样即时更新数据库呢?
- 求救:mysql数据库还原问题
- 有2个表 [color=#FF0000]lasttime_table[/color](登录时间表) [color=#FF00FF]regtime_table[/
- sql问题,行转列
- mysql备份中含有自定义函数无法恢复的问题
- 请求各位大哥帮忙解决二张表查询?
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语句
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
`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);
+-----+------+-------+------------+---------+--------+
| 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: 0mysql> select * from z_b;
+-----+--------------+------------+---------+
| tid | lastposttime | lastposter | replies |
+-----+--------------+------------+---------+
| 1 | 1280000005 | user6 | 6 |
| 2 | 1280000009 | user10 | 4 |
+-----+--------------+------------+---------+
2 rows in set (0.02 sec)mysql>
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);
= 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);
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楼比哪个性能好点?