遇到困难啦，求一SQL语句，~~~~~~~~~~~~急呀`~~有美女呀~~~

[xiaohuozhi]

我有一个表，如下：
+------+-------+-------+
|Name | grade | class |
+------+-------+-------+
|anson |1 |1 |
+------+-------+-------+
|ABC |1 |1 |
+------+-------+-------+
|DEF |1 |1 |
+------+-------+-------+
|GAD |1 |2 |
+------+-------+-------+
|ABE |1 |2 |
+------+-------+-------+
|ABD |1 |2 |
+------+-------+-------+
|AYU |1 |3 |
+------+-------+-------+
|RHD |1 |3 |
+------+-------+-------+
|JED |1 |3 |
+------+-------+-------+
|JRF |2 |1 |
+------+-------+-------+
|adf |2 |1 |
+------+-------+-------+
|hjg |2 |1 |
+------+-------+-------+
|asf |2 |2 |
+------+-------+-------+
|yti |2 |2 |
+------+-------+-------+
|bvg |2 |2 |
+------+-------+-------+
|rte |2 |3 |
+------+-------+-------+
|jgf |2 |3 |
+------+-------+-------+
|vff |2 |3 |
+------+-------+-------+
|erd |3 |1 |
+------+-------+-------+
|afd |3 |1 |
.....
+------+-------+-------+
|URD |n |n |
+------+-------+-------+
|URD |n |n |
+------+-------+-------+

我想从每个grade里的每个class里面随机挑选两条记录，请问SQL语句要怎么写？

[walkany]

大概不能实现吧，怎么限制分类纪录的条数呢？

[shuixin13]

可以实现的,
下面给出示例.
测试环境 MySQL 5.0.15


-- 创建测试环境
CREATE TABLE `tA` (
`id` int(10) unsigned NOT NULL default '0',
`content` varchar(255) NOT NULL default ''
) ENGINE=MyISAM;

INSERT INTO `tA` VALUES (1, '1,1');
INSERT INTO `tA` VALUES (1, '1,2');
INSERT INTO `tA` VALUES (1, '1,3');
INSERT INTO `tA` VALUES (1, '1,4');
INSERT INTO `tA` VALUES (2, '2,1');
INSERT INTO `tA` VALUES (2, '2,2');
INSERT INTO `tA` VALUES (2, '2,3');
INSERT INTO `tA` VALUES (2, '2,4');
INSERT INTO `tA` VALUES (3, '3,1');
INSERT INTO `tA` VALUES (3, '3,2');
INSERT INTO `tA` VALUES (3, '3,3');
INSERT INTO `tA` VALUES (4, '4,1');
INSERT INTO `tA` VALUES (4, '4,2');
INSERT INTO `tA` VALUES (4, '4,3');




SELECT tC.`Id`, tC.`content`
FROM
(
SELECT
@Id := CASE WHEN (@GroupId <> `Id`) THEN 1 WHEN ((@GroupId = `Id`) /*&& (@Id < 3)*/) THEN (@Id + 1) ELSE 0 END `RId`
, (@GroupId := `Id`)

, `tA`.*
FROM
(SELECT * FROM `tA` ORDER BY `Id`, RAND()) `tA`
,(SELECT @Id := 0, @GroupId := -1) tB
) `tC`
WHERE tC.`RId` < 3;


-- 删除测试环境
DROP TABLE `tA`;

[shuixin13]

还有一个方法是通过临时表。
思路与上面的一样。


就是选项组合成中间结果

+------+--------------------+----+---------+
| RId | (@GroupId := `Id`) | id | content |
+------+--------------------+----+---------+
| 1 | 1 | 1 | 1,2 |
| 2 | 1 | 1 | 1,3 |
| 3 | 1 | 1 | 1,2 |
| 4 | 1 | 1 | 1,4 |
| 5 | 1 | 1 | 1,1 |
| 6 | 1 | 1 | 1,1 |
| 7 | 1 | 1 | 1,4 |
| 8 | 1 | 1 | 1,3 |
| 9 | 1 | 1 | 1,4 |
| 10 | 1 | 1 | 1,3 |
| 11 | 1 | 1 | 1,2 |
| 12 | 1 | 1 | 1,1 |
| 1 | 2 | 2 | 2,2 |
| 2 | 2 | 2 | 2,4 |
| 3 | 2 | 2 | 2,2 |
| 4 | 2 | 2 | 2,3 |
| 5 | 2 | 2 | 2,1 |
| 6 | 2 | 2 | 2,3 |
| 7 | 2 | 2 | 2,1 |
| 8 | 2 | 2 | 2,1 |
| 9 | 2 | 2 | 2,2 |
| 10 | 2 | 2 | 2,3 |
| 11 | 2 | 2 | 2,4 |
| 12 | 2 | 2 | 2,4 |
| 1 | 3 | 3 | 3,1 |
| 2 | 3 | 3 | 3,3 |
| 3 | 3 | 3 | 3,1 |
| 4 | 3 | 3 | 3,2 |
| 5 | 3 | 3 | 3,3 |
| 6 | 3 | 3 | 3,2 |
| 7 | 3 | 3 | 3,3 |
| 8 | 3 | 3 | 3,2 |
| 9 | 3 | 3 | 3,1 |
| 1 | 4 | 4 | 4,1 |
| 2 | 4 | 4 | 4,3 |
| 3 | 4 | 4 | 4,3 |
| 4 | 4 | 4 | 4,2 |
| 5 | 4 | 4 | 4,1 |
| 6 | 4 | 4 | 4,1 |
| 7 | 4 | 4 | 4,3 |
| 8 | 4 | 4 | 4,2 |
| 9 | 4 | 4 | 4,2 |
+------+--------------------+----+---------+