如何将多个序列号添加到 MySQL 中的一个 sql 视图中

发布于 2024-10-16 08:01:14 字数 431 浏览 3 评论 0原文

我想做的是将多个序列号添加到 MySQL 中的一个 sql 视图中。考虑下面的示例表：

Folder 1, File 1
Folder 1, File 2
Folder 2, File 3
Folder 3, File 4
Folder 3, File 5
Folder 3, File 6

我想要得到的结果是：

1, Folder 1, File 1
2, Folder 1, File 2
1, Folder 2, File 3
1, Folder 3, File 4
2, Folder 3, File 5
3, Folder 3, File 6

我知道如何使用变量将一个序列号添加到整个视图，但我不知道如何解决我的具体问题。希望有人能帮助我。

多谢！

干杯，约翰内斯

原文

what I would like to do is to add several sequence numbers to one single sql view in MySQL. Consider
the following example table:

Folder 1, File 1
Folder 1, File 2
Folder 2, File 3
Folder 3, File 4
Folder 3, File 5
Folder 3, File 6

What I would like to get as a result would be:

1, Folder 1, File 1
2, Folder 1, File 2
1, Folder 2, File 3
1, Folder 3, File 4
2, Folder 3, File 5
3, Folder 3, File 6

I know how I can add one single sequence number to the whole view using variables but I have no idea how to solve my specific problem. Hopefully someone can help me with that.

Thanks a lot!

Cheers,
Johannes

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

感受沵的脚步 2024-10-23 08:01:14

您不需要使用一个变量，而是需要两个变量，只要组发生变化，就重置行编号。

select folder, file,
  @r = case when @g = folder then @r+1 else 1 end SequenceNo,
  @g := folder
from (select @g:=null) g
cross join tbl
order by folder, file

Instead of using one single variable you will need two, reset the row numbering whenever the group changes.

select folder, file,
  @r = case when @g = folder then @r+1 else 1 end SequenceNo,
  @g := folder
from (select @g:=null) g
cross join tbl
order by folder, file

回复收藏 0 原文

~没有更多了~