CREATE TABLE `order` ( `order_id` int(11) NOT NULL AUTO_INCREMENT, `phone`bigint (20) not null default' 0' comment' phone number', PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Is there any way to avoid union all, because this is my simplified model, and in actual business, the number of phone may be large.
Select phone, GROUP_CONCAT(order_id ORDER BY rand()) From `order` Where? phone IN (11, 22) GROUP BY Phone
The above method still takes out all the data, and the GROUP_CONCAT function does not support the limit keyword.
(SELECT `order_id` FROM `order` WHERE `phone`='$phone1' LIMIT 3 ORDER BY rand()) UNION ALL (SELECT `order_id` FROM `order` WHERE `phone`='$phone2' LIMIT 3 ORDER BY rand())
If only SQL is used, this is the simplest, but ORDER BY rand () has poor performance. The best thing is to take out a certain number of records and then randomly select three more in the program.