MySQL-选择记录的行号 [英] MySQL - Select row number of a record

问题描述

我在MySQL中有一个填充如下的表.现在，我需要按其排序顺序选择记录的行号.例如，以'c'开头的单词的行数应为4.

I have a table in MySQL populated as follows. Now I need to select the row number of a record in its sorted order. For example, the row number of words starting with 'c' should be 4.

Words
=====
coffee
banana
apple
cherry
blackberry

我尝试了以下查询，但是得到了错误的结果.这里的dict是表名，而words是列名.

I tried the following query, but I get wrong results. Here dict is the table name and words is the column name.

SELECT @rownum:=@rownum + 1 id FROM (SELECT * FROM dict ORDER BY words) d,(SELECT @rownum:=0) r WHERE d.words LIKE CONCAT('c','%')

对于上面的查询，我正在获取外部查询的行号.但是我想要内部查询的行号.我不知道该怎么做.

For the above query, I am getting the row numbers for the outer query. But I want the row numbers of the internal query. I do not know how to get that.

感谢您的帮助.谢谢.

推荐答案

请尝试以下操作:

SET @rownum = 0;
SELECT id 
FROM (SELECT *, @rownum:=@rownum + 1 AS id FROM dict ORDER BY words) d
WHERE d.words LIKE CONCAT('c','%')

作为单个查询，请尝试以下操作:

As single query, try this:

SELECT id 
FROM (SELECT *, @rownum:=@rownum + 1 AS id FROM dict, (SELECT @rownum:=0) r ORDER BY words) d
WHERE d.words LIKE CONCAT('c','%')

这篇关于MySQL-选择记录的行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！