我得到如下表
mysql> select * from tb_dts;
+----+------+------+
| Id | key1 | key2 |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 1 |
| 4 | 2 | 1 |
| 5 | 2 | 1 |
| 6 | 2 | 1 |
| 7 | 2 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 2 |
| 10 | 1 | 2 |
| 11 | 1 | 2 |
| 12 | 1 | 2 |
| 13 | 3 | 1 |
| 14 | 3 | 1 |
| 15 | 3 | 1 |
| 16 | 3 | 1 |
| 17 | 2 | 2 |
| 18 | 2 | 2 |
| 19 | 2 | 2 |
| 20 | 2 | 3 |
| 21 | 2 | 3 |
| 22 | 2 | 3 |
| 23 | 3 | 2 |
| 24 | 3 | 2 |
| 25 | 3 | 2 |
| 26 | 3 | 2 |
+----+------+------+
26 rows in set (0.00 sec)
我采用这样的不同值,在某些应用程序分页中使用
mysql> select distinct key1,key2 from tb_dts limit 0,4;
+------+------+
| key1 | key2 |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
+------+------+
4 rows in set (0.00 sec)
mysql> select distinct key1,key2 from tb_dts limit 4,4;
+------+------+
| key1 | key2 |
+------+------+
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
+------+------+
3 rows in set (0.00 sec)
Through group_concat
我也得到了 Id,但是,我想在中使用这个 IdWHERE Field IN
类似子句where somefield IN ( ..here my Ids goes...)
mysql> select key1,key2,group_concat(Id) from tb_dts group by key1,key2 limit 0,4;
+------+------+------------------+
| key1 | key2 | group_concat(Id) |
+------+------+------------------+
| 1 | 1 | 1,2,3 |
| 1 | 2 | 8,9,10,11,12 |
| 2 | 1 | 4,5,6,7 |
| 2 | 2 | 17,18,19 |
+------+------+------------------+
4 rows in set (0.00 sec)
mysql> select key1,key2,group_concat(Id) from tb_dts group by key1,key2 limit 4,4;
+------+------+------------------+
| key1 | key2 | group_concat(Id) |
+------+------+------------------+
| 2 | 3 | 20,21,22 |
| 3 | 1 | 13,14,15,16 |
| 3 | 2 | 23,24,25,26 |
+------+------+------------------+
3 rows in set (0.00 sec)
但我该如何把它放进去WHERE Fieldname IN
条款?
我需要这样的东西,作为我的tb_dts
包含更多内容3000万记录,以及15
真实的字段,我无法使用ID BETWEEN min_id and max_id
为了处理我需要的前 4 个唯一组合值
select * from tb_dts where Id IN (1,2,3,8,9,10,11,12,4,5,6,7,17,18,19 )
为了处理接下来的 4 个唯一组合值,我需要在我的应用程序中使用 Id,所以简而言之,我希望在我的应用程序中包含下面提到的 Idwhere Field IN
clause
select * from tb_dts where Id IN (20,21,22,13,14,15,16,23,24,25,26 )
这是我的表的结构
DROP TABLE IF EXISTS `tb_dts`;
CREATE TABLE `tb_dts` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`key1` int(11) DEFAULT '-99',
`key2` int(11) DEFAULT '-99',
PRIMARY KEY (`Id`),
KEY `main` (`key1`,`key2`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;
LOCK TABLES `tb_dts` WRITE;
INSERT INTO `tb_dts` VALUES (1,1,1),(2,1,1),(3,1,1),(4,2,1),(5,2,1),(6,2,1),(7,2,1),(8,1,2),(9,1,2),(10,1,2),(11,1,2),(12,1,2),(13,3,1),(14,3,1),(15,3,1),(16,3,1),(17,2,2),(18,2,2),(19,2,2),(20,2,3),(21,2,3),(22,2,3),(23,3,2),(24,3,2),(25,3,2),(26,3,2);
UNLOCK TABLES;
正如您在此处看到的,它为每个不同的组合值提供第一个找到的 ID
mysql> select Id from tb_dts group by key1,key2 limit 0,4;
+----+
| Id |
+----+
| 1 |
| 8 |
| 4 |
| 17 |
+----+
4 rows in set (0.00 sec)
但我期待一切Ids
符合给定的标准,这只不过是所有 ID 都低于 4 个唯一值的情况
mysql> select key1,key2 from tb_dts group by key1,key2 limit 0,4;
+------+------+
| key1 | key2 |
+------+------+
| 1 | 1 | --- 1,2,3
| 1 | 2 | --- 8,9,10,11,12
| 2 | 1 | --- 4,5,6,7
| 2 | 2 | --- 17,18,19
+------+------+
4 rows in set (0.00 sec)
预期产出
我期望得到Id
像这样group by key1,key2 limit 0,4
,这样就可以在我的WHERE IN
clause.
1
2
3
8
9
10
11
12
4
5
6
7
17
18
19