在mysql查询中按顺序获取序列号 [英] get serial number according order by in a mysql query

问题描述

大家好，

我需要在我的sql中查询序列号。意味着我有如下表格（表格）

 id name 
  11  ram 
  21  sita 
  31  gita 
  41  deepak 
  51  harshit 
  61  prashant 

我的查询是

 选择名称，@ acount：= @ acount + 1 serial_number 
  from （SELECT @acount：=  0 ）AS acount，user 
 order by name asc 

我需要这样的结果

 name serial_number 
 deepak  1  
 gita  2  
 harshit  3  
 prashant  4  
 ram  5  
 sita  6  

<无线电通信/>
谢谢

mohit

解决方案

嗨试试这样，



这是我为满足您的要求而编写的示例查询。

希望它有所帮助。

选择concat（name，''，@ row：= @row + 1）作为用户名，（SELECT @row：= 0）r order by @row：= @row; 
选择名称作为名称，@ row：= @row + 1作为来自用户的SNo，（SELECT @row：= 0）r order by @row：= @row; 

 选择名称，row_number（） over （ order   by  Id） as  serial_number 来自 用户 

我用过的其他方式限制0,10然后11,10等....

hi all ,
i need serial number in order by query in my sql. Means i have table like following(table)

id   name
11   ram
21   sita
31   gita
41   deepak
51   harshit
61   prashant

my query is

select name,@acount:=@acount+1 serial_number
from (SELECT @acount:= 0) AS acount,user
order by name asc

and i need result like this

name     serial_number
deepak   1
gita     2
harshit  3
prashant 4
ram      5
sita     6

thanks
mohit

解决方案

Hi try like this,

This is the sample query i have written to achieve your requirement.
Hope it helps.

select concat(name,' ',@row := @row + 1) as Name from user,(SELECT @row := 0) r order by @row := @row;
select name as Name,@row := @row + 1 as SNo from user,(SELECT @row := 0) r order by @row := @row;

select name,row_number() over(order by Id) as serial_number from user

i used other way limit 0,10 then 11,10 so on....

这篇关于在mysql查询中按顺序获取序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！