$sql = "SELECT `contents_table`.* FROM `contents_table` LEFT JOIN `class_table` ON `contents_table`.ClassID=`class_table`.ID ORDER BY `contents_table`.ID DESC LIMIT 6";
//我是以内容表ID的大小来判断,记录的新旧,建议加上插入时间到`内容表`
//我是以内容表ID的大小来判断,记录的新旧,建议加上插入时间到`内容表`
他还要看你的代码,要你用他的sql语句?
select *,1 as x, c2.id as t from c2, c1
where c2.cid=c1.id and (c2.cid, c2.id) in
(select cid, max(id) from c2 group by cid )
union
select *,2 as x, c2.id as t from c2,c1
where c2.cid=c1.id and (c2.cid, c2.id) not in
(select cid, max(id) from c2 group by cid )
order by x, t desc
limit 6
------------------------------------------------------------------
表及数据:CREATE TABLE c1 (
id int(11) NOT NULL auto_increment,
`name` varchar(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;--
-- Dumping data for table `c1`
-- INSERT INTO c1 (id, name) VALUES (1, 'php');
INSERT INTO c1 (id, name) VALUES (2, 'jsp');
INSERT INTO c1 (id, name) VALUES (3, 'asp');-- ----------------------------------------------------------
-- Table structure for table `c2`
-- CREATE TABLE c2 (
id int(11) NOT NULL auto_increment,
`time` datetime NOT NULL,
cid int(11) NOT NULL,
title varchar(20) character set utf8 default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;--
-- Dumping data for table `c2`
-- INSERT INTO c2 (id, time, cid, title) VALUES (1, '2006-11-08 00:00:00', 1, 'php作用是什么 ');
INSERT INTO c2 (id, time, cid, title) VALUES (2, '2006-11-10 00:00:00', 3, 'asp连接数据库 ');
INSERT INTO c2 (id, time, cid, title) VALUES (3, '2006-11-10 00:00:00', 2, '什么是jsp ');
INSERT INTO c2 (id, time, cid, title) VALUES (4, '2006-11-11 00:00:00', 1, 'PHP连接sql server ');
INSERT INTO c2 (id, time, cid, title) VALUES (5, '2006-11-12 00:00:00', 2, 'jsq连接数据库 ');
INSERT INTO c2 (id, time, cid, title) VALUES (6, '2006-11-12 00:00:00', 1, 'PHP连接mysql ');
INSERT INTO c2 (id, time, cid, title) VALUES (7, '2006-11-13 00:00:00', 1, 'php分页类 ');
INSERT INTO c2 (id, time, cid, title) VALUES (8, '2006-11-14 00:00:00', 1, 'php统计系统 ');
INSERT INTO c2 (id, time, cid, title) VALUES (9, '2006-11-16 00:00:00', 2, 'jsp统计系统代码 ');
就是在每插入一条新数据,把本类的其他纪录的newest_flag置0,新插入的纪录newest_flag置1。
这样子sql就好写了
....
ORDER BY newest_flag DESC,ClassID ASC, update DESC
LIMIT 6