[征集]分组取最大N条记录方法征集，及散分(新年，升星)

Question

2009 的最后一天，升了一颗星，同时也祝大家新年快乐。由于技术版不能散分。所以在此征集 <分组取最大N条记录>create table t2 (
    id int primary key,
    gid    char,
    col1    int,
    col2    int
) engine=myisam;insert into t2 values 
(1,'A',31,6),
(2,'B',25,83),
(3,'C',76,21),
(4,'D',63,56),
(5,'E',3,17),
(6,'A',29,97),
(7,'B',88,63),
(8,'C',16,22),
(9,'D',25,43),
(10,'E',45,28),
(11,'A',2,78),
(12,'B',30,79),
(13,'C',96,73),
(14,'D',37,40),
(15,'E',14,86),
(16,'A',32,67),
(17,'B',84,38),
(18,'C',27,9),
(19,'D',31,21),
(20,'E',80,63),
(21,'A',89,9),
(22,'B',15,22),
(23,'C',46,84),
(24,'D',54,79),
(25,'E',85,64),
(26,'A',87,13),
(27,'B',40,45),
(28,'C',34,90),
(29,'D',63,8),
(30,'E',66,40),
(31,'A',83,49),
(32,'B',4,90),
(33,'C',81,7),
(34,'D',11,12),
(35,'E',85,10),
(36,'A',39,75),
(37,'B',22,39),
(38,'C',76,67),
(39,'D',20,11),
(40,'E',81,36);
期望结果
1) N=1 取GID每组 COL2最大的记录
    +----+------+------+------+
    | id | gid  | col1 | col2 |
    +----+------+------+------+
    |  6 | A    |   29 |   97 |
    | 15 | E    |   14 |   86 |
    | 24 | D    |   54 |   79 |
    | 28 | C    |   34 |   90 |
    | 32 | B    |    4 |   90 |
    +----+------+------+------+
2) N=3 取GID每组 COL2最大的3条记录
    +----+------+------+------+
    | id | gid  | col1 | col2 |
    +----+------+------+------+
    |  6 | A    |   29 |   97 |
    | 11 | A    |    2 |   78 |
    | 36 | A    |   39 |   75 |
    | 32 | B    |    4 |   90 |
    |  2 | B    |   25 |   83 |
    | 12 | B    |   30 |   79 |
    | 28 | C    |   34 |   90 |
    | 23 | C    |   46 |   84 |
    | 13 | C    |   96 |   73 |
    | 24 | D    |   54 |   79 |
    |  4 | D    |   63 |   56 |
    |  9 | D    |   25 |   43 |
    | 15 | E    |   14 |   86 |
    | 25 | E    |   85 |   64 |
    | 20 | E    |   80 |   63 |
    +----+------+------+------+
    
注：
1）不限数据库，但请说明，比如 Oracle Database 10g 10.2 , MySQL 5.1.33
2) 不限方法， SQL语句，存储过程。

Answer 1

SELECT a.id,a.gid,a.col1,a.col2 FROM t2v a
LEFT JOIN t2v b
ON a.gid=b.gid AND a.col2<=b.col2
GROUP BY a.id,a.gid,a.col1,a.col2
HAVING COUNT(b.id)<=3
ORDER BY a.gid,a.col2 desc

Answer 2

SELECT a.id,a.gid,a.col1,a.col2 FROM t2v a
WHERE 3>=(
SELECT COUNT(*) FROM t2v b
WHERE a.gid=b.gid AND a.col2<=b.col2)
ORDER BY a.gid,a.col2 desc

Answer 3

恭喜版主!!mysql:5.0.45-community-nt1) 
select * from t2 a
where not exists
(select 1 from t2 where gid=a.gid and col2>a.col2);2)
select * from t2 a where
3>(select count(*) from t2 where gid=a.gid and col2>a.col2)
order by a.gid,a.col2 desc;

Answer 4

asa11:
select * from (select id,gid,col1,col2, ROW_NUMBER( ) over(partition by gid order by col2) as bz from t2v group by id,gid,col1,col2) a
where bz<=3

Answer 5

---------------------------------
--  Author: liangCK 小梁
--  Title : 查每个分组前N条记录
--  Date  : 2008-11-13 17:19:23
-----------------------------------> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)
INSERT INTO #T
SELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALL
SELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALL
SELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALL
SELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALL
SELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALL
SELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALL
SELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALL
SELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALL
SELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALL
SELECT '010',3,'黄占涛','SQL技术手册','2006-01-01'--SQL查询如下:--按GID分组,查每个分组中Date最新的前2条记录
--1.字段ID唯一时:
SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC)--2.如果ID不唯一时:
SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date)--SQL Server 2005 使用新方法--3.使用ROW_NUMBER()进行排位分组
SELECT ID,GID,Author,Title,Date
FROM
(
   SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),*
   FROM #T
) AS T
WHERE rid<=2--4.使用APPLY
SELECT DISTINCT b.*
FROM #T AS a
CROSS APPLY
(
    SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC
) AS b
--结果
/*ID   GID         Author                        Title                                   Date
---- ----------- ----------------------------- --------------------------------------- -----------------------
003  1           格罗夫Groff.J.R.                 SQL完全手册                                 2009-07-01 00:00:00.000
004  1           KalenDelaney                  SQLServer2005技术内幕存储引擎                   2008-08-01 00:00:00.000
005  2           Alex.Kriegel.Boris.M.Trukhnov SQL宝典                                   2007-10-05 00:00:00.000
007  2           胡百敬                           SQLServer2005数据库开发详解                    2008-06-15 00:00:00.000
009  3           赵松涛                           SQLServer2005系统管理实录                     2008-10-01 00:00:00.000
010  3           黄占涛                           SQL技术手册                                 2006-01-01 00:00:00.000(6 行受影响)
*/--得到每组前几条数据
--假設每組Col1中， Col3不會重復--建立測試環境
Create Table TEST
(Col1 Varchar(10),
 Col2 Varchar(10),
 Col3 Int)
--插入數據
Insert TEST Select 'BD1V','Label', 4
Union All Select 'BD1V', 'BATT', 2
Union All Select 'BD1V', 'ODD', 3
Union All Select 'BD1V', 'HDD', 5
Union All Select 'BD1V', 'LCD', 1
Union All Select 'BD1W','HDD', 3
Union All Select 'BD1W','RAM', 8
Union All Select 'BD1W','TP CABLE', 5
Union All Select 'BD1W','LCD', 6
Union All Select 'BD1W','Label', 2
Union All Select 'BL3', 'LCD CABLE', 7
Union All Select 'BL3', 'LABEL', 6
Union All Select 'BL3', 'LCD', 5
Union All Select 'BL3', 'RAM', 1
Union All Select 'BL3D', 'Label', 4
GO
--測試
--方法一：
Select Col1, Col2, Col3 From TEST A
Where (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3) < 3
Order By Col1, Col3 Desc
--方法二：
Select Col1, Col2, Col3 From TEST A
Where Exists (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3 Having Count(*) < 3)
Order By Col1, Col3 Desc
--方法三：
Select Col1, Col2, Col3 From TEST A
Where Col3 In (Select TOP 3 Col3 From TEST Where Col1 = A.Col1 Order By Col3 Desc)
Order By Col1, Col3 Desc
GO
--刪除測試環境
Drop Table TEST
--結果
/*
Col1  Col2   Col3
BD1V HDD  5
BD1V Label  4
BD1V ODD  3
BD1W RAM  8
BD1W LCD   6
BD1W TP CABLE 5
BL3  LCD CABLE 7
BL3  LABEL  6
BL3  LCD   5
BL3D Label  4
*/

Answer 6

select * from t2 where concat(gid,col2) in(select concat(gid,max(col2)) from t2 group by gid);

Answer 7

mysql:
SELECT gid,col2,1 INTO @i,@j,@mc FROM t2v LIMIT 1;
SELECT id,gid,col1,col2 FROM (
SELECT a.id,a.gid,a.col1,a.col2,
IF(@i<>gid,@mc:=1,@mc) AS a2,
IF(@i<>gid,@i:=gid,@i) AS b1,
IF(@i=gid AND col2>=@j,@mc:=@mc+1,@mc) AS a1 FROM t2v a
 ORDER BY gid,col2 DESC) aa
 WHERE a2<=3

Answer 8

赶在年末接分 happy new year

Answer 9

呵呵，学习中。
mysql5.1.41
期望结果 
1) N=1 取GID每组 COL2最大的记录
SELECT a.id,a.gid,a.col1,a.col2 
FROM t2 as a,t2 as b 
where a.gid=b.gid AND a.col2 <=b.col2
GROUP BY a.id,a.gid,a.col1,a.col2 
having a.col2>=max(b.col2)
ORDER BY a.gid,a.col2 desc2) N=3 取GID每组 COL2最大的3条记录
SELECT a.id,a.gid,a.col1,a.col2 
FROM t2  a,t2 b 
where a.gid=b.gid AND a.col2 <=b.col2
GROUP BY a.id,a.gid,a.col1,a.col2 
HAVING   COUNT(b.id) <=3 
ORDER BY a.gid,a.col2 desc

Answer 10

今天在 rmstar 提醒之下，找到一种新方法。1) N=1 取GID每组 COL2最大的记录
mysql> select *
    -> from (select * from t2 order by gid,col2 desc) t
    -> group by gid;
+----+------+------+------+
| id | gid  | col1 | col2 |
+----+------+------+------+
|  6 | A    |   29 |   97 |
| 32 | B    |    4 |   90 |
| 28 | C    |   34 |   90 |
| 24 | D    |   54 |   79 |
| 15 | E    |   14 |   86 |
+----+------+------+------+
5 rows in set (0.00 sec)mysql>

Answer 11

SQL2005:
select * from (select id,gid,col1,col2, ROW_NUMBER( ) over(partition by gid order by col2 DESC) as bz from ZW1..t2v group by id,gid,col1,col2) a 
where bz <=3

Answer 12

+----------------------+
| 5.1.37-community-log |
+----------------------+
1 row in set (0.00 sec)root@test 11:02:50>select * from t2 a where not exists (select 1 from t2 where a.gid= gid and a.col2<col2) order by gid;
+----+------+------+------+
| id | gid  | col1 | col2 |
+----+------+------+------+
|  6 | A    |   29 |   97 |
| 32 | B    |    4 |   90 |
| 28 | C    |   34 |   90 |
| 24 | D    |   54 |   79 |
| 15 | E    |   14 |   86 |
+----+------+------+------+

Answer 13

oracel、POSTGRESQL 8.4.2均可用20楼代码

Answer 14

ddddddddddddddddddddd
dsdd
d
dd
d
d
d
d
d
d
d
d
d
d
d

Answer 15

数据库:ORACLE 9I以上--建表语句
create table t2 ( 
    id int primary key, 
    gid    char, 
    col1    int, 
    col2    int 
) ;insert into t2 values (1,'A',31,6);
insert into t2 values (2,'B',25,83);
insert into t2 values (3,'C',76,21); 
insert into t2 values (4,'D',63,56); 
insert into t2 values (5,'E',3,17); 
insert into t2 values (6,'A',29,97); 
insert into t2 values (7,'B',88,63); 
insert into t2 values (8,'C',16,22); 
insert into t2 values (9,'D',25,43); 
insert into t2 values (10,'E',45,28); 
insert into t2 values (11,'A',2,78); 
insert into t2 values (12,'B',30,79); 
insert into t2 values (13,'C',96,73); 
insert into t2 values (14,'D',37,40); 
insert into t2 values (15,'E',14,86); 
insert into t2 values (16,'A',32,67); 
insert into t2 values (17,'B',84,38); 
insert into t2 values (18,'C',27,9);
insert into t2 values (19,'D',31,21); 
insert into t2 values (20,'E',80,63); 
insert into t2 values (21,'A',89,9); 
insert into t2 values (22,'B',15,22); 
insert into t2 values (23,'C',46,84); 
insert into t2 values (24,'D',54,79); 
insert into t2 values (25,'E',85,64); 
insert into t2 values (26,'A',87,13); 
insert into t2 values (27,'B',40,45); 
insert into t2 values (28,'C',34,90); 
insert into t2 values (29,'D',63,8); 
insert into t2 values (30,'E',66,40); 
insert into t2 values (31,'A',83,49); 
insert into t2 values (32,'B',4,90); 
insert into t2 values (33,'C',81,7); 
insert into t2 values (34,'D',11,12); 
insert into t2 values (35,'E',85,10); 
insert into t2 values (36,'A',39,75); 
insert into t2 values (37,'B',22,39); 
insert into t2 values (38,'C',76,67); 
insert into t2 values (39,'D',20,11); 
insert into t2 values (40,'E',81,36); --SQL
select *
  from (select id,
               gid,
               col1,
               col2,
               rank() over(partition by gid order by col2 desc) as r_col2
          from t2) tt2
 where tt2.r_col2 = 1--1置换成:N
 order by id

Answer 16

当分组后,COL2最大有重复的时候,是该都输出还是仅输出一条?

Answer 17

当N=1时候,可以这样 select t2.*
   from t2, (select gid, max(col2) as m_col2 from t2 group by gid) tt2
  where t2.gid = tt2.gid
    and t2.col2 = tt2.m_col2 order by id

Answer 18

20楼的代码存在这样的问题是关于分析函数ROW_NUMBER() 与RANK()的区别
下来来实验--建表语句
create table t2 ( 
    id int primary key, 
    gid    char, 
    col1    int, 
    col2    int 
) ;insert into t2 values (1,'A',31,6);
insert into t2 values (2,'B',25,83);
insert into t2 values (3,'C',76,21); 
insert into t2 values (4,'D',63,56); 
insert into t2 values (5,'E',3,17); 
insert into t2 values (6,'A',29,97); 
insert into t2 values (7,'B',88,63); 
insert into t2 values (8,'C',16,22); 
insert into t2 values (9,'D',25,43); 
insert into t2 values (10,'E',45,28); 
insert into t2 values (11,'A',2,78); 
insert into t2 values (12,'B',30,79); 
insert into t2 values (13,'C',96,73); 
insert into t2 values (14,'D',37,40); 
insert into t2 values (15,'E',14,86); 
insert into t2 values (16,'A',32,67); 
insert into t2 values (17,'B',84,38); 
insert into t2 values (18,'C',27,9);
insert into t2 values (19,'D',31,21); 
insert into t2 values (20,'E',80,63); 
insert into t2 values (21,'A',89,9); 
insert into t2 values (22,'B',15,22); 
insert into t2 values (23,'C',46,84); 
insert into t2 values (24,'D',54,79); 
insert into t2 values (25,'E',85,64); 
insert into t2 values (26,'A',87,13); 
insert into t2 values (27,'B',40,45); 
insert into t2 values (28,'C',34,90); 
insert into t2 values (29,'D',63,8); 
insert into t2 values (30,'E',66,40); 
insert into t2 values (31,'A',83,49); 
insert into t2 values (32,'B',4,90); 
insert into t2 values (33,'C',81,7); 
insert into t2 values (34,'D',11,12); 
insert into t2 values (35,'E',85,10); 
insert into t2 values (36,'A',39,75); 
insert into t2 values (37,'B',22,39); 
insert into t2 values (38,'C',76,67); 
insert into t2 values (39,'D',20,11); 
insert into t2 values (40,'E',81,36); 
--row_number
select * from (
select * ,row_number() over ( partition by gid order by col2 desc) as cc from t2
) tt2 where tt2.cc<=1--查询结果
id          gid  col1        col2        cc
----------- ---- ----------- ----------- --------------------
6           A    29          97          1
32          B    4           90          1
28          C    34          90          1
24          D    54          79          1
15          E    14          86          1(5 行受影响)--rank
select * from (
select * ,rank() over ( partition by gid order by col2 desc) as cc from t2
) tt2 where tt2.cc<=1--查询结果
id          gid  col1        col2        cc
----------- ---- ----------- ----------- --------------------
6           A    29          97          1
32          B    4           90          1
28          C    34          90          1
24          D    54          79          1
15          E    14          86          1(5 行受影响)现在我再INSERT 一条数据
--插入数据
insert into t2 values (41,'A',29,97); 再运行如下脚本:
select * from (
select * ,row_number() over ( partition by gid order by col2 desc) as cc from t2
) tt2 where tt2.cc<=1
--查询结果还是不变
id          gid  col1        col2        cc
----------- ---- ----------- ----------- --------------------
6           A    29          97          1
32          B    4           90          1
28          C    34          90          1
24          D    54          79          1
15          E    14          86          1(5 行受影响)当用RANK()的时候,得的结果就不一样,如下:id          gid  col1        col2        cc
----------- ---- ----------- ----------- --------------------
6           A    29          97          1
41          A    29          97          1
32          B    4           90          1
28          C    34          90          1
24          D    54          79          1
15          E    14          86          1结果多出了一行,我不知道LZ要的究竟是那个结果? 

Answer 19

SELECT a.id,a.gid,a.col1,a.col2 FROM t2v a 
LEFT JOIN t2v b 
ON a.gid=b.gid AND a.col2 <=b.col2 
GROUP BY a.id,a.gid,a.col1,a.col2 
HAVING COUNT(b.id) <=3 
ORDER BY a.gid,a.col2 desc 

Answer 20

select * from (
select *,ids=(
select count(distinct col2)
from t2
where col2>=k.col2 and gid=k.gid
group by gid)
from t2 k
) as t
where ids<=3
order by gid注：col2如果有重复数据(相同gid)，则有可能把重复的col2也显示出来

Answer 21

恭喜楼主 SQL对于自己还是比较薄弱的一方面 需要继续努力

Answer 22

MSSQL2005当中，如果排序项不会重复出现，那除了可以使用row_number以后，还可以使用另一排名函数dense_rank。
在楼主的示例当中，如果col2在所在的gid分组内不会存在重复的值。则可以使用以下排名函数解决方案。SELECT * FROM (
SELECT o1.id, o1.gid, o1.col1,o1.col2,DENSE_RANK() 
OVER (PARTITION BY O1.gid ORDER BY o1.col2 desc ) AS rank
FROM t2 o1 ) x
WHERE x.rank<=3

Answer 23

select * from t2 a where 1=(select count(b.id) from t2 b where b.gid=a.gid  and b.col2>a.col2 )select * from t2 a where 3=(select count(b.id) from t2 b where b.gid=a.gid  and b.col2>a.col2 ) 

Answer 24

SQL SERVER 2000
select *
from t2 a ,
(select gid,col2 = max(b.col2)
  from t2 b
   group by gid
) b
where a.gid = b.gid
          and a.col2 = b.col2
order by a.gid

Answer 25

SQL SERVER 2000 取每组最大的前三条记录：
select a.id,a.gid,a.col1,a.col2
from t2 a left join t2 b
on a.gid = b.gid
           and a.col2 <= b.col2
group by a.id,a.gid,a.col1,a.col2
having count(b.id) <= 3
order by a.gid