mysql 分组后 每组随机取一条记录 求sql语句

mysql 分组后 每组随机取一条记录 求sql语句

CREATE TABLE test_gd2gd2(
id INT,
type INT,
str CHAR(3)
);

INSERT INTO test_gd2gd2
SELECT 1, 0, 'aaa' UNION ALL
SELECT 2, 0, 'bbb' UNION ALL
SELECT 3, 1, 'sss' UNION ALL
SELECT 4, 1, 'ddd' UNION ALL
SELECT 5, 2, 'ggg' UNION ALL
SELECT 6, 2, 'hhh';

mysql> SELECT
-> type,
-> (SELECT str FROM test_gd2gd2 sub
-> WHERE type = main.type ORDER BY rand()
-> LIMIT 0,1) AS Rstr
-> FROM
-> test_gd2gd2 main
-> GROUP BY
-> type;
+------+------+
| type | Rstr |
+------+------+
| 0 | bbb |
| 1 | ddd |
| 2 | hhh |
+------+------+
3 rows in set (0.01 sec)

mysql> SELECT
-> type,
-> (SELECT str FROM test_gd2gd2 sub
-> WHERE type = main.type ORDER BY rand()
-> LIMIT 0,1) AS Rstr
-> FROM
-> test_gd2gd2 main
-> GROUP BY
-> type;
+------+------+
| type | Rstr |
+------+------+
| 0 | aaa |
| 1 | sss |
| 2 | hhh |
+------+------+
3 rows in set (0.00 sec)2011-09-01

SELECT * FROM (SELECT * FROM tablename ORDER BY RAND()) as a GROUP BY a.type2011-09-07

在sqlserver中用的是top关键字 比如查询user表
select * from user limit 1 ;这就可以了，， 另外 mysql 中的翻页很方便的。比sqlserver中的简单多了。
2011-09-01

select * from table order by rand() limit 0,1;2011-09-01

wangzhiqing999 ， holychuo答案均正确，但 wangzhiqing999 好象要强一点，学习了2011-09-10