英文:
MySQL Get multple rows for a single row in db
问题
你可以在MySQL中使用以下查询来实现这个目标：
SELECT id, (@row_number:=CASE WHEN @id=id THEN @row_number+1 ELSE 1 END) AS seq
FROM (SELECT id FROM tags ORDER BY id) AS t
JOIN (SELECT @row_number:=0, @id:=NULL) AS r;
这个查询会为每个标签ID生成一个序列号（seq），并按照ID进行排序。
英文:
I have a select query
select id from tags
Which returns data like
id
--
1
2
I want to get data with a sequence number as shown below
id seq
-- ---
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 3
2 4
2 5
How can I achieve this in mysql
答案1
得分: 2
以下是您要翻译的内容：
select id, seq
from tags
cross join (
select 1 seq union all select 2 union all select 3 union all select 4 union all select 5
) seqs
order by id, seq
或者
with recursive seqs as (
select 1 seq
union all
select seq+1
from seqs
where seq < 5
)
select id, seq
from tags
cross join seqs
order by id, seq
英文:
select id, seq
from tags
cross join (
select 1 seq union all select 2 union all select 3 union all select 4 union all select 5
) seqs
order by id, seq
Or
with recursive seqs as (
select 1 seq
union all
select seq+1
from seqs
where seq < 5
)
select id, seq
from tags
cross join seqs
order by id, seq
答案2
得分: 0
你可以按照这里指定的方式使用递增变量；
在你的情况下，查询应该像这样：
SET @rowcount=0;
SELECT id, @rowcount:=@rowcount+1 as seq from tags;
英文:
You can use an incremental variable as specified here;
In your case, the query should be like this:
SET @rowcount=0;
SELECT id, @rowcount:=@rowcount+1 as seq from tags;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论