如何获取 4 个不同值的 ID(2 个键的组合)

2022-01-15 00:00:00 sql mariadb mysql

我得到了如下表

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
);

相关文章