mysql行號使用實例,mysql 行號

本文目錄一覽：

• 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 就保存了行編號信息。這個行編號信息的某種用途在於當你需要根據需要對數據按照某種規則排序並取出排序之後的某一行數據，並且希望知道這行數據在之前排序中的位置時就用得着了。為了方便，我們通常將兩句合起來執行，以避免變量全局自增：