示例:我有一个包含列的表
______________________
|field_id|Code|seq_num|
| 1 | a | 1 |
| 1 | a | 2 |
| 1 | a | 3 |
| 2 | a | 4 |
| 2 | a | 5 |
| 3 | a | 6 |
| 3 | a | 7 |
| 3 | a | 8 |如何查询,因此序列号如下所示
_____________________
|field_id|Code|seq_num|
| 1 | a | 1 |
| 1 | a | 2 |
| 1 | a | 3 |
| 2 | a | 1 |
| 2 | a | 2 |
| 3 | a | 1 |
| 3 | a | 2 |
| 3 | a | 3 |请帮帮我!!
发布于 2018-02-21 00:38:38
一种方法是获取字段的最小序列:
select t.field_id, t.code,
(seq_num - min_seqnum + 1) as seqnum
from t join
(select field_id, min(seq_num) as min_seq_num
from t
group by field_id
) f
on t.field_id = f.field_id;如果您不相信当前序列号没有间隙,您也可以使用变量来完成此操作:
select . . .,
(@rn := if(@f = field_id, @rn + 1,
if(@f := field_id, 1, 1)
)
) as seq_no
from (select t.*
from t
order by field_id, seq_no
) t cross join
(select @f := '', @rn := 0) params;https://stackoverflow.com/questions/48890015
复制相似问题