问题描述
我有如下表
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)
我采用类似的值，在某些应用程序分页中使用
I take distinct values like this, used in some application pagination
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)
通过group_concat我也获得了ID，但是，我想在WHERE Field IN子句中使用此ID，例如where somefield IN ( ..here my Ids goes...)
Through group_concat I get Ids as well but, I want to use this Ids in WHERE Field IN clause like 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子句中?
But How do I put this in WHERE Fieldname IN clause ?
我需要这样的东西，因为我的tb_dts包含了更多的 3000万记录和真实的15字段，所以我不能使用ID BETWEEN min_id and max_id
I need something like this, as my tb_dts contains more that 30 million reocrds, and 15 fields in real, I can't use ID BETWEEN min_id and max_id
要处理我需要的前4个唯一组合值
For processing first 4 unique combination values I need
select * from tb_dts where Id IN (1,2,3,8,9,10,11,12,4,5,6,7,17,18,19 )
要处理接下来的4个唯一组合值，我需要在应用程序中使用ID，因此简而言之，我想在我的where Field IN子句中包含以下提到的ID
For processing next 4 unique combination values I need Ids in my application, so in short I want to have below mentioned Ids in my where 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个唯一值而已
But I expect all Ids which falls within the criteria given, that is nothing but what all IDs falls for below 4 unique value
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)
预期o/p
我希望对group by key1,key2 limit 0,4这样获得Id，以便可以在我的WHERE IN子句中使用.
I expect to get Id like this for group by key1,key2 limit 0,4, so that this can be used in my WHERE IN clause.
1
2
3
8
9
10
11
12
4
5
6
7
17
18
19
推荐答案
对于您当前遇到的问题，可以使用find_in_set这样:
For your immediate problem, you can use find_in_set like this:
select t.*
from your_table t
where exists (
select 1 from (
select group_concat(Id)
from tb_dts
group by key1, key2
order by key1, key2 -- very important when using limit
limit 0, 4
) t2 where find_in_set(t.fieldname, t2.ids) > 0
);
尽管我不确定这是否是完成您正在做的事情的最佳方法.使用group by创建字符串，然后在该字符串中搜索会太慢.
Though I am not sure if this is the best way to do what you're doing. Creating strings using group by and then searching in that string will be too slow.
此外，您还希望在key1，key2，id列上有一个索引.
Also, you want to have an index on key1, key2, id columns.
create index idx_tb_dts on tb_dts (key1, key2, id);
可以尝试:
select t.*
from your_table t
where exists (
select 1
from tb_dts t1
inner join (
select distinct key1, key2
from tb_dts
order by key1, key2
limit 0, 4
) t2 on t1.key1 = t2.key1
and t1.key2 = t2.key2
where t1.id = t.fieldname
);
您应该了解，分组依据或不同之处可能会影响性能.如果有一个单独的表包含唯一的key1，key2并在其上具有唯一的索引，那就更好了.
You should understand that the group by or distinct part can be heavy on performance. It will be much better if there was a separate table containing unique key1, key2 with a unique index on them.
create table the_keys_table(
key1 int not null,
key2 int not null,
primary key (key1, key2)
);
然后，您可以使用以下表格替换下面的tb_dts:
Then you could replace the tb_dts in below with that table like this:
select key1, key2 -- no distinct or group by needed.
from the_keys_table
order by key1, key2
limit 0, 4
您的最终查询将变为:
select t.*
from your_table t
where exists (
select 1
from tb_dts t2
where (key1, key2) in (
select key1, key2
from the_keys_table
order by key1, key2
limit 0, 4
) and t1.id = t.fieldname
);
这篇关于如何获取4个不同值的ID(2个键的组合)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！