[征集]分组取最大N条记录方法征集，及散分(新年，升星)
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语句，存储过程。
[解决办法]
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
[解决办法]
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
[解决办法]
恭喜版主!!
mysql:5.0.45-community-nt
1)
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;
[解决办法]
----------------------------------- Author: liangCK 小梁-- Title : 查每个分组前N条记录-- Date : 2008-11-13 17:19:23-----------------------------------> 生成测试数据: #TIF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #TCREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)INSERT INTO #TSELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALLSELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALLSELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALLSELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALLSELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALLSELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALLSELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALLSELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALLSELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALLSELECT '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,DateFROM( SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),* FROM #T) AS TWHERE rid<=2--4.使用APPLYSELECT DISTINCT b.*FROM #T AS aCROSS 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.000004 1 KalenDelaney SQLServer2005技术内幕存储引擎 2008-08-01 00:00:00.000005 2 Alex.Kriegel.Boris.M.Trukhnov SQL宝典 2007-10-05 00:00:00.000007 2 胡百敬 SQLServer2005数据库开发详解 2008-06-15 00:00:00.000009 3 赵松涛 SQLServer2005系统管理实录 2008-10-01 00:00:00.000010 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', 4Union All Select 'BD1V', 'BATT', 2Union All Select 'BD1V', 'ODD', 3Union All Select 'BD1V', 'HDD', 5Union All Select 'BD1V', 'LCD', 1Union All Select 'BD1W','HDD', 3Union All Select 'BD1W','RAM', 8Union All Select 'BD1W','TP CABLE', 5Union All Select 'BD1W','LCD', 6Union All Select 'BD1W','Label', 2Union All Select 'BL3', 'LCD CABLE', 7Union All Select 'BL3', 'LABEL', 6Union All Select 'BL3', 'LCD', 5Union All Select 'BL3', 'RAM', 1Union All Select 'BL3D', 'Label', 4GO--測試--方法一：Select Col1, Col2, Col3 From TEST AWhere (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3) < 3Order By Col1, Col3 Desc--方法二：Select Col1, Col2, Col3 From TEST AWhere 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 AWhere Col3 In (Select TOP 3 Col3 From TEST Where Col1 = A.Col1 Order By Col3 Desc)Order By Col1, Col3 DescGO--刪除測試環境Drop Table TEST--結果/*Col1 Col2 Col3BD1V HDD 5BD1V Label 4BD1V ODD 3BD1W RAM 8BD1W LCD 6BD1W TP CABLE 5BL3 LCD CABLE 7BL3 LABEL 6BL3 LCD 5BL3D Label 4*/
[解决办法]
select * from t2 where concat(gid,col2) in(select concat(gid,max(col2)) from t2 group by gid);
[解决办法]
jf
[解决办法]
jf
[解决办法]
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
[解决办法]
jf。
[解决办法]
恭喜版主，接分如接红包，谢谢！
[解决办法]
赶在年末接分 happy new year
[解决办法]
2010好啊
[解决办法]
贴吧
[解决办法]
呵呵，学习中。。。。
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 desc
2) 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
[解决办法]
不懂，学习！
[解决办法]
接分了，谢谢
[解决办法]
恭喜升级
[解决办法]
恭喜升级 学习
[解决办法]
JF，恭喜
[解决办法]
+----------------------+
| 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 |
+----+------+------+------+
[解决办法]
jf+学习
[解决办法]
[解决办法]
学习，接分
[解决办法]
大神的帖子，不懂也要顶个
[解决办法]
看看
------解决方案--------------------
呵呵！！
[解决办法]
所谓高手就是这样炼成的
[解决办法]
恭喜,恭喜...
[解决办法]
learning.
[解决办法]
learn
[解决办法]
mark
[解决办法]
Mark!
[解决办法]
ddddddddddddddddddddd
dsdd
d
dd
d
d
d
d
d
d
d
d
d
d
d
[解决办法]
数据库: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); --SQLselect * 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
[解决办法]
当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
[解决办法]
不太懂啊
[解决办法]
有点乱！！！
[解决办法]
学习
[解决办法]
select * from (select *,ids=(select count(distinct col2)from t2where col2>=k.col2 and gid=k.gidgroup by gid)from t2 k) as twhere ids<=3order by gid
[解决办法]
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 rankFROM t2 o1 ) xWHERE x.rank<=3
[解决办法]
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 )
[解决办法]
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