首页 / 技术问答 / Mysql问答列表 / 问答详情

Mysql 如何计算超过特定数字的所有不同ID组，然后返回这些ID

Introduction:个

我想为用户可以参加的技术测试设置一个限制.一旦超过这一限制，我想删除剩余的部分，它们将代表最早提交的技术测试. 先进先出的系统类型.

Detail:个

MySQL 5.5

使用以下SQL:

/*
Navicat MySQL Data Transfer

Source Server         : local
Source Server Version : 50562
Source Host           : localhost:3306
Source Database       : devtester-blog

Target Server Type    : MYSQL
Target Server Version : 50562
File Encoding         : 65001

Date: 2024-01-08 20:50:01
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tbluserquestion
-- ----------------------------
DROP TABLE IF EXISTS `tbluserquestion`;
CREATE TABLE `tbluserquestion` (
  `UserQuestion_ID` int(11) NOT NULL AUTO_INCREMENT,
  `User_ID` int(11) DEFAULT NULL,
  `Question_ID` int(11) DEFAULT NULL,
  `Technical_test_uuid` varchar(255) DEFAULT NULL,
  `Answer_text` longtext,
  `Duration` int(11) DEFAULT '10',
  `Submission_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`UserQuestion_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1663 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbluserquestion
-- ----------------------------
INSERT INTO `tbluserquestion` VALUES ('23', '1', '36', '6f21885b-fe56-0a35-3dd181707f656b08', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('24', '1', '37', '6f21885b-fe56-0a35-3dd181707f656b08', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('25', '1', '43', '6f21885b-fe56-0a35-3dd181707f656b08', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('26', '1', '41', '6f21885b-fe56-0a35-3dd181707f656b08', 'async', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('27', '1', '38', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('28', '1', '40', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('29', '1', '41', '6f34ce31-987b-cb1e-a676985c54114a46', 'asyncXXXX', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('30', '1', '42', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('31', '1', '45', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('44', '1', '37', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('45', '1', '39', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('46', '1', '40', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('47', '1', '42', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('48', '1', '43', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('188', '1', '43', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('189', '1', '45', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('190', '1', '47', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('191', '1', '46', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('192', '1', '42', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('193', '1', '43', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('194', '1', '42', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('195', '1', '44', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', 'nav', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('196', '1', '45', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('197', '1', '46', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('198', '1', '46', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('199', '1', '47', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('200', '1', '45', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('201', '1', '44', 'acbe4c54-974e-5a75-fd0a74fb06c69171', 'nav', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('202', '1', '42', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:35');
INSERT INTO `tbluserquestion` VALUES ('733', '1', '37', '14c7e6ed-ff42-110b-dc4cbaa2e34f80c5', '', '10', '2023-12-24 11:54:33');
INSERT INTO `tbluserquestion` VALUES ('734', '1', '36', '14c7e6ed-ff42-110b-dc4cbaa2e34f80c5', '', '10', '2023-12-24 11:54:33');
INSERT INTO `tbluserquestion` VALUES ('735', '1', '41', '14c7e6ed-ff42-110b-dc4cbaa2e34f80c5', 'nav', '10', '2023-12-24 11:54:33');
INSERT INTO `tbluserquestion` VALUES ('736', '1', '39', '14c7e6ed-ff42-110b-dc4cbaa2e34f80c5', '', '10', '2023-12-24 11:54:33');
INSERT INTO `tbluserquestion` VALUES ('737', '1', '38', '14c7e6ed-ff42-110b-dc4cbaa2e34f80c5', '', '10', '2023-12-24 11:54:33');
INSERT INTO `tbluserquestion` VALUES ('738', '1', '40', '14d8c247-fa69-7dff-72fc601dadb06df9', '', '10', '2023-12-24 11:56:23');
INSERT INTO `tbluserquestion` VALUES ('739', '1', '38', '14d8c247-fa69-7dff-72fc601dadb06df9', '', '10', '2023-12-24 11:56:23');
INSERT INTO `tbluserquestion` VALUES ('740', '1', '39', '14d8c247-fa69-7dff-72fc601dadb06df9', '', '10', '2023-12-24 11:56:23');
INSERT INTO `tbluserquestion` VALUES ('741', '1', '36', '14d8c247-fa69-7dff-72fc601dadb06df9', '', '10', '2023-12-24 11:56:23');
INSERT INTO `tbluserquestion` VALUES ('742', '1', '41', '14d8c247-fa69-7dff-72fc601dadb06df9', 'async', '10', '2023-12-24 11:56:23');
INSERT INTO `tbluserquestion` VALUES ('1198', '1', '40', 'eba21284-9a25-dbbf-748ab2a62aa3e314', '', '10', '2024-01-02 15:20:02');
INSERT INTO `tbluserquestion` VALUES ('1199', '1', '37', 'eba21284-9a25-dbbf-748ab2a62aa3e314', '', '10', '2024-01-02 15:20:02');
INSERT INTO `tbluserquestion` VALUES ('1200', '1', '38', 'eba21284-9a25-dbbf-748ab2a62aa3e314', '', '10', '2024-01-02 15:20:02');
INSERT INTO `tbluserquestion` VALUES ('1201', '1', '39', 'eba21284-9a25-dbbf-748ab2a62aa3e314', '', '10', '2024-01-02 15:20:02');
INSERT INTO `tbluserquestion` VALUES ('1202', '1', '41', 'eba21284-9a25-dbbf-748ab2a62aa3e314', 'async', '10', '2024-01-02 15:20:02');
INSERT INTO `tbluserquestion` VALUES ('1213', '1', '41', 'effd132a-991e-e99c-236c68dc5f167090', 'async', '10', '2024-01-02 17:21:49');
INSERT INTO `tbluserquestion` VALUES ('1214', '1', '39', 'effd132a-991e-e99c-236c68dc5f167090', '', '10', '2024-01-02 17:21:49');
INSERT INTO `tbluserquestion` VALUES ('1215', '1', '38', 'effd132a-991e-e99c-236c68dc5f167090', '', '10', '2024-01-02 17:21:49');
INSERT INTO `tbluserquestion` VALUES ('1216', '1', '37', 'effd132a-991e-e99c-236c68dc5f167090', '', '10', '2024-01-02 17:21:49');
INSERT INTO `tbluserquestion` VALUES ('1217', '1', '36', 'effd132a-991e-e99c-236c68dc5f167090', '', '10', '2024-01-02 17:21:49');
INSERT INTO `tbluserquestion` VALUES ('1218', '1', '39', 'f01edf85-c805-dfa1-c0f9d8ece9b2bf20', '', '10', '2024-01-02 17:25:31');
INSERT INTO `tbluserquestion` VALUES ('1219', '1', '36', 'f01edf85-c805-dfa1-c0f9d8ece9b2bf20', '', '10', '2024-01-02 17:25:31');
INSERT INTO `tbluserquestion` VALUES ('1220', '1', '38', 'f01edf85-c805-dfa1-c0f9d8ece9b2bf20', '', '10', '2024-01-02 17:25:31');
INSERT INTO `tbluserquestion` VALUES ('1221', '1', '37', 'f01edf85-c805-dfa1-c0f9d8ece9b2bf20', '', '10', '2024-01-02 17:25:31');
INSERT INTO `tbluserquestion` VALUES ('1222', '1', '41', 'f01edf85-c805-dfa1-c0f9d8ece9b2bf20', 'async', '10', '2024-01-02 17:25:31');
INSERT INTO `tbluserquestion` VALUES ('1313', '1', '45', 'f535c6d7-c4c9-1006-ef99f2bffb0df9d7', '', '10', '2024-01-02 19:47:49');
INSERT INTO `tbluserquestion` VALUES ('1314', '1', '47', 'f535c6d7-c4c9-1006-ef99f2bffb0df9d7', '', '10', '2024-01-02 19:47:49');
INSERT INTO `tbluserquestion` VALUES ('1315', '1', '42', 'f535c6d7-c4c9-1006-ef99f2bffb0df9d7', '', '10', '2024-01-02 19:47:49');
INSERT INTO `tbluserquestion` VALUES ('1316', '1', '46', 'f535c6d7-c4c9-1006-ef99f2bffb0df9d7', '', '10', '2024-01-02 19:47:49');
INSERT INTO `tbluserquestion` VALUES ('1317', '1', '43', 'f535c6d7-c4c9-1006-ef99f2bffb0df9d7', '', '10', '2024-01-02 19:47:49');
INSERT INTO `tbluserquestion` VALUES ('1328', '1', '46', 'f57b0d9a-972a-61c3-84f7bc1463a71124', '', '10', '2024-01-02 19:55:23');
INSERT INTO `tbluserquestion` VALUES ('1329', '1', '45', 'f57b0d9a-972a-61c3-84f7bc1463a71124', '', '10', '2024-01-02 19:55:23');
INSERT INTO `tbluserquestion` VALUES ('1330', '1', '44', 'f57b0d9a-972a-61c3-84f7bc1463a71124', 'nav', '10', '2024-01-02 19:55:23');
INSERT INTO `tbluserquestion` VALUES ('1331', '1', '42', 'f57b0d9a-972a-61c3-84f7bc1463a71124', '', '10', '2024-01-02 19:55:23');
INSERT INTO `tbluserquestion` VALUES ('1332', '1', '43', 'f57b0d9a-972a-61c3-84f7bc1463a71124', '', '10', '2024-01-02 19:55:23');
SET FOREIGN_KEY_CHECKS=1;

Objective:个

如果DISTINCT Technical_test_uuids的数量大于10，我将如何编写返回N个DISTINCT Technical_test_uuid的SQL查询.因此，如果有13组Technical_test_uuid，那么将返回3个DISTINCT Technical_test_uuid.这3条记录将代表最早的Technical_test_uuid到Submission_date

在这张表中，有13个不同的Technical_test_uuids个组.

Query:个

这是我的try :

SELECT Technical_test_uuid
FROM tblUserQuestion uq 
WHERE 
    Technical_test_uuid IN (
        SELECT Technical_test_uuid  
        FROM tblUserQuestion
        WHERE User_ID = uq.User_ID AND Technical_test_uuid IN (
            SELECT DISTINCT(Technical_test_uuid)
            FROM tblUserQuestion
            WHERE User_ID = uq.User_ID
            HAVING COUNT(DISTINCT(Technical_test_uuid)) > 10
        )
    )
GROUP BY uq.Technical_test_uuid

但是它只返回一个结果，而实际上它应该返回3

Technical_test_uuid
6f21885b-fe56-0a35-3dd181707f656b08

我预计会有以下结果:

Technical_test_uuid
6f21885b-fe56-0a35-3dd181707f656b08
6f34ce31-987b-cb1e-a676985c54114a46
ff38ad50-e835-447a-49e220375e3f7769

请理解，我想要数表中所有不同的Technical_test_uuid，而不是每组中的Technical_test_uuid的数量？

UPDATE:. 09.01.2024.

非常感谢@Barmar和@blabla_bingo的解决方案. 最后，我 Select 了@blabla_bingo解决方案，因为它可以与MySQL5.5一起使用. @Barmar提供的解决方案要简单得多，如果您有一个更现代的MySQL引擎，它也可以工作. 如果用户具有比NLIMIT值更明显的Technical_TEST_UUID，但不符合我的要求，如果用户具有比NLIMIT值更不明显的Technical_TEST_UUID，则@blabla_bingo解决方案可以工作.

然而，我发现了一个非常简单的解决方案，可以同时满足这两个要求:

select * 
from (select distinct user_id,technical_test_uuid
     from tbluserquestion t
     where user_id in (select user_id from tbluserquestion group by user_id having count(distinct technical_test_uuid) <= 0)
     group by user_id,technical_test_uuid
     UNION
     select tx.user_id,technical_test_uuid
     from (select user_id,technical_test_uuid,
            if(user_id=@user_id, @row_id:=@row_id+1,@row_id:=1) as row_id,if(user_id=@user_id,@user_id,@user_id:=user_id) as u_id 
            from (select user_id,technical_test_uuid
                  from tbluserquestion tb
                  where user_id in (select user_id from tbluserquestion group by user_id having count(distinct technical_test_uuid) > 5)
                  group by user_id,technical_test_uuid
                  order by user_id,min(submission_date)
                  ) t1, (select @user_id:=0,@row_id:=1) t2
            ) tx
     join (select t1.i*10+t0.i+1 as u_id
            from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
                 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1
           order by u_id
          ) return_id
     on tx.row_id=return_id.u_id
     join (select user_id,count(distinct technical_test_uuid) dist_row_count from tbluserquestion group by user_id) tbl_row_count
     on tx.user_id=tbl_row_count.user_id and tx.row_id<=tbl_row_count.dist_row_count - 5
) tbl
order by user_id

我所做的只是将第一个NLIMIT值加零:

where user_id in (select user_id from tbluserquestion group by user_id having count(distinct technical_test_uuid) <= 0)

我现在有accepted个@blabla_bingo解决方案:)

推荐答案

我们可以使用UNION来区分这两种情况(不同Query_uuid<；个数为10的用户和不同Query_uuid<；个数=10的用户).但首先，让我们在原始INSERT语句的末尾再添加3个user_id:

INSERT INTO `tbluserquestion` VALUES ('10023', '2', '36', 'user_2_01', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10024', '2', '37', 'user_2_01', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10025', '2', '43', 'user_2_02', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10026', '2', '41', 'user_2_02', 'async', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10027', '2', '38', 'user_2_03', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10028', '2', '40', 'user_2_03', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10029', '2', '41', 'user_2_03', 'asyncXXXX', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10030', '3', '42', 'user_3_01', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10031', '3', '45', 'user_3_01', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10044', '3', '37', 'user_3_02', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10045', '3', '39', 'user_3_03', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10046', '3', '40', 'user_3_04', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10047', '3', '42', 'user_3_05', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10048', '3', '43', 'user_3_06', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('100188', '3', '43', 'user_3_07', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100189', '3', '45', 'user_3_08', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100190', '3', '47', 'user_3_09', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100191', '3', '46', 'user_3_10', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100192', '3', '42', 'user_3_11', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100193', '3', '43', 'user_3_12', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100194', '4', '42', 'user_4_01', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100195', '4', '44', 'user_4_01', 'nav', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100196', '4', '45', 'user_4_02', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100197', '4', '46', 'user_4_02', '', '10', '2023-12-22 11:23:59');

select distinct user_id,technical_test_uuid from tbluserquestion ;
-- result (with user_id 1 and 3 have over 10 distinct test_uuid):
+---------+-------------------------------------+
| user_id | technical_test_uuid                 |
+---------+-------------------------------------+
|       1 | 6f21885b-fe56-0a35-3dd181707f656b08 |
|       1 | 6f34ce31-987b-cb1e-a676985c54114a46 |
|       1 | ff38ad50-e835-447a-49e220375e3f7769 |
|       1 | ac9dad15-f677-8816-50eba2056f77e9ce |
|       1 | acafb668-aa4a-fa1e-a91ab2a0f6005d7f |
|       1 | acbe4c54-974e-5a75-fd0a74fb06c69171 |
|       1 | 14c7e6ed-ff42-110b-dc4cbaa2e34f80c5 |
|       1 | 14d8c247-fa69-7dff-72fc601dadb06df9 |
|       1 | eba21284-9a25-dbbf-748ab2a62aa3e314 |
|       1 | effd132a-991e-e99c-236c68dc5f167090 |
|       1 | f01edf85-c805-dfa1-c0f9d8ece9b2bf20 |
|       1 | f535c6d7-c4c9-1006-ef99f2bffb0df9d7 |
|       1 | f57b0d9a-972a-61c3-84f7bc1463a71124 |
|       2 | user_2_01                           |
|       2 | user_2_02                           |
|       2 | user_2_03                           |
|       3 | user_3_01                           |
|       3 | user_3_02                           |
|       3 | user_3_03                           |
|       3 | user_3_04                           |
|       3 | user_3_05                           |
|       3 | user_3_06                           |
|       3 | user_3_07                           |
|       3 | user_3_08                           |
|       3 | user_3_09                           |
|       3 | user_3_10                           |
|       3 | user_3_11                           |
|       3 | user_3_12                           |
|       4 | user_4_01                           |
|       4 | user_4_02                           |
+---------+-------------------------------------+

以下是执行此操作的查询.由于MySQL5.5不支持窗口函数，我们需要使用用户变量来生成行号.这是一个棘手的问题.

select * 
from (select distinct user_id,technical_test_uuid
     from tbluserquestion t
     where user_id in (select user_id from tbluserquestion group by user_id having count(distinct technical_test_uuid) <=10)
     group by user_id,technical_test_uuid
     UNION
     select user_id,technical_test_uuid
     from (select user_id,technical_test_uuid,
            if(user_id=@user_id, @row_id:=@row_id+1,@row_id:=1) as row_id,if(user_id=@user_id,@user_id,@user_id:=user_id) as u_id 
            from (select user_id,technical_test_uuid
                  from tbluserquestion
                  where user_id in (select user_id from tbluserquestion group by user_id having count(distinct technical_test_uuid) >10)
                  group by user_id,technical_test_uuid
                  order by user_id,min(submission_date)) t1, (select @user_id:=0,@row_id:=1) t2
            ) tx
     join (select 1 u_id union select 2 union select 3) limit_3
     on tx.row_id=limit_3.u_id
) tbl
order by user_id
;

-- result set:
+---------+-------------------------------------+
| user_id | technical_test_uuid                 |
+---------+-------------------------------------+
|       1 | 6f21885b-fe56-0a35-3dd181707f656b08 |
|       1 | 6f34ce31-987b-cb1e-a676985c54114a46 |
|       1 | ff38ad50-e835-447a-49e220375e3f7769 |
|       2 | user_2_01                           |
|       2 | user_2_02                           |
|       2 | user_2_03                           |
|       3 | user_3_01                           |
|       3 | user_3_02                           |
|       3 | user_3_03                           |
|       4 | user_4_01                           |
|       4 | user_4_02                           |
+---------+-------------------------------------+

看到https://www.db-fiddle.com/f/6S8RTqevYA4UHD8tba24mU/0

UPDATED SECTION
基于OP的 comments ，从最早的提交日期开始，针对用户返回的不同UUID的数量由(用户minus的不同UUID的数量，LIMIT_NUMBER)确定.
由于我们知道LIMIT_NUMBER会有所不同(返回数字也会有所不同)，因此前面的(select 1 u_id union select 2 union select 3) limit_3不再符合条件.我们将需要一个Return_id表，该表应该涵盖用户的不同UUID的最高可能数量.

-- supposing the highest can be no more than 100
select t1.i*10+t0.i+1 as u_id
from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1
order by u_id     
;
-- result 
+------+
| u_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
|   12 |

-- all the way to 100

|   95 |
|   96 |
|   97 |
|   98 |
|   99 |
|  100 |
+------+

现在让我们重新构建这张桌子.

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tbluserquestion
-- ----------------------------
DROP TABLE IF EXISTS `tbluserquestion`;
CREATE TABLE `tbluserquestion` (
  `UserQuestion_ID` int(11) NOT NULL AUTO_INCREMENT,
  `User_ID` int(11) DEFAULT NULL,
  `Question_ID` int(11) DEFAULT NULL,
  `Technical_test_uuid` varchar(255) DEFAULT NULL,
  `Answer_text` longtext,
  `Duration` int(11) DEFAULT '10',
  `Submission_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`UserQuestion_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1663 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbluserquestion
-- ----------------------------
INSERT INTO `tbluserquestion` VALUES ('23', '1', '36', '6f21885b-fe56-0a35-3dd181707f656b08', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('24', '1', '37', '6f21885b-fe56-0a35-3dd181707f656b08', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('25', '1', '43', '6f21885b-fe56-0a35-3dd181707f656b08', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('26', '1', '41', '6f21885b-fe56-0a35-3dd181707f656b08', 'async', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('27', '1', '38', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('28', '1', '40', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('29', '1', '41', '6f34ce31-987b-cb1e-a676985c54114a46', 'asyncXXXX', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('30', '1', '42', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('31', '1', '45', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('44', '1', '37', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('45', '1', '39', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('46', '1', '40', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('47', '1', '42', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('48', '1', '43', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('188', '1', '43', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('189', '1', '45', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('190', '1', '47', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('191', '1', '46', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('192', '1', '42', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('193', '1', '43', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('194', '1', '42', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('195', '1', '44', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', 'nav', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('196', '1', '45', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('197', '1', '46', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('198', '1', '46', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('199', '1', '47', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('200', '1', '45', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('201', '1', '44', 'acbe4c54-974e-5a75-fd0a74fb06c69171', 'nav', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('202', '1', '42', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:35');

INSERT INTO `tbluserquestion` VALUES ('10023', '2', '36', 'user_2_01', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10024', '2', '37', 'user_2_01', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10025', '2', '43', 'user_2_02', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10026', '2', '41', 'user_2_03', 'async', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10027', '2', '38', 'user_2_04', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10028', '2', '40', 'user_2_05', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10029', '2', '41', 'user_2_06', 'asyncXXXX', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10030', '2', '41', 'user_2_07', 'asyncXXXX', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10031', '2', '41', 'user_2_07', 'asyncXXXX', '10', '2023-10-17 16:46:34');

INSERT INTO `tbluserquestion` VALUES ('10032', '3', '42', 'user_3_01', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10033', '3', '45', 'user_3_01', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10044', '3', '37', 'user_3_02', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10045', '3', '39', 'user_3_03', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10046', '3', '40', 'user_3_04', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10047', '3', '42', 'user_3_05', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10048', '3', '43', 'user_3_06', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('100188', '3', '43', 'user_3_07', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100189', '3', '45', 'user_3_08', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100190', '3', '47', 'user_3_09', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100191', '3', '46', 'user_3_10', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100192', '3', '42', 'user_3_11', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100193', '3', '43', 'user_3_12', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100194', '4', '42', 'user_4_01', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100195', '4', '44', 'user_4_01', 'nav', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100196', '4', '45', 'user_4_02', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100197', '4', '46', 'user_4_02', '', '10', '2023-12-22 11:23:59');

select distinct user_id,technical_test_uuid from tbluserquestion ;
+---------+-------------------------------------+
| user_id | technical_test_uuid                 |
+---------+-------------------------------------+
|       1 | 6f21885b-fe56-0a35-3dd181707f656b08 |
|       1 | 6f34ce31-987b-cb1e-a676985c54114a46 |
|       1 | ff38ad50-e835-447a-49e220375e3f7769 |
|       1 | ac9dad15-f677-8816-50eba2056f77e9ce |
|       1 | acafb668-aa4a-fa1e-a91ab2a0f6005d7f |
|       1 | acbe4c54-974e-5a75-fd0a74fb06c69171 |
|       2 | user_2_01                           |
|       2 | user_2_02                           |
|       2 | user_2_03                           |
|       2 | user_2_04                           |
|       2 | user_2_05                           |
|       2 | user_2_06                           |
|       2 | user_2_07                           |
|       3 | user_3_01                           |
|       3 | user_3_02                           |
|       3 | user_3_03                           |
|       3 | user_3_04                           |
|       3 | user_3_05                           |
|       3 | user_3_06                           |
|       3 | user_3_07                           |
|       3 | user_3_08                           |
|       3 | user_3_09                           |
|       3 | user_3_10                           |
|       3 | user_3_11                           |
|       3 | user_3_12                           |
|       4 | user_4_01                           |
|       4 | user_4_02                           |
+---------+-------------------------------------+

如上所示，这次用户1有6个不同的UUID，而用户2有7个，用户3有12个，用户4有2个.现在让我们把这项工作一劳永逸地做完.

select * 
from (select distinct user_id,technical_test_uuid
     from tbluserquestion t
     where user_id in (select user_id from tbluserquestion group by user_id having count(distinct technical_test_uuid) <=5)
     group by user_id,technical_test_uuid
     UNION
     select tx.user_id,technical_test_uuid
     from (select user_id,technical_test_uuid,
            if(user_id=@user_id, @row_id:=@row_id+1,@row_id:=1) as row_id,if(user_id=@user_id,@user_id,@user_id:=user_id) as u_id 
            from (select user_id,technical_test_uuid
                  from tbluserquestion tb
                  where user_id in (select user_id from tbluserquestion group by user_id having count(distinct technical_test_uuid) >5)
                  group by user_id,technical_test_uuid
                  order by user_id,min(submission_date)
                  ) t1, (select @user_id:=0,@row_id:=1) t2
            ) tx
     join (select t1.i*10+t0.i+1 as u_id
            from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
                 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1
           order by u_id
          ) return_id
     on tx.row_id=return_id.u_id
     join (select user_id,count(distinct technical_test_uuid) dist_row_count from tbluserquestion group by user_id) tbl_row_count
     on tx.user_id=tbl_row_count.user_id and tx.row_id<=tbl_row_count.dist_row_count - 5
) tbl
order by user_id
;
-- result:
+---------+-------------------------------------+
| user_id | technical_test_uuid                 |
+---------+-------------------------------------+
|       1 | 6f21885b-fe56-0a35-3dd181707f656b08 |
|       2 | user_2_01                           |
|       2 | user_2_02                           |
|       3 | user_3_01                           |
|       3 | user_3_02                           |
|       3 | user_3_03                           |
|       3 | user_3_04                           |
|       3 | user_3_05                           |
|       3 | user_3_06                           |
|       3 | user_3_07                           |
|       4 | user_4_01                           |
|       4 | user_4_02                           |
+---------+-------------------------------------+

我还在https://www.db-fiddle.com/f/skyWhkYozUfYP4dMJ8CW3W/0台上进行了测试

Mysql相关问答推荐

MySQL问题难以将文本字符串转换为正确的日期格式

找出同一表中列A中的每个值在列B中出现的次数

如何为Oracle DB查询获得所需的GROUP BY结果？

比较2个mysql json数据类型列

SQL：如何为给定组中的所有记录 Select 一列与另一列不匹配的位置

优化解析 5000 万行 MySQL 表的请求

使用来自另一个表 mysql 的值将新行插入到表中

基于关系的每个实体有一个真值和多个假值

MySQL - 如何查询涉及前面计算值的表结果

Mysql，显示谁已经和没有 Select 退出巴士服务

根据使用 mysql 的第一个过滤结果添加更多表行

如何根据特定条件从mysql数据库中 Select 查询

结果差异(MySQL 5.7 vs MySQL 8.0)

动态创建内联 SQL 表(用于排除左连接)

MySQL使用多列 Select 重复记录

安装 mysql-python (Windows)

Sequelize Query 查找日期范围内的所有记录

如何让mysql自动启动？ (仅限 linux-cli)

MySQL - 错误 1045 - 访问被拒绝

MySQL讲解查询理解

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630