如何获取 4 个不同值的 ID(2 个键的组合) [英] How to get Ids of 4 distinct values ( combination of 2 keys )
问题描述
我得到了如下表
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去...)
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 和 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
);
您应该了解 group by 或 distinct 部分对性能的影响很大.如果有一个单独的表，其中包含唯一的 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 个键的组合)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
