mysql - 在 MYSQL 中将列拆分为定义的范围

Question

我有一个看起来像这样的表:

+-----------------------
| id     | first_name 
+-----------------------
| AC0089 | John      |
| AC0015 | Dan       |
| AC0017 | Marry     |
| AC0003 | Andy      |
| AC0001 | Trent     |
| AC0006 | Smith     |
+-----------------------

我需要一个查询来将 id 拆分为 3 的范围，并显示该范围的起始 id，即

+------------+----------+--------
| startrange | endrange | id                  
+------------+----------+--------
|          1 |       3 | AC0089 
|          4 |       6 | AC0003 
+------------+----------+--------

我是 SQL 的新手，正在尝试以下查询，但我认为我根本没有找到正确的解决方案!这是查询:

select startrange, endrange, id from table inner join (select 1 startRange, 3 endrange union all select 4 startRange, 6 endRange) r group by r.startRange, r.endRange;

它每次都提供相同的 ID，我无法想出任何其他解决方案。如何获得所需的输出？

Answer 1

试试这个

SET @ct := 0;
select startrange,(startrange + 2) as endrange, seq_no from
(select (c.st - (select count(*) from <table_name>)) as startrange,  c.* from
(select (@ct := @ct + 1) as st, b.* from <table_name> as b) c
having startrange mod 3 = 1) as cc;

抱歉格式化。