楼主显示最新的10条 是帖子和每个帖子最新回复混合在一起的?那就去帖子最新十条 回复最新十条 和在一起取最新十条select tex,t from ( select A.帖子主题 as tex,A.时间 as t from 帖子表 A order by id desc limit 10 union all select B.回复内容 as tex,B.回复时间 as t from 回复表 B where not exists (select 1 from 回复表 C where B.帖子id=C.帖子id and B.回复时间<C.回复时间) order by B.id desc limit 10 )T order by t desc limit 10
create table contents( id int not null primary key auto_increment name varchar(10) not null default '' content mediumtext not null title varchar(20) not null default '' time int unsigned not null );insert into contents(id,name,content,title,time) values('1','test1','test1','test1','1');insert into contents(id,name,content,title,time) values('2','test2','test2','test2','2');insert into contents(id,name,content,title,time) values('3','test3','test3','test3','3'); create table reply( id int not null primary key auto_increment reply_id int not null name varchar(10) not null default '' content mediumtext not null time int unsigned not null );insert into reply(id,reply_id,name,content,time) values('1','3','reply2','reply2','4');insert into reply(id,reply_id,name,content,time) values('2','2','reply1','reply1','5');insert into reply(id,reply_id,name,content,time) values('3','3','reply3','reply3','6');想得到的结果是contents中这样的记录:id name content title time 3 test3 test3 test3 3 2 test2 test2 test2 2 1 test1 test1 test1 1同时,我还要得到如下的关于time字段的查询结果 time 6 5 1查询的目的:以回复时间或者发帖时间为依据,对帖子列表进行排序(时间越新的排的越前,和百度贴吧一样) 如果有回复,则只取reply表里的相关的time值,没有回复则只取contents表里的time值.
MySQL版本: Ver 14.12 Distrib 5.0.51b, for Win32(ia32)
reply:结果应该是 6 5 NULL ? 保留最大1条
CREATE TABLE contents( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) NOT NULL DEFAULT '', content MEDIUMTEXT NOT NULL, title VARCHAR(20) NOT NULL DEFAULT '', TIME INT UNSIGNED NOT NULL );INSERT INTO contents(id,NAME,content,title,TIME) VALUES('1','test1','test1','test1','1');INSERT INTO contents(id,NAME,content,title,TIME) VALUES('2','test2','test2','test2','2');INSERT INTO contents(id,NAME,content,title,TIME) VALUES('3','test3','test3','test3','3');CREATE TABLE reply( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, reply_id INT NOT NULL, NAME VARCHAR(10) NOT NULL DEFAULT '', content MEDIUMTEXT NOT NULL, TIME INT UNSIGNED NOT NULL );INSERT INTO reply(id,reply_id,NAME,content,TIME) VALUES('1','3','reply2','reply2','4');INSERT INTO reply(id,reply_id,NAME,content,TIME) VALUES('2','2','reply1','reply1','5');INSERT INTO reply(id,reply_id,NAME,content,TIME) VALUES('3','3','reply3','reply3','6');SELECT * FROM contents A LEFT JOIN ( SELECT * FROM REPLY A WHERE NOT EXISTS(SELECT 1 FROM REPLY WHERE A.`reply_id`=`reply_id` AND A.`time`<`time`)) B ON A.`id`=B.ID ORDER BY A.`TIME` DESC;
SELECT * FROM contents a2 LEFT JOIN ( SELECT * FROM ( SELECT a.`time`,a.`id` FROM contents a UNION ALL SELECT a1.`time`,a1.`reply_id` FROM REPLY A1) b1 WHERE NOT EXISTS( SELECT 1 FROM ( SELECT a.`time`,a.`id` FROM contents a UNION ALL SELECT a1.`time`,a1.`reply_id` FROM REPLY A1) b2 WHERE b1.id=b2.id AND b1.`time`<`time` )) b3 ON a2.`id`=b3.id ORDER BY b3.`time` DESC
select A.帖子主题 as tex,A.时间 as t
from 帖子表 A
order by id desc
limit 10
union all
select B.回复内容 as tex,B.回复时间 as t
from 回复表 B
where not exists (select 1 from 回复表 C where B.帖子id=C.帖子id and B.回复时间<C.回复时间)
order by B.id desc
limit 10
)T
order by t desc
limit 10
[征集]分组取最大N条记录方法征集,及散分....
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
id int not null primary key auto_increment
name varchar(10) not null default ''
content mediumtext not null
title varchar(20) not null default ''
time int unsigned not null
);insert into contents(id,name,content,title,time) values('1','test1','test1','test1','1');insert into contents(id,name,content,title,time) values('2','test2','test2','test2','2');insert into contents(id,name,content,title,time) values('3','test3','test3','test3','3');
create table reply(
id int not null primary key auto_increment
reply_id int not null
name varchar(10) not null default ''
content mediumtext not null
time int unsigned not null
);insert into reply(id,reply_id,name,content,time) values('1','3','reply2','reply2','4');insert into reply(id,reply_id,name,content,time) values('2','2','reply1','reply1','5');insert into reply(id,reply_id,name,content,time) values('3','3','reply3','reply3','6');想得到的结果是contents中这样的记录:id name content title time
3 test3 test3 test3 3
2 test2 test2 test2 2
1 test1 test1 test1 1同时,我还要得到如下的关于time字段的查询结果
time
6
5
1查询的目的:以回复时间或者发帖时间为依据,对帖子列表进行排序(时间越新的排的越前,和百度贴吧一样)
如果有回复,则只取reply表里的相关的time值,没有回复则只取contents表里的time值.
6
5
NULL
?
保留最大1条
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10) NOT NULL DEFAULT '',
content MEDIUMTEXT NOT NULL,
title VARCHAR(20) NOT NULL DEFAULT '',
TIME INT UNSIGNED NOT NULL
);INSERT INTO contents(id,NAME,content,title,TIME) VALUES('1','test1','test1','test1','1');INSERT INTO contents(id,NAME,content,title,TIME) VALUES('2','test2','test2','test2','2');INSERT INTO contents(id,NAME,content,title,TIME) VALUES('3','test3','test3','test3','3');CREATE TABLE reply(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
reply_id INT NOT NULL,
NAME VARCHAR(10) NOT NULL DEFAULT '',
content MEDIUMTEXT NOT NULL,
TIME INT UNSIGNED NOT NULL
);INSERT INTO reply(id,reply_id,NAME,content,TIME) VALUES('1','3','reply2','reply2','4');INSERT INTO reply(id,reply_id,NAME,content,TIME) VALUES('2','2','reply1','reply1','5');INSERT INTO reply(id,reply_id,NAME,content,TIME) VALUES('3','3','reply3','reply3','6');SELECT * FROM contents A
LEFT JOIN (
SELECT * FROM REPLY A WHERE NOT EXISTS(SELECT 1 FROM REPLY WHERE A.`reply_id`=`reply_id` AND A.`time`<`time`)) B
ON A.`id`=B.ID
ORDER BY A.`TIME` DESC;
我测试了下你给的这个代码..好像不是我想要的结果...其实就是和百度贴吧的那个帖子排序规则一样..按时间来排序..只不过我的contents表专门用来存放楼主的发帖信息...reply专门存放对应于reply_id的那个帖子的回复信息...两个表的time字段都是用来保存发帖、发表回复时的时间的...所以都要用来比较..只不过如果某个帖子如果有回复信息(有对应的reply_id记录存在)..则就按该回复的时间来代表帖子的时间..如果没有就只取帖子的发帖时间来比较..
你这个好像只是按contents表的time字段的值进行排序...没有把reply表的time值加进来比较..如果某个reply表中的time值是最大的..则要把该条记录的reply_id所对应到的contents.id的那条记录放到最前面.
这么说吧..
reply表中的reply3这条记录的time值是6.reply2这条记录的time值是4.但是它们两个的reply_id都为3.同时contents表中的test3这条记录的id值为3,time值为3.所以这3个time的值我取reply3的time值6来和其它的值比较(也就是id为3的test3这个帖子中有一条time值为6的回复)reply表中的reply2这条记录的time值是5.它的reply_id为2.所以contents表中id为2的test2这条记录的time值为2.那么我就取reply2的time值5来进行比较.因为reply表中没有reply_id=1的记录存在.所以我就直接取contents表中id=1的test1这条记录的time值1来进行比较.所以.最后参与比较的是3个值:6>5>1所以我排序的结果是test3>test2>test1
LEFT JOIN (
SELECT * FROM (
SELECT a.`time`,a.`id` FROM contents a
UNION ALL
SELECT a1.`time`,a1.`reply_id` FROM REPLY A1) b1 WHERE NOT EXISTS(
SELECT 1 FROM
(
SELECT a.`time`,a.`id` FROM contents a
UNION ALL
SELECT a1.`time`,a1.`reply_id` FROM REPLY A1) b2
WHERE b1.id=b2.id AND b1.`time`<`time`
)) b3
ON a2.`id`=b3.id ORDER BY b3.`time` DESC