sql中自定义变量的使用场景（显示行号）

1.

sql中自定义变量的使用场景（显示行号）

select * from boys ;
# +--+----+---+---+
# |id|name|sex|age|
# +--+----+---+---+
# |1 |慕容皝 |男  |22 |
# |2 |慕容冲 |男  |33 |
# |3 |慕容垂 |男  |44 |
# |4 |慕容博 |男  |55 |
# |5 |慕容雪 |女  |55 |
# +--+----+---+---+# 一个会话有效
select @curRow := @curRow + 1 AS row_num,name,age
from boysJOIN (SELECT @curRow := 0) r
where sex = '男'
# +-------+----+---+
# |row_num|name|age|
# +-------+----+---+
# |1      |慕容博 |55 |
# |2      |慕容垂 |44 |
# |3      |慕容冲 |33 |
# |4      |慕容皝 |22 |
# +-------+----+---+union  allselect @curRow := @curRow + 1 AS row_num,name,age
from boysJOIN (SELECT @curRow := 0) r
where sex = '女';#+-------+----+---+
# |row_num|name|age|
# +-------+----+---+
# |1      |慕容雪 |55 |
# +-------+----+---+#  将上面两个结果 union all  以下 ，自定义变量是同一个范围内有效，所有会 将最终的结果,进行 加 序号select @curRow := @curRow + 1 AS row_num,name,age
from boysJOIN (SELECT @curRow := 0) r
where sex = '男'
union  all
select @curRow := @curRow + 1 AS row_num,name,age
from boysJOIN (SELECT @curRow := 0) r
where sex = '女' ;
# +-------+----+---+
# |row_num|name|age|
# +-------+----+---+
# |1      |慕容皝 |22 |
# |2      |慕容冲 |33 |
# |3      |慕容垂 |44 |
# |4      |慕容博 |55 |
# |5      |慕容雪 |55 |
# +-------+----+---+# 排序select @curRow := @curRow + 1 AS row_num,name,age
from boysJOIN (SELECT @curRow := 0) r
where sex = '男'
# 注意此处不能放 order by 否则语法错误
# order by age desc;
union  all
select @curRow := @curRow + 1 AS row_num,name,age
from boysJOIN (SELECT @curRow := 0) r
where sex = '女'order by age desc;
# +-------+----+---+
# |row_num|name|age|
# +-------+----+---+
# |4      |慕容博 |55 |
# |5      |慕容雪 |55 |
# |3      |慕容垂 |44 |
# |2      |慕容冲 |33 |
# |1      |慕容皝 |22 |
# +-------+----+---+# 发现上述结果并不符合要求 ：所以加个套，将所有的数据排完序后，在加上编号
# 正确的降序处理 sql 如下
select @curRow := @curRow + 1 AS row_num,name,age
from (select name,agefrom boyswhere sex = '男'union allselect name,agefrom boyswhere sex = '女') as AJOIN (SELECT @curRow := 0) r
order by age desc;

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！