表tt,里面的数据为
id value
1 11
1 12
1 13
1 14
1 15
1 16
2 21
2 22
2 23
2 24怎样取出每个id的前3条记录?是不是要用存储过程?用的话,存储过程怎么写?我还没接触过这部分知识。
如果不用存储过程,应该怎么写?先谢过了^_^
id value
1 11
1 12
1 13
1 14
1 15
1 16
2 21
2 22
2 23
2 24怎样取出每个id的前3条记录?是不是要用存储过程?用的话,存储过程怎么写?我还没接触过这部分知识。
如果不用存储过程,应该怎么写?先谢过了^_^
Select *
From tbl A
Where (Select Count(*) From tbl Where id = A.id And value > A.value) < 3
Order By, value Desc
Select * From tbl A
Where (Select Count(*) From tbl Where id = A.id And value > A.value) < 3
Order By id, value Desc
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`test` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
declare i int default 1;
declare c int default (select count(distinct id) from tt);
create temporary table abc select (select count(distinct id) from tt where id <= a.id) as id2, id from tt a;
create temporary table temp select id, value from tt limit 0,1;
delete from temp;
while i <= c
do
insert into temp select id, value from (select a.id, a.value from tt a, abc b where a.id = b.id and b.id2 = i) x order by id limit 0,3;
set i = i + 1;
end while;
select * from temp;
END $$DELIMITER ;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
declare i int default 1;
declare c int default (select count(distinct id) from tt);
create temporary table abc select (select count(distinct id) from tt where id <= a.id) as id2, id from tt a;
create temporary table temp select id, value from tt limit 0,1;
delete from temp;
while i <= c
do
insert into temp select id, value from (select a.id, a.value from tt a, abc b where a.id = b.id and b.id2 = i) x limit 0,3;
set i = i + 1;
end while;
select * from temp;
END $$DELIMITER ;不用 order by 排序
SELECT * FROM (
SELECT *,(SELECT COUNT(*) FROM TTB WHERE A.ID=ID AND A.VALUE<=VALUE) AS PX FROM TTB A
) A
WHERE PX<=3
ORSELECT A.ID,A.value FROM TTB A LEFT JOIN TTB B ON A.ID=B.ID AND A.value<=B.value
GROUP BY A.ID,A.value
HAVING COUNT(*)<=3