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

CREATE TABLE test_gd2gd2(

idINT,

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)

创建数据表

create table test

(username varchar(10),

type int)

insert into test values ('aaa',1)

insert into test values ('bbb',1)

insert into test values ('ccc',2)

insert into test values ('ddd',2)

insert into test values ('eee',3)

执行

select s.username,s.type

from

(select username,type,rank

from

(select t.username,t.type,@rownum:=@rownum+1,

if (@type=t.type,@rank:=@rank+1,@rank:=1) as rank,

@type:=t.type

from(

select username,type from test order by type) t,

(select @rownum:=9,@type:=null,@rank:=0) a

) result) s

where s.rank=1

结果截图

---