INSERT INTO t_carrier_route (id,carrier_user_id,`START_PROVINCE`,start_city,`START_AREA`,start_address,`END_PROVINCE`,`END_CITY`,`END_AREA`,end_address,create_date,create_by,del_flag)
SELECT REPLACE(UUID(),"-",""),blood.carrier_user_id,blood.`START_PROVINCE`,blood.start_city,blood.`START_AREA`,blood.start_address,blood.`END_PROVINCE`,blood.`END_CITY`,blood.`END_AREA`,blood.`end_address`,NOW(),1,0
FROM
(SELECT *
FROM (SELECT tb.id,tb.dd,tb.carrier_user_id,tb.start_city,tb.`START_PROVINCE`,tb.`START_AREA`,tb.start_address,tb.`END_PROVINCE`,tb.`END_CITY`,tb.`END_AREA`,tb.end_address,
@num := IF(@type=tb.carrier_user_id, @num + 1, 1) AS row_number,
@type := tb.carrier_user_id AS other_type
FROM (SELECT *,(select @num := 0),(select @type := '') FROM (SELECT b.id,COUNT(*) AS dd,b.carrier_user_id,b.start_city,b.`START_PROVINCE`,b.`START_AREA`,b.start_address,b.`END_PROVINCE`,b.`END_CITY`,b.`END_AREA`,b.end_address FROM t_trans_order b GROUP BY b.carrier_user_id,b.start_city,b.`START_PROVINCE`,b.`START_AREA`,b.`END_PROVINCE`,b.`END_CITY`,b.`END_AREA`) cc ORDER BY cc.carrier_user_id,cc.dd DESC) tb
) ace
HAVING ace.row_number <= 2) blood;
mysql mybatis 分组取出表中使用次数最多的前两条记录
最新推荐文章于 2024-07-24 11:55:50 发布