CREATE TABLE article
(
ID int auto_increment,
title varchar(250) NOT NULL,
type int not null,
content text,
date timestamp not null,
primary key(id)
);type表示文章的分类.现在想从每个分类中取出最新发布的5条记录,用一个SQL语句能不能实现呀?
我在MS SqlServer专区搜到了一个帖子,但MySql不支持子查询中带limit的语法.
调试欢乐多
//eg:从columnid in (25,30) 中安装发布时间提取前tmpnum条记录drop procedure if exists getrecord;
delimiter //
create procedure getrecord(in tmpnum int)
begin
declare is_last_row tinyint default 0;
declare tmpcolumnid int default 0;
declare cur1 cursor for select columnid from liv_column where columnid >= 25 and columnid <= 30 order by columnid asc;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_last_row=1; set is_last_row = 0;
set @_tmpnum = tmpnum;create table if not exists tmptable(id int(10) not null default 0,
columnid int(10) not null default 0,
title char(64) not null default ''
)engine=memory default character set = utf8;
truncate table tmptable;open cur1;
cursor_loop:LOOP
fetch cur1 into tmpcolumnid;
set @_tmpcolumnid = tmpcolumnid;
if is_last_row = 1 then
leave cursor_loop;
end if;prepare stmt1 from 'insert into tmptable select id,columnid,title from liv_contentmap where columnid = ? order by pubdate desc limit ?';
execute stmt1 using @_tmpcolumnid,@_tmpnum;
deallocate prepare stmt1;END LOOP cursor_loop;close cur1;
select * from tmptable;
end
//
delimiter ;
将记录及要求结果贴出来看看,应该可以用SQL语句
每一篇文章都有一个type,比如500条记录,一共10类,类别号分别是1~10
现在想从每个类别中取date最晚的5条记录出来.能否用一个SQL语句实现?
SELECT * FROM (
SELECT *,(SELECT COUNT(*) FROM TT WHERE TYPE=A.TYPE AND A.DATE<=DATE) AS JS FROM TT A)
WHERE JS<=5OR
SELECT A1.* FROM TT A1
INNER JOIN
(SELECT A.TYPE,A.DATE FROM TT A LEFT JOIN TT B
ON A.TYPE=B.TYPE AND A.DATE<=B.DATE
HAVING COUNT(B.DATE)<=5) B1
ON A1.TYPE=B1.TYPE AND A1.DATE=B1.DATE第二个性能高一些
SELECT A1.* FROM TT A1
INNER JOIN
(SELECT A.TYPE,A.DATE FROM TT A LEFT JOIN TT B
ON A.TYPE=B.TYPE AND A.DATE <=B.DATE group by A.TYPE,A.DATE
HAVING COUNT(B.DATE) <=5) B1
ON A1.TYPE=B1.TYPE AND A1.DATE=B1.DATE
INNER JOIN
(SELECT A.TYPE,A.DATE FROM TT A LEFT JOIN TT B
ON A.TYPE=B.TYPE AND A.DATE <=B.DATE group by A.TYPE,A.DATE
HAVING COUNT(B.DATE) <=5) B1
ON A1.TYPE=B1.TYPE AND A1.DATE=B1.DATE这种方式速度比较快,如只要TYPE、DATE
SELECT A.TYPE,A.DATE FROM TT A LEFT JOIN TT B
ON A.TYPE=B.TYPE AND A.DATE <=B.DATE group by A.TYPE,A.DATE
HAVING COUNT(B.DATE) <=5
现在结贴,每人一半分吧.刚才通过循环添加了两千条记录测试了一下都可以.