MySQL - ORDER BY

18098 ワード

神様に感謝 mysql パパ愛してるテキストリンク

Udemy-Theフラッグシップ版MySQL Bootcamp:Go from SQL Beginner to Expertレッスンまとめ

ORDER BY

sorting result

SELECTコラム名
FROMテーブル
ORDER BY順に並べたいコラム名

mysql> SELECT author_lname FROM books ORDER BY author_lname;
+----------------+
| author_lname   |
+----------------+
| Carver         |
| Carver         |
| Chabon         |
| DeLillo        |
| Eggers         |
| Eggers         |
| Eggers         |
| Foster Wallace |
| Foster Wallace |
| Gaiman         |
| Gaiman         |
| Gaiman         |
| Harris         |
| Harris         |
| Lahiri         |
| Lahiri         |
| Saunders       |
| Smith          |
| Steinbeck      |
+----------------+
19 rows in set (0.00 sec)

アルファベット順に並んでいるのが見えます.Defaultは昇順で並べられています.△ASCを貼ってもいいし、Defaultを貼らなくてもASCです.降順に並べたい場合は、後でDESCを付けることができます.

mysql> SELECT author_lname FROM books ORDER BY author_lname DESC;
+----------------+
| author_lname   |
+----------------+
| Steinbeck      |
| Smith          |
| Saunders       |
| Lahiri         |
| Lahiri         |
| Harris         |
| Harris         |
| Gaiman         |
| Gaiman         |
| Gaiman         |
| Foster Wallace |
| Foster Wallace |
| Eggers         |
| Eggers         |
| Eggers         |
| DeLillo        |
| Chabon         |
| Carver         |
| Carver         |
+----------------+
19 rows in set (0.00 sec)

このように降順に並べられているのが見えます.
今度はtitleを印刷しましょうまずORDER BYはありません

mysql> SELECT title FROM books;
+-----------------------------------------------------+
| title                                               |
+-----------------------------------------------------+
| The Namesake                                        |
| Norse Mythology                                     |
| American Gods                                       |
| Interpreter of Maladies                             |
| A Hologram for the King: A Novel                    |
| The Circle                                          |
| The Amazing Adventures of Kavalier & Clay           |
| Just Kids                                           |
| A Heartbreaking Work of Staggering Genius           |
| Coraline                                            |
| What We Talk About When We Talk About Love: Stories |
| Where I'm Calling From: Selected Stories            |
| White Noise                                         |
| Cannery Row                                         |
| Oblivion: Stories                                   |
| Consider the Lobster                                |
| 10% Happier                                         |
| fake_book                                           |
| Lincoln In The Bardo                                |
+-----------------------------------------------------+
19 rows in set (0.00 sec)

今回はORDER BYで順番に出力しましょう

mysql> SELECT title FROM books ORDER BY title;
+-----------------------------------------------------+
| title                                               |
+-----------------------------------------------------+
| 10% Happier                                         |
| A Heartbreaking Work of Staggering Genius           |
| A Hologram for the King: A Novel                    |
| American Gods                                       |
| Cannery Row                                         |
| Consider the Lobster                                |
| Coraline                                            |
| fake_book                                           |
| Interpreter of Maladies                             |
| Just Kids                                           |
| Lincoln In The Bardo                                |
| Norse Mythology                                     |
| Oblivion: Stories                                   |
| The Amazing Adventures of Kavalier & Clay           |
| The Circle                                          |
| The Namesake                                        |
| What We Talk About When We Talk About Love: Stories |
| Where I'm Calling From: Selected Stories            |
| White Noise                                         |
+-----------------------------------------------------+
19 rows in set (0.00 sec)

-- 내림차순으로
mysql> SELECT title FROM books ORDER BY title DESC;
+-----------------------------------------------------+
| title                                               |
+-----------------------------------------------------+
| White Noise                                         |
| Where I'm Calling From: Selected Stories            |
| What We Talk About When We Talk About Love: Stories |
| The Namesake                                        |
| The Circle                                          |
| The Amazing Adventures of Kavalier & Clay           |
| Oblivion: Stories                                   |
| Norse Mythology                                     |
| Lincoln In The Bardo                                |
| Just Kids                                           |
| Interpreter of Maladies                             |
| fake_book                                           |
| Coraline                                            |
| Consider the Lobster                                |
| Cannery Row                                         |
| American Gods                                       |
| A Hologram for the King: A Novel                    |
| A Heartbreaking Work of Staggering Genius           |
| 10% Happier                                         |
+-----------------------------------------------------+
19 rows in set (0.00 sec)

今回はそれを数字に応用してみましょう.まずORDER BYはありません

mysql> SELECT released_year FROM books;
+---------------+
| released_year |
+---------------+
|          2003 |
|          2016 |
|          2001 |
|          1996 |
|          2012 |
|          2013 |
|          2000 |
|          2010 |
|          2001 |
|          2003 |
|          1981 |
|          1989 |
|          1985 |
|          1945 |
|          2004 |
|          2005 |
|          2014 |
|          2001 |
|          2017 |
+---------------+
19 rows in set (0.00 sec)

今回はORDER BYを貼りましょう.

mysql> SELECT released_year FROM books ORDER BY released_year; 
+---------------+
| released_year |
+---------------+
|          1945 |
|          1981 |
|          1985 |
|          1989 |
|          1996 |
|          2000 |
|          2001 |
|          2001 |
|          2001 |
|          2003 |
|          2003 |
|          2004 |
|          2005 |
|          2010 |
|          2012 |
|          2013 |
|          2014 |
|          2016 |
|          2017 |
+---------------+
19 rows in set (0.00 sec)

mysql> SELECT released_year FROM books ORDER BY released_year DESC;
+---------------+
| released_year |
+---------------+
|          2017 |
|          2016 |
|          2014 |
|          2013 |
|          2012 |
|          2010 |
|          2005 |
|          2004 |
|          2003 |
|          2003 |
|          2001 |
|          2001 |
|          2001 |
|          2000 |
|          1996 |
|          1989 |
|          1985 |
|          1981 |
|          1945 |
+---------------+
19 rows in set (0.00 sec)

今回は他のコラムと組み合わせてデータを印刷します.

mysql> SELECT title, pages, released_year FROM books ORDER BY released_year;
+-----------------------------------------------------+-------+---------------+
| title                                               | pages | released_year |
+-----------------------------------------------------+-------+---------------+
| Cannery Row                                         |   181 |          1945 |
| What We Talk About When We Talk About Love: Stories |   176 |          1981 |
| White Noise                                         |   320 |          1985 |
| Where I'm Calling From: Selected Stories            |   526 |          1989 |
| Interpreter of Maladies                             |   198 |          1996 |
| The Amazing Adventures of Kavalier & Clay           |   634 |          2000 |
| American Gods                                       |   465 |          2001 |
| A Heartbreaking Work of Staggering Genius           |   437 |          2001 |
| fake_book                                           |   428 |          2001 |
| The Namesake                                        |   291 |          2003 |
| Coraline                                            |   208 |          2003 |
| Oblivion: Stories                                   |   329 |          2004 |
| Consider the Lobster                                |   343 |          2005 |
| Just Kids                                           |   304 |          2010 |
| A Hologram for the King: A Novel                    |   352 |          2012 |
| The Circle                                          |   504 |          2013 |
| 10% Happier                                         |   256 |          2014 |
| Norse Mythology                                     |   304 |          2016 |
| Lincoln In The Bardo                                |   367 |          2017 |
+-----------------------------------------------------+-------+---------------+
19 rows in set (0.00 sec)

出版年ごとにデータを公表した.ここでrelease yearは出力せず、ソートのみに使用することもできます.△SELECTのコラム名には書かず、ORDER BYの後に書くだけです.

mysql> SELECT title, pages FROM books ORDER BY released_year;
+-----------------------------------------------------+-------+
| title                                               | pages |
+-----------------------------------------------------+-------+
| Cannery Row                                         |   181 |
| What We Talk About When We Talk About Love: Stories |   176 |
| White Noise                                         |   320 |
| Where I'm Calling From: Selected Stories            |   526 |
| Interpreter of Maladies                             |   198 |
| The Amazing Adventures of Kavalier & Clay           |   634 |
| American Gods                                       |   465 |
| A Heartbreaking Work of Staggering Genius           |   437 |
| fake_book                                           |   428 |
| The Namesake                                        |   291 |
| Coraline                                            |   208 |
| Oblivion: Stories                                   |   329 |
| Consider the Lobster                                |   343 |
| Just Kids                                           |   304 |
| A Hologram for the King: A Novel                    |   352 |
| The Circle                                          |   504 |
| 10% Happier                                         |   256 |
| Norse Mythology                                     |   304 |
| Lincoln In The Bardo                                |   367 |
+-----------------------------------------------------+-------+
19 rows in set (0.00 sec)

さっきと同じ順番

SELECT title, author_fname, author_lname FROM books ORDER BY 2;

今回は上のSQL文を見てみましょう.ORDER BY 2ってどういう意味ですか?
まずやってみます.

mysql> SELECT title, author_fname, author_lname FROM books ORDER BY 2;
+-----------------------------------------------------+--------------+----------------+
| title                                               | author_fname | author_lname   |
+-----------------------------------------------------+--------------+----------------+
| 10% Happier                                         | Dan          | Harris         |
| A Hologram for the King: A Novel                    | Dave         | Eggers         |
| The Circle                                          | Dave         | Eggers         |
| A Heartbreaking Work of Staggering Genius           | Dave         | Eggers         |
| Oblivion: Stories                                   | David        | Foster Wallace |
| Consider the Lobster                                | David        | Foster Wallace |
| White Noise                                         | Don          | DeLillo        |
| fake_book                                           | Freida       | Harris         |
| Lincoln In The Bardo                                | George       | Saunders       |
| The Namesake                                        | Jhumpa       | Lahiri         |
| Interpreter of Maladies                             | Jhumpa       | Lahiri         |
| Cannery Row                                         | John         | Steinbeck      |
| The Amazing Adventures of Kavalier & Clay           | Michael      | Chabon         |
| Norse Mythology                                     | Neil         | Gaiman         |
| American Gods                                       | Neil         | Gaiman         |
| Coraline                                            | Neil         | Gaiman         |
| Just Kids                                           | Patti        | Smith          |
| What We Talk About When We Talk About Love: Stories | Raymond      | Carver         |
| Where I'm Calling From: Selected Stories            | Raymond      | Carver         |
+-----------------------------------------------------+--------------+----------------+
19 rows in set (0.00 sec)

ここで正解を見つけたかもしれないけど2番目のORDER BYで

これは3番目のコラムです(ここではauthor lnameでORDER BYを行います)

mysql> SELECT title, author_fname, author_lname FROM books ORDER BY 3;
+-----------------------------------------------------+--------------+----------------+
| title                                               | author_fname | author_lname   |
+-----------------------------------------------------+--------------+----------------+
| What We Talk About When We Talk About Love: Stories | Raymond      | Carver         |
| Where I'm Calling From: Selected Stories            | Raymond      | Carver         |
| The Amazing Adventures of Kavalier & Clay           | Michael      | Chabon         |
| White Noise                                         | Don          | DeLillo        |
| A Hologram for the King: A Novel                    | Dave         | Eggers         |
| The Circle                                          | Dave         | Eggers         |
| A Heartbreaking Work of Staggering Genius           | Dave         | Eggers         |
| Oblivion: Stories                                   | David        | Foster Wallace |
| Consider the Lobster                                | David        | Foster Wallace |
| Norse Mythology                                     | Neil         | Gaiman         |
| American Gods                                       | Neil         | Gaiman         |
| Coraline                                            | Neil         | Gaiman         |
| 10% Happier                                         | Dan          | Harris         |
| fake_book                                           | Freida       | Harris         |
| The Namesake                                        | Jhumpa       | Lahiri         |
| Interpreter of Maladies                             | Jhumpa       | Lahiri         |
| Lincoln In The Bardo                                | George       | Saunders       |
| Just Kids                                           | Patti        | Smith          |
| Cannery Row                                         | John         | Steinbeck      |
+-----------------------------------------------------+--------------+----------------+
19 rows in set (0.00 sec)

今回は.mysql> SELECT author_fname, author_lname FROM books ORDER BY author_lname, author_fname;ORDER BYの後ろに2つのコラムを入力するとどうなりますか?
author lnameでソートし、author fnameでソートします.

mysql> SELECT author_fname, author_lname FROM books ORDER BY author_lname, author_fname;
+--------------+----------------+
| author_fname | author_lname   |
+--------------+----------------+
| Raymond      | Carver         |
| Raymond      | Carver         |
| Michael      | Chabon         |
| Don          | DeLillo        |
| Dave         | Eggers         |
| Dave         | Eggers         |
| Dave         | Eggers         |
| David        | Foster Wallace |
| David        | Foster Wallace |
| Neil         | Gaiman         |
| Neil         | Gaiman         |
| Neil         | Gaiman         |
| Dan          | Harris         |
| Freida       | Harris         |
| Jhumpa       | Lahiri         |
| Jhumpa       | Lahiri         |
| George       | Saunders       |
| Patti        | Smith          |
| John         | Steinbeck      |
+--------------+----------------+
19 rows in set (0.01 sec)

上のデータから、author lnameにソートし、author fnameにソートすることがわかります.どういう意味ですか.
Dan HarrisとFred Harrisの部分から見ると,著者lnameは同じである.その後author fname順に並べ替えます.
今回、author lnameは降順、author fnameは昇順に並びます.

mysql> SELECT author_fname, author_lname FROM books ORDER BY author_lname DESC, author_fname;
+--------------+----------------+
| author_fname | author_lname   |
+--------------+----------------+
| John         | Steinbeck      |
| Patti        | Smith          |
| George       | Saunders       |
| Jhumpa       | Lahiri         |
| Jhumpa       | Lahiri         |
| Dan          | Harris         |
| Freida       | Harris         |
| Neil         | Gaiman         |
| Neil         | Gaiman         |
| Neil         | Gaiman         |
| David        | Foster Wallace |
| David        | Foster Wallace |
| Dave         | Eggers         |
| Dave         | Eggers         |
| Dave         | Eggers         |
| Don          | DeLillo        |
| Michael      | Chabon         |
| Raymond      | Carver         |
| Raymond      | Carver         |
+--------------+----------------+
19 rows in set (0.00 sec)

上記のデータからauthor lnameは降順で並べ替えられていますが、Dan HarrisとFred Harris部分は昇順で並べ替えられています.

Reference

この問題について(MySQL - ORDER BY), 我々は、より多くの情報をここで見つけました https://velog.io/@dlawogus/MySQL-ORDER-BY

テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。

Collection and Share based on the CC Protocol

iOSアドレス帳のバックアップ、リカバリ

[programmers]n進数ゲーム