Description:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.41 |
+-----------+
1 row in set (0.00 sec)
mysql> select char_col as col1,id_col as col2 from tbl_1_all_typeindex_desc where (char_col,id_col) IN (select A.char_col as char_col,A.id_col as id_col from (select char_col,id_col from tbl_3_all_typeindex_desc where char_col IN ('aaa2','aaaa','aabb','aacd','ab',NULL)) A INNER JOIN (select char_col,id_col from tbl_1_all_type2index_desc) B on A.char_col = B.char_col and A.id_col = B.id_col where A.char_col BETWEEN 'aacd' AND 'ab') group by 1,2 having id_col <= 23 order by 1,2;
+------+------+
| col1 | col2 |
+------+------+
| aacd | 11 |
| aacd | 12 |
| aacd | 13 |
| aacd | 14 |
| aacd | 15 |
+------+------+
5 rows in set (0.02 sec)
mysql> select char_col as col1,id_col as col2 from tbl_1_all_typeindex_desc where (char_col,id_col) IN (select A.char_col as char_col,A.id_col as id_col from (select char_col,id_col from tbl_3_all_typeindex_desc where char_col IN ('aaa2','aaaa','aabb','aacd','ab',NULL)) A INNER JOIN (select char_col,id_col from tbl_1_all_type2index_desc) B on A.char_col = B.char_col and A.id_col = B.id_col where A.char_col BETWEEN 'aacd' AND 'ab') group by 1,2 having id_col <= 23 order by 1,2 limit 7 offset 4;
+------+------+
| col1 | col2 |
+------+------+
| aacd | 11 |
+------+------+
1 row in set (0.02 sec)
How to repeat:
set sql_mode="";
source tbl_1_all_typeindex_desc.sql
source tbl_3_all_typeindex_desc.sql
source tbl_1_all_type2index_desc.sql
select char_col as col1,id_col as col2 from tbl_1_all_typeindex_desc where (char_col,id_col) IN (select A.char_col as char_col,A.id_col as id_col from (select char_col,id_col from tbl_3_all_typeindex_desc where char_col IN ('aaa2','aaaa','aabb','aacd','ab',NULL)) A INNER JOIN (select char_col,id_col from tbl_1_all_type2index_desc) B on A.char_col = B.char_col and A.id_col = B.id_col where A.char_col BETWEEN 'aacd' AND 'ab') group by 1,2 having id_col <= 23 order by 1,2;
select char_col as col1,id_col as col2 from tbl_1_all_typeindex_desc where (char_col,id_col) IN (select A.char_col as char_col,A.id_col as id_col from (select char_col,id_col from tbl_3_all_typeindex_desc where char_col IN ('aaa2','aaaa','aabb','aacd','ab',NULL)) A INNER JOIN (select char_col,id_col from tbl_1_all_type2index_desc) B on A.char_col = B.char_col and A.id_col = B.id_col where A.char_col BETWEEN 'aacd' AND 'ab') group by 1,2 having id_col <= 23 order by 1,2 limit 7 offset 4;
Description: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.41 | +-----------+ 1 row in set (0.00 sec) mysql> select char_col as col1,id_col as col2 from tbl_1_all_typeindex_desc where (char_col,id_col) IN (select A.char_col as char_col,A.id_col as id_col from (select char_col,id_col from tbl_3_all_typeindex_desc where char_col IN ('aaa2','aaaa','aabb','aacd','ab',NULL)) A INNER JOIN (select char_col,id_col from tbl_1_all_type2index_desc) B on A.char_col = B.char_col and A.id_col = B.id_col where A.char_col BETWEEN 'aacd' AND 'ab') group by 1,2 having id_col <= 23 order by 1,2; +------+------+ | col1 | col2 | +------+------+ | aacd | 11 | | aacd | 12 | | aacd | 13 | | aacd | 14 | | aacd | 15 | +------+------+ 5 rows in set (0.02 sec) mysql> select char_col as col1,id_col as col2 from tbl_1_all_typeindex_desc where (char_col,id_col) IN (select A.char_col as char_col,A.id_col as id_col from (select char_col,id_col from tbl_3_all_typeindex_desc where char_col IN ('aaa2','aaaa','aabb','aacd','ab',NULL)) A INNER JOIN (select char_col,id_col from tbl_1_all_type2index_desc) B on A.char_col = B.char_col and A.id_col = B.id_col where A.char_col BETWEEN 'aacd' AND 'ab') group by 1,2 having id_col <= 23 order by 1,2 limit 7 offset 4; +------+------+ | col1 | col2 | +------+------+ | aacd | 11 | +------+------+ 1 row in set (0.02 sec) How to repeat: set sql_mode=""; source tbl_1_all_typeindex_desc.sql source tbl_3_all_typeindex_desc.sql source tbl_1_all_type2index_desc.sql select char_col as col1,id_col as col2 from tbl_1_all_typeindex_desc where (char_col,id_col) IN (select A.char_col as char_col,A.id_col as id_col from (select char_col,id_col from tbl_3_all_typeindex_desc where char_col IN ('aaa2','aaaa','aabb','aacd','ab',NULL)) A INNER JOIN (select char_col,id_col from tbl_1_all_type2index_desc) B on A.char_col = B.char_col and A.id_col = B.id_col where A.char_col BETWEEN 'aacd' AND 'ab') group by 1,2 having id_col <= 23 order by 1,2; select char_col as col1,id_col as col2 from tbl_1_all_typeindex_desc where (char_col,id_col) IN (select A.char_col as char_col,A.id_col as id_col from (select char_col,id_col from tbl_3_all_typeindex_desc where char_col IN ('aaa2','aaaa','aabb','aacd','ab',NULL)) A INNER JOIN (select char_col,id_col from tbl_1_all_type2index_desc) B on A.char_col = B.char_col and A.id_col = B.id_col where A.char_col BETWEEN 'aacd' AND 'ab') group by 1,2 having id_col <= 23 order by 1,2 limit 7 offset 4;