MySQL SELECT计数器，分组依据 [英] MySQL SELECT Counter, Group By

问题描述

我不确定我要问的事情是否可能...但是可能是大声笑.

im not sure if what I am asking for is possible... but it probably is lol.

基本上，我有两个桌子，曲目和专辑.

Basically, I have two tables, tracks and albums.

我想显示所有曲目的列表，并从专辑部分获取名称.

I want to display a list of all tracks and get the name from the albums part.

表格如下:

tbl_tracks
id - int, auto increment, primary key
album_id - int
title - varchar 50

和

tbl_albums
id - int, auto increment, primary key
title - varchar 50

现在，我运行查询:

选择tbl_tracks.title，tbl_albums.title从tbl_tracks，tbl_albums其中tbl_tracks.album_id = tbl_albums.id GROUP BY tbl_tracks. c1>

SELECT tbl_tracks.title, tbl_albums.title FROM tbl_tracks, tbl_albums WHERE tbl_tracks.album_id = tbl_albums.id GROUP BY tbl_tracks.title

现在将打印出以下列表:

Now that prints out the following list:

Track1 Artist1
Track2 Artist1
Track3 Artist1
Track1 Artist2

等...

(为清晰起见，我发布的曲目名称为arnt)

(The track names arnt as posted i did that for clarity sake)

现在我要做的是选择另一列，按艺术家对每个曲目进行编号，以便上面的查询输出:

Now what i want to do is select another column that numbers each track by artist so the above query would output:

1 Track1 Artist1
2 Track2 Artist1
3 Track3 Artist1
1 Track1 Artist2

但是我想在没有其他任何列添加到数据库或没有任何服务器端循环的情况下执行此操作，

But i want to do that without any other columns added to the database or without any serverside loops, how would i do this?

推荐答案

SELECT @rn := if(@g = tbl_albums.id, @rn+1, 1) rownumber,
       tbl_tracks.title, tbl_albums.title,
  @g := tbl_albums.id
FROM (select @g:=null, @rn:=0) initvars
CROSS JOIN tbl_tracks
INNER JOIN tbl_albums on tbl_tracks.album_id = tbl_albums.id
ORDER BY tbl_albums.id, tbl_tracks.title;

这篇关于MySQL SELECT计数器，分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！