MySQL分组后取最早3条数据SQL

DBA圈 2021-04-13

842

目标表：t_vcar_cus_device（目前有数据）

需求：将t_vcar_cus_device中数据导入t_vcar_cus_device ，customer_id 存在的不导入，且t_vcar_cus_device中customer_id 存在多条只取最早的三条
customer_id 不为-1 device_uuid 分别查device_type Android iOS 大于三条取前三条

t_vcar_cus_device

CREATE TABLE `t_vcar_cus_device` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增',
  `customer_id` bigint(20) NOT NULL COMMENT '用户ID',
  `term_type` varchar(36) NOT NULL COMMENT 'IOS,Android,WebChat',
  `term_uuid` varchar(255) NOT NULL COMMENT '终端唯一标识',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否有效',
  `created_time` datetime NOT NULL COMMENT '创建时间',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_term_uuid` (`term_uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=788 DEFAULT CHARSET=utf8 COMMENT='用户终端设备'

表结构

-- 取前三条类型为：Android

-- INSERT INTO t_vcar_cus_device (customer_id,term_type,term_uuid,created_time,updated_time)
SELECT * FROM(
SELECT * FROM (
SELECT customer_id,device_type,device_uuid,created_time,updated_time FROM (SELECT * FROM t_vcar_device_info WHERE customer_id !=-1 AND device_type='Android' AND record_type='LOGIN' AND device_uuid !='') a WHERE 3 > (SELECT COUNT(1) FROM (SELECT * FROM t_vcar_device_info WHERE customer_id !=-1 AND device_type='Android' AND record_type='LOGIN' AND device_uuid !='' )b
WHERE b.customer_id = a.customer_id AND b.id < a.id)
)a WHERE customer_id NOT IN(SELECT customer_id FROM `t_vcar_cus_device` WHERE term_type='Android')
)aa
-- 取前三条类型为：iOS

-- INSERT INTO t_vcar_cus_device (customer_id,term_type,term_uuid,created_time,updated_time)
SELECT * FROM(
SELECT * FROM
(
SELECT customer_id,device_type,device_uuid,created_time,updated_time FROM (SELECT * FROM t_vcar_device_info WHERE customer_id !=-1 AND device_type='iOS' AND record_type='LOGIN' AND device_uuid !='') a WHERE 3 > (SELECT COUNT(1) FROM (SELECT * FROM t_vcar_device_info WHERE customer_id !=-1 AND device_type='iOS' AND record_type='LOGIN' AND device_uuid !='' )b
WHERE b.customer_id = a.customer_id AND b.id < a.id)
)a WHERE customer_id NOT IN(SELECT customer_id FROM `t_vcar_cus_device` WHERE term_type='iOS')
)aa

1、WHERE 3 > (SELECT COUNT(1) FROM (SELECT * FROM t_vcar_device_info WHERE customer_id !=-1 AND device_type='iOS' AND record_type='LOGIN' AND device_uuid !='' ) 取前三的条件

2、过滤条件 a、b 都需要加上

3、可以参考：
SELECT * FROM emp AS a WHERE 3> (SELECT COUNT(*) FROM emp WHERE deptno = a.deptno AND sal < a.sal ) ORDER BY a.deptno ,a.sal

数据库

最后修改时间：2021-04-13 17:42:28

文章转载自DBA圈，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL分组后取最早3条数据SQL

评论