MySQL получить позицию строки в порядке

со следующей таблицей MySQL:

+-----------------------------+
+ id INT UNSIGNED             +
+ name VARCHAR(100)           +
+-----------------------------+

Как выбрать один строка и ее положение среди других строк в таблице, при сортировке по name ASC. Поэтому, если данные таблицы выглядят так, при сортировке по имени:

+-----------------------------+
+ id | name                   +
+-----------------------------+
+  5 | Alpha                  +
+  7 | Beta                   +
+  3 | Delta                  +
+ .....                       +
+  1 | Zed                    +
+-----------------------------+

как я мог выбрать Beta строка становится текущей позиции этой строки? Результирующий набор, который я ищу, будет примерно таким:

+-----------------------------+
+ id | position | name        +
+-----------------------------+
+  7 |        2 | Beta        +
+-----------------------------+

Я могу сделать простой SELECT * FROM tbl ORDER BY name ASC затем перечислить строки в PHP, но кажется расточительным загружать потенциально большой набор результатов только для одной строки.

6 71
sql mysql analytic-functions

6 ответов:

используйте этот:

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'

...чтобы получить уникальное значение позиции. Это:

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 для другого...

101

Это единственный способ, который я могу придумать:

SELECT `id`,
       (SELECT COUNT(*) FROM `table` WHERE `name` <= 'Beta') AS `position`,
       `name`
FROM `table`
WHERE `name` = 'Beta'
16

если запрос прост и размер возвращаемого результирующего набора потенциально велик, то вы можете попытаться разделить его на два запроса.

первый запрос с узкими критериями фильтрации только для получения данных этой строки, а второй запрос использует COUNT с WHERE предложение для вычисления позиции.

например, в вашем случае

запрос 1:

SELECT * FROM tbl WHERE name = 'Beta'

запрос 2:

SELECT COUNT(1) FROM tbl WHERE name >= 'Beta'

мы используем этот подход в таблице с записью 2M, и это способ более масштабируемый, чем подход OMG Ponies.

7

у меня очень похожая проблема, поэтому я не буду задавать один и тот же вопрос, но я поделюсь здесь тем, что я сделал, мне пришлось использовать также группу by и заказать AVG. Есть студенты, с подписями и socore, и я должен был ранжировать их (другими словами, я сначала вычисляю AVG, затем упорядочиваю их в DESC, а затем, наконец, мне нужно было добавить позицию (ранг для меня), поэтому я сделал что-то очень похоже как лучший ответ здесь, с небольшими изменениями, которые регулируют моя проблема):

Я наконец-то position (ранг для меня) столбец во внешнем SELECT

SET @rank=0;
SELECT @rank := @rank + 1 AS ranking, t.avg, t.name
  FROM(SELECT avg(students_signatures.score) as avg, students.name as name
FROM alumnos_materia
JOIN (SELECT @rownum := 0) r
left JOIN students ON students.id=students_signatures.id_student
GROUP BY students.name order by avg DESC) t
2

позиция строки в таблице представляет, сколько строк "лучше", чем целевая строка.

Итак, вы должны считать эти строки.

ВЫБЕРИТЕ COUNT (*)+1 FROM table здесь name

в случае ничьей возвращается самая высокая позиция.

Если вы добавите еще одну строку с тем же именем "Beta" после существующей строки "Beta", то возвращенная позиция будет по-прежнему 2, так как они будут разделять одно и то же место в классификация.

надеюсь, что это поможет людям, которые будут искать что-то подобное в будущем, так как я считаю, что вопрос владелец уже решил свою проблему.

0

может быть, вам нужно добавить синтаксис

LIMIT

чтобы использовать

SELECT * FROM tbl ORDER BY name ASC LIMIT 1

Если вам просто нужно одну строку..

-7