Блог

Учитывая следующую (значительно упрощенную) таблицу примеров:

 CREATE TABLE `permissions` (
    `name` varchar(64) NOT NULL DEFAULT '',
    `access` enum('read_only','read_write') NOT NULL DEFAULT 'read_only'
);
  

И следующее содержимое примера:

 | name | access     |
=====================
| foo  | read_only  |
| foo  | read_write |
| bar  | read_only  |
  

Что я хочу сделать, так это запустить SELECT запрос, который извлекает одну строку для каждого уникального значения в name , отдавая предпочтение тем, у access которых есть значение read_write , есть ли способ, которым это можно сделать? т.е. Так, чтобы результаты, которые я получу, были:

 foo | read_write |
bar | read_only  |
  

Возможно, в будущем мне потребуется добавить новые параметры в access столбец, но они всегда будут в порядке важности (от наименьшего к наибольшему), поэтому, если возможно, решение, которое может справиться с этим, было бы особенно полезно.

Кроме того, чтобы уточнить, моя фактическая таблица включает в себя другие поля, кроме этих, поэтому я не использую уникальный ключ в name столбце; по дизайну будет несколько строк по имени в соответствии с различными критериями.

С вашими данными будет работать следующее:

 select name, max(access)
from permissions
group by name;
  

Однако это упорядочивает по строковым значениям, а не по индексам. Вот еще один метод:

 select name,
       substring_index(group_concat(access order by access desc), ',') as access
from permissions
group by name;
  

Довольно сложно использовать индекс, order by но min() max() использовать символьное значение. Некоторые могут даже назвать это ошибкой.

Вы можете создать другую таблицу с приоритетом access (чтобы вы могли добавлять новые параметры), а затем сгруппировать и найти MIN() значение таблицы приоритетов:

Например. создайте таблицу, вызываемую Priority со значениями

 | PriorityID| access     |
========================
| 1         | read_write |
| 2         | read_only  |
  

И затем,

 SELECT A.Name, B.Access
FROM (
    SELECT A.name, MIN(B.PriorityID) AS Most_Valued_Option  -- This will be 1 if there is a read_write for that name
    FROM permissions A
    INNER JOIN Priority B
    ON A.Access = B.Access
    GROUP BY A.Name ) A
INNER JOIN Priority B
ON A.Most_Valued_Option = B.PriorityID   
   -- Join that ID with the actual access 
   -- (and we will select the value of the access in the select statement)
  

Решение, предложенное Гордоном, достаточно для текущих требований.

Если мы ожидаем, что в будущем порядок приоритетов будет отличаться от алфавитного порядка строк (или по значению индекса перечисления)…

В качестве модифицированной версии ответа Гордона у меня возникнет соблазн использовать функцию MySQL FIELD и (ее обратную) ELT функцию, что-то вроде этого:

 SELECT p.name
     , ELT(
         MIN(
           FIELD(p.access
             ,'read_only','read_write','read_some'
           )
         )
       ,'read_only','read_write','read_some'
     ) AS access 
 FROM `permissions` p
GROUP BY p.name
  

Если спецификация должна извлекать всю строку, а не только значение access столбца, мы могли бы использовать запрос встроенного представления, чтобы найти предпочтительный access , и соединение с preferences таблицей, чтобы извлечь всю строку…

 SELECT p.*
  FROM ( -- inline view, to get the highest priority value of access
         SELECT r.name
              , MIN(FIELD(r.access,'read_only','read_write','read_some')) AS ax
           FROM `permissions` r
          GROUP BY r.name
       ) q
  JOIN `permissions` p
    ON p.name   = q.name
   AND p.access = ELT(q.ax,'read_only','read_write','read_some')
  

Обратите внимание, что этот запрос возвращает не только access с наивысшим приоритетом, но также может возвращать любые столбцы из этой строки.

С FIELD ELT помощью функций и мы можем реализовать любое произвольное упорядочение списка конкретных известных значений. Не просто алфавитный порядок или порядок по значению индекса перечисления.

Эта логика для «приоритета» может содержаться в запросе и не будет зависеть от дополнительных столбцов в permissions таблице или содержимого любых других таблиц.

Чтобы получить поведение, которое мы ищем, просто указав приоритет для access , «список значений», используемый в FIELD функции, должен соответствовать «списку значений» в ELT функции, в том же порядке, и списки должны включать все возможные значения access .

Ссылка:

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_elt

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_field

РАСШИРЕННОЕ ИСПОЛЬЗОВАНИЕ

Не то, чтобы у вас было требование сделать это, но учитывая возможные будущие требования… мы отмечаем, что…

Другой порядок «списка значений» приведет к другому порядку приоритета access . Таким образом, каждый из множества запросов может реализовывать свои собственные правила для «приоритета». Какое access значение искать первым, вторым и так далее, путем изменения порядка полного «списка значений».

Помимо простого изменения порядка, также возможно исключить возможное значение из «списка значений» в функциях FIELD и ELT . Рассмотрим, например, исключение 'read_only' значения из списка в этой строке:

               , MIN(FIELD(r.access,'read_write','read_some')) AS ax
  

и из этой строки:

   AND p.access = ELT(q.ax,'read_write','read_some')
  

Это эффективно ограничит name возвращаемые строки. Только name те, которые имеют access значение 'read_write' или 'read_some' . Другой способ взглянуть на это, a name , который имеет только a 'read_only' for access , не будет возвращен запросом.

Также возможны другие модификации «списка значений», где списки не «совпадают», для реализации еще более мощных правил. Например, мы могли бы исключить a name , у которого есть строка с 'read_only' .

Например, в ELT функции вместо 'read_only' значения мы используем значение, которое, как мы знаем, не существует (и не может существовать) ни в каких строках. Для иллюстрации,

мы можем включить 'read_only' в качестве «наивысшего приоритета» в этой строке…

               , MIN(FIELD(r.access,'read_only','read_write','read_some')) AS ax 
                                   ^^^^^^^^^^^
  

поэтому, если найдена строка с 'read_only' , это будет иметь приоритет. Но в ELT функции во внешнем запросе мы можем преобразовать это обратно в другое значение…

   AND p.access = ELT(q.ax,'eXcluDe','read_write','read_some')
                          ^^^^^^^^^ 
  

Если мы знаем, что 'eXcluDe' это значение не существует в access столбце, мы фактически исключили любое name значение, имеющее 'read_only' строку, даже если 'read_write' строка есть.

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

Вы можете использовать оператор distinct (или Group by) ВЫБЕРИТЕ отдельное имя, доступ ИЗ вкладки;

Это тоже работает:

 SELECT name, MAX(access)
  FROM permissions
GROUP BY name ORDER BY MAX(access) desc