mysql取商_mysql 从五个指定的分类cid中随机取出5个商品，mysql怎么写？

CREATE TABLE item ( cid INT, title varchar(10) );

INSERT INTO item VALUES(50010167, '标题1');

INSERT INTO item VALUES(50000436, '标题2');

INSERT INTO item VALUES(50008881, '标题3');

INSERT INTO item VALUES(50008882, '标题4');

INSERT INTO item VALUES(50011123, '标题5');

INSERT INTO item VALUES(50010167, '标题6');

INSERT INTO item VALUES(50000436, '标题7');

INSERT INTO item VALUES(50008881, '标题8');

INSERT INTO item VALUES(50008882, '标题9');

INSERT INTO item VALUES(50011123, '标题10');

INSERT INTO item VALUES(50010167, '标题11');

INSERT INTO item VALUES(50000436, '标题12');

INSERT INTO item VALUES(50008881, '标题13');

INSERT INTO item VALUES(50008882, '标题14');

INSERT INTO item VALUES(50011123, '标题15');

INSERT INTO item VALUES(50010167, '标题16');

INSERT INTO item VALUES(50000436, '标题17');

INSERT INTO item VALUES(50008881, '标题18');

INSERT INTO item VALUES(50008882, '标题19');

INSERT INTO item VALUES(50011123, '标题20');

SELECT

allData.cid,

`title`

FROM

(

SELECT

CASE

WHEN @cn != cid THEN @rownum:= 1

ELSE @rownum:= @rownum + 1

END AS No,

cid,

@cn := cid AS cid,

`title`

FROM

(SELECT @rownum:=0) r,

(SELECT @cn:=0) p,

`item`

WHERE

`cid` in(50010167,50000436,50008881,50008882,50011123)

ORDER BY

cid

) allData,

(

SELECT

cid,

FLOOR(1 + ( RAND() * count(cid) )) AS randNO

FROM

`item`

WHERE

`cid` in(50010167,50000436,50008881,50008882,50011123)

GROUP BY

cid

) randData

WHERE

allData.cid = randData.cid

AND allData.NO = randData.randNO;

执行结果：

+----------+--------+

| cid      | title  |

+----------+--------+

| 50000436 | 标题12 |

| 50008881 | 标题8  |

| 50008882 | 标题19 |

| 50010167 | 标题1  |

| 50011123 | 标题5  |

+----------+--------+

5 rows in set (0.00 sec)