本文目录一览：
- 1、如何在MySQL查询结果集中得到记录行号
- 2、mysql中的一些稍微复杂用法实例代码
- 3、mysql获取查询结果的行号
- 4、mysql 怎么才能做到rownumber序号？
- 5、详解mysql的limit经典用法及优化实例
- 6、如何让mysql的查询结果显示行号
如何在MySQL查询结果集中得到记录行号
sqlserver中输出行号：row_number over()
而在mysql中没有排序函数，应该使用:
SET @rownum = 0;
SELECT (@rownum := @rownum + 1) AS ROWNUM , 表名.* FROM 表名;
验证
mysql中的一些稍微复杂用法实例代码
前言
mysql的语法相信对大家来说都不是难事，但是本文主要给分享了一些mysql复杂用法的相关内容，通过这篇文章相信大家会对mysql更深的了解一些，下面话不多说了，来一起看看详细的介绍吧
一对多数据显示成一行
GROUP_CONCAT(expr)
1、涉及的表关系:teacher表、teacher_subject_rel表(教师所能教的学科表)、subject表
2、业务场景:
需要拉取所有教师的编号(teacher_no)、学科名(subject_name)。
nbsp
教师表(teacher)和学科(teacher_subject_rel)是一对多关系,
往往查询出现的是同一教师多条
数据。我们希望得到每个教师一条数据
学科拼接成一条
1、基本语法
group_concat(
[DISTINCT]
要连接的字段
[Order
BY
排序字段
ASC/DESC]
[Separator
'分隔符']
)
2、例子
SELECT
t.teacher_id
as
'教师id',
t.teacher_no
'教师编号',
(
SELECT
GROUP_CONCAT(s.subject_name)
FROM
teacher_subject_rel
tsr
LEFT
JOIN
`subject`
s
ON
tsr.subject_id
=
s.subject_id
WHERE
t.teacher_id
=
tsr.teacher_id
)
AS
'学科'
FROM
teacher
t
子查询、查询临时表、EXISTS
例子
SELECT
*
FROM
(
SELECT
o.id,
o.student_intention_id,
s.
NAME,
s.area_id,
a.area_name,
s.exam_year,
o.
STATUS,
CASE
o.
STATUS
WHEN
'1'
THEN
'待提交'
WHEN
'2'
THEN
'待指派'
WHEN
'3'
THEN
'已完成'
WHEN
'4'
THEN
'处理中'
END
statusName,
CASE
o.emergency_degree
WHEN
'1'
THEN
'正常'
WHEN
'2'
THEN
'紧急'
WHEN
'3'
THEN
'非常紧急'
END
emergencyDegreeName,
o.emergency_degree,
o.update_time,
(
SELECT
first_lesson_time
FROM
jx_strategy
WHERE
jx_lesson_plan_order_id
=
o.id
AND
STATUS
IN
(2,
7)
AND
first_lesson_time
now()
ORDER
BY
first_lesson_time
ASC
LIMIT
1
)
AS
first_time,
(
SELECT
deal_user_id
FROM
jx_strategy
WHERE
jx_lesson_plan_order_id
=
o.id
AND
STATUS
7
AND
deal_user_id
ORDER
BY
id
DESC
LIMIT
1
)
AS
deal_user_id
FROM
jx_lesson_plan_order
o
LEFT
JOIN
student
s
ON
s.student_intention_id
=
o.student_intention_id
LEFT
JOIN
area
a
ON
s.area_id
=
a.id
WHERE
o.
STATUS
1
AND
s.phone
=
'18501665888'
AND
o.emergency_degree
=
1
AND
o.
STATUS
=
2
AND
s.exam_year
=
'2015'
AND
o.update_time
=
'2018-08-14
20:28:55'
AND
o.update_time
=
'2018-08-14
20:28:55'
)
AS
a
WHERE
1
=
1
AND
a.deal_user_id
=
145316
AND
a.first_time
=
'2018-08-17
00:00:00'
AND
a.first_time
=
'2018-08-30
00:00:00'
AND
EXISTS
(
SELECT
*
FROM
jx_strategy
js
WHERE
js.jx_lesson_plan_order_id
=
a.id
AND
js.
STATUS
IN
(2,
7)
AND
js.subject_id
IN
(2,
3)
)
ORDER
BY
a.update_time
DESC
LIMIT
0,
10
update
关联变量条件修改
1、涉及的表关系:
user_info表中的
id_number(身份证号)
teacher表中的birth字段、
关联关系usrer_id
=
teacher_id
2、业务场景：获取用户身份证上的出生日期将出生日期更新在birth字段
UPDATE
teacher
t
INNER
JOIN
(
SELECT
t.teacher_id,
t.birth,
u.id_number,
CONCAT(SUBSTRING(u.id_number,
7,
4),
'-',
SUBSTRING(u.id_number,
11,
2),
'-',
SUBSTRING(u.id_number,
13,
2))
as
birth1,
u.reg_date,
t.exit_time
from
teacher
t
INNER
JOIN
user_info
u
ON
u.user_id
=
t.teacher_id
)
info
on
info.teacher_id
=
t.teacher_id
SET
t.birth
=
info.birth1
WHERE
info.reg_date
'2018-08-20
00:00:00'
and
info.id_number
is
not
NULL
and
(info.birth
is
NULL
or
t.birth
=
'')
and
t.is_train
=
1
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。
您可能感兴趣的文章:MySQL在关联复杂情况下所能做出的一些优化Mysql一些复杂的sql语句(查询与删除重复的行)深入mysql
"ON
DUPLICATE
KEY
UPDATE"
语法的分析MySQL
最基本的SQL语法/语句MySQL与Oracle的语法区别详细对比浅析Mysql
Join语法以及性能优化MySQL
ALTER语法的运用方法MySQL
prepare语句的SQL语法MySQL进阶SELECT语法篇MySQL
SQL
语法参考
mysql获取查询结果的行号
看你的意思行号就是显示顺序号，你的查询结果可以按id升序排序（order by id）,这样查询结果的顺序就是显示的顺序，这个行号可以在程序里加上，不用在数据库操作。另外最好是加上一个排序值得字段，这样可以随时调整显示顺序。
mysql 怎么才能做到rownumber序号？
为每一行记录添加行号
方法一：为了实现row_number函数功能，此方法我们要使用到会话变量，下面的实例是从 employees 表中选出5名员工，并为每一行添加行号：
1
2
3
4
5
6
SET@row_number = 0;
SELECT
(@row_number:=@row_number + 1) ASnum, firstName, lastName
FROM
employees
LIMIT 5;
输出结果：
在这个实例中：首先，定义变量 @row_number ，并初始化为0；然后，在查询时我们为 @row_number 变量加1
方法二：这种方法仍然要用到变量，与上一种方法不同的是，我们把变量当做派生表，与主业务表关联查询实现row_number函数功能。下面我们仍然以查询5位员工为例：
1
2
3
4
5
SELECT
(@row_number:=@row_number + 1) ASnum, firstName, lastName
FROM
employees,(SELECT@row_number:=0) ASt
LIMIT 5;
这样的输出结果与上一种结果是一致的。需要注意的是，在这种方法中，派生表必须要有别名，否则执行时会出错。为每一组添加行号。了解ORACLE的朋友应该知道，row_number函数还有一个非常有用的功能就是分组排序 “over partition by” 。MySQL同样可以实现这样的功能，看下面的实例：首先将payments表中按照客户将记录分组：
1
2
3
4
5
SELECT
customerNumber, paymentDate, amount
FROM
payments
ORDERBYcustomerNumber;
输出结果如下：下面我们需要将每个客户添加一个行号，这里我们需要用到两个变量，一个用于存储行号，一个用于存储客户编号，如：
1
2
3
4
5
6
7
8
9
10
11
SELECT
@row_number := CASE
WHEN@customer_no = customerNumber THEN@row_number + 1
ELSE1
ENDASnum,
@customer_no := customerNumber asC
根据具体问题类型，进行步骤拆解／原因原理分析／内容拓展等。
具体步骤如下：／导致这种情况的原因主要是……
详解mysql的limit经典用法及优化实例
用法一
复制代码
代码如下:
SELECT
`keyword_rank`.*
FROM
`keyword_rank`
WHERE
(advertiserid='59')
LIMIT
2
OFFSET
1;
比如这个SQL
，limit后面跟的是2条数据，offset后面是从第1条开始读取。
用法二
复制代码
代码如下:
SELECT
`keyword_rank`.*
FROM
`keyword_rank`
WHERE
(advertiserid='59')
LIMIT
2,1;
而这个SQL，limit后面是从第2条开始读，读取1条信息。
这两个千万别搞混哦。
用法三
复制代码
代码如下:
select
*
from
tablename
条件语句
limit
100,-1
从第100条后开始-最后一条的记录
用法四
复制代码
代码如下:
select
*
from
tablename
条件语句
limit
15
相当于limit
0,15
.查询结果取前15条数据用法五
mysql低版本不支持limit
offset
limit
offset
在mysql
4.0以上的版本中都可以正常运行，在旧版本的mysql
3.23中无效
limit
m
offset
n
等价于
limit
m,n
limit
的优化
mysql的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降
来源：一亩三分地博客
MYSQL的优化是非常重要的。其他最常用也最需要优化的就是limit。mysql的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降。
同样是取10条数据
复制代码
代码如下:
select
*
from
yanxue8_visit
limit
10000,10
select
*
from
yanxue8_visit
limit
0,10
就不是一个数量级别的。
网上也很多关于limit的五条优化准则，都是翻译自mysql手册，虽然正确但不实用。今天发现一篇文章写了些关于limit优化的，很不错。
文中不是直接使用limit，而是首先获取到offset的id然后直接使用limit
size来获取数据。根据他的数据，明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。（测试环境win2033+p4双核
(3GHZ)
+4G内存
mysql
5.0.19）
1、offset比较小的时候。
复制代码
代码如下:
select
*
from
yanxue8_visit
limit
10,10
多次运行，时间保持在0.0004-0.0005之间
复制代码
代码如下:
Select
*
From
yanxue8_visit
Where
vid
＞=(
Select
vid
From
yanxue8_visit
Order
By
vid
limit
10,1
)
limit
10
多次运行，时间保持在0.0005-0.0006之间，主要是0.0006
结论：偏移offset较小的时候，直接使用limit较优。这个显然是子查询的原因。
2、offset大的时候。
复制代码
代码如下:
select
*
from
yanxue8_visit
limit
10000,10
多次运行，时间保持在0.0187左右
复制代码
代码如下:
Select
*
From
yanxue8_visit
Where
vid
＞=(
Select
vid
From
yanxue8_visit
Order
By
vid
limit
10000,1
)
limit
10
多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优。
以后要注意改正自己的limit语句，优化一下mysql了
如何让mysql的查询结果显示行号
Oracle 中可以使用标准方法（8i版本以上），也可以使用非标准的 ROWNUM ； MS SQL Server 则在 2005 版本中提供了 ROW_NUMBER() 函数；但在 MySQL 中似乎还没有这样的系统自带功能。 解决方法是通过预定义用户变量来实现： mysql set @mycnt = 0; mysql select (@mycnt := @mycnt + 1) as ROWNUM ,NAME fromCHARBASE LIMIT 100; 这样查询出来的结果集中 ROWNUM 就保存了行编号信息。这个行编号信息的某种用途在于当你需要根据需要对数据按照某种规则排序并取出排序之后的某一行数据，并且希望知道这行数据在之前排序中的位置时就用得着了。 为了方便，我们通常将两句合起来执行，以避免变量全局自增：
