..:: La。琉光 ::..
關於部落格
粼粼波光
反映心靈閃爍的跳動靈魂

在沒有三角、菱形、方塊的dynamic block

交織而成的

只有盈滿戀戀
我的琉光
  • 66837

    累積人氣

  • 0

    今日人氣

    0

    訂閱人氣

[ 轉 MySQL] Advanced ordering of MySQL results


MySQL's 「order by」 keyword allows the order in which result rows are returned to be defined. This is quite useful in a wide variety of circumstances. You can specify any number of columns whose values you want to order the results by, as well as what sorting order to use (desc or asc; asc is default). However, you may also run into situations in which you want certain groups within the resultset displayed in a certain order based on particular values in the results, not just according to alphanumeric sorting. This is easy to achieve by use of the equal operator or the field keyword within the order by clause.

Some examples are in order. I'll be using the world database. The following shows country names and a language spoken therein for European countries that speak Arabic, Dutch, and/or Greek:

1
2
3
4
SELECT a.Name, b.LANGUAGE FROM Country a
JOIN CountryLanguage b ON (a.Code = b.CountryCode)
WHERE a.Continent = "Europe"
AND b.LANGUAGE IN ("Arabic", "Dutch", "Greek");

This returns:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+-------------+----------+
| Name        | Language |
+-------------+----------+
| Netherlands | Arabic   |
| Netherlands | Dutch    |
| Albania     | Greek    |
| Belgium     | Arabic   |
| Belgium     | Dutch    |
| Gibraltar   | Arabic   |
| Greece      | Greek    |
| France      | Arabic   |
| Sweden      | Arabic   |
| Germany     | Greek    |
| Denmark     | Arabic   |
+-------------+----------+

But what if we want Dutch speaking countries first? We can't use order by Language desc or asc since Dutch results are in the middle of either sorting. Instead:

1
2
3
4
5
SELECT a.Name, b.LANGUAGE FROM Country a
JOIN CountryLanguage b ON (a.Code = b.CountryCode)
WHERE a.Continent = "Europe"
AND b.LANGUAGE IN ("Arabic", "Dutch", "Greek")
ORDER BY LANGUAGE="Dutch" DESC, LANGUAGE;

This orders the results with records having Language = 「Dutch」 first, followed by all the others ordered by their Language value. And this is the desired result:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+-------------+----------+
| Name        | Language |
+-------------+----------+
| Netherlands | Dutch    |
| Belgium     | Dutch    |
| France      | Arabic   |
| Gibraltar   | Arabic   |
| Netherlands | Arabic   |
| Denmark     | Arabic   |
| Belgium     | Arabic   |
| Sweden      | Arabic   |
| Albania     | Greek    |
| Germany     | Greek    |
| Greece      | Greek    |
+-------------+----------+

You can specify as many values in the order by clause as you want, each also allowing an asc or desc modifier. If you have a number of values in one field you want to specify, the field keyword can save you some typing:

1
2
3
4
5
SELECT a.Name, b.LANGUAGE FROM Country a
JOIN CountryLanguage b ON (a.Code = b.CountryCode)
WHERE a.Continent = "Europe"
AND b.LANGUAGE IN ("Arabic", "Dutch", "Greek", "Fries")
ORDER BY FIELD(LANGUAGE, "Greek", "Dutch", "Fries", "Arabic");

Returning the customized ordering:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+-------------+----------+
| Name        | Language |
+-------------+----------+
| Albania     | Greek    |
| Germany     | Greek    |
| Greece      | Greek    |
| Netherlands | Dutch    |
| Belgium     | Dutch    |
| Netherlands | Fries    |
| Belgium     | Arabic   |
| Gibraltar   | Arabic   |
| France      | Arabic   |
| Netherlands | Arabic   |
| Denmark     | Arabic   |
| Sweden      | Arabic   |
+-------------+----------+
Be Sociable, Share!

可以直接看 下面的 來源網站,排版上比較清晰,為避免網站無法顯示,上述所有內容皆出自於下述的網站連結,並無修改任何內容。

來源:dancingpenguinsoflight.com/2009/09/advanced-ordering-of-mysql-results/
相簿設定
標籤設定
相簿狀態