問題描述
使用以下 MySQL 表:
With the following MySQL table:
+-----------------------------+
+ id INT UNSIGNED +
+ name VARCHAR(100) +
+-----------------------------+
當按 name ASC 排序時，如何選擇 單個 行及其在表中其他行中的位置.所以如果表數據看起來像這樣，當按名稱排序時:
How can I select a single row AND its position amongst the other rows in the table, when sorted by name ASC. So if the table data looks like this, when sorted by name:
+-----------------------------+
+ id | name +
+-----------------------------+
+ 5 | Alpha +
+ 7 | Beta +
+ 3 | Delta +
+ ..... +
+ 1 | Zed +
+-----------------------------+
如何選擇 Beta 行以獲取該行的當前位置?我正在尋找的結果集將是這樣的:
How could I select the Beta row getting the current position of that row? The result set I'm looking for would be something like this:
+-----------------------------+
+ id | position | name +
+-----------------------------+
+ 7 | 2 | Beta +
+-----------------------------+
我可以做一個簡單的 SELECT * FROM tbl ORDER BY name ASC 然后在 PHP 中枚舉行，但是只為單行加載一個可能很大的結果集似乎很浪費.
I can do a simple SELECT * FROM tbl ORDER BY name ASC then enumerate the rows in PHP, but it seems wasteful to load a potentially large resultset just for a single row.
推薦答案
使用這個:
SELECT x.id,
x.position,
x.name
FROM (SELECT t.id,
t.name,
@rownum := @rownum + 1 AS position
FROM TABLE t
JOIN (SELECT @rownum := 0) r
ORDER BY t.name) x
WHERE x.name = 'Beta'
...獲得唯一的位置值.這:
...to get a unique position value. This:
SELECT t.id,
(SELECT COUNT(*)
FROM TABLE x
WHERE x.name <= t.name) AS position,
t.name
FROM TABLE t
WHERE t.name = 'Beta'
...會給關系相同的值.IE:如果有兩個值排在第二位，當第一個查詢將位置 2 給其中一個時，它們的位置都為 2，而另一個位置為 3...
...will give ties the same value. IE: If there are two values at second place, they'll both have a position of 2 when the first query will give a position of 2 to one of them, and 3 to the other...
這篇關于MySQL 在 ORDER BY 中獲取行位置的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！