Сначала отсортировать результат с пустыми строками
У меня есть эта locations таблица:
+----+-----------+------------+----------+
| id | country | state | city |
+----+-----------+------------+----------+
| 1 | US | Georgia | Atlanta |
| 2 | US | California | |
| 3 | US | | |
| 4 | Canada | Ontario | |
| 5 | Canada | Manitoba | Winnipeg |
| 6 | Canada | | |
Я хочу создать запрос, но не смог правильно построить свой ORDER BY. Это результат, который я хочу:
+----+-----------+------------+----------+
| id | country | state | city |
+----+-----------+------------+----------+
| 6 | Canada | | |
| 3 | US | | |
| 4 | Canada | Ontario | |
| 2 | US | California | |
| 5 | Canada | Manitoba | Winnipeg |
| 1 | US | Georgia | Atlanta |
По сути, это приоритет, которому я хочу следовать:
- Список стран. Алфавитный порядок.
- Государственный листинг. Алфавитный порядок.
- Листинг города. Алфавитный порядок.
Этот запрос, похоже, не учитывает пустые столбцы (я не использую NULL в своей таблице locations):
SELECT * FROM locations
ORDER BY
country,state,city
Считаете ли вы, что такие операции сильно нагружают процессор? Я подумываю о предварительном заказе столика.
Если вам нужен этот порядок только для целей отображения (без разбиения на страницы); Я бы предпочел сделать это в коде приложения (например, PHP); В этом случае MySQL может нет может использовать индекс, тем самым нагружая ЦП.
@user1506104 user1506104 В SQL нет концепции предварительного заказа данных таблицы. Самое близкое к тому, что вы имеете в виду, - это создать индекс, который позволит вашей базе данных сортировать данные так, как вы хотите их видеть.
@MadhurBhaiya, у меня есть все места в мире, и я рисую страницы, поэтому я не отправляю каждое место в свое приложение. Я считаю, что мой лучший вариант - сделать заказ в моем запросе sql?
@TimBiegeleisen «нет концепции предварительного заказа». я узнал кое-что сегодня.
@user1506104 user1506104 Если вы разбиваете на страницы, то вам подойдет SQL-запрос. Если данные в этой таблице не являются динамическими (вообще не должны быть, если вы уже учли все места в мире), то вы можете заново заполнить эту таблицу в нужном вам порядке. А затем используйте ключ автоинкремента для получения данных. В противном случае этот конкретный тип сложной сортировки не сможет использовать какой-либо индекс.
LIMIT 1000000, 1000, поскольку это кажется наиболее естественным в MySQL для разбиения на страницы, но это ужасно для производительности, поскольку базам данных MySQL необходимо получить 1001000 записей. ..
@RaymondNijland да; Каков обходной путь в этом случае? Хотя в большинстве практических приложений ни один пользователь не перейдет на 1000-ю страницу... если этот пользователь не попытается копнуть намного глубже...
WHERE id >= 1000000 ORDER BY id LIMIT 1000@RaymondNijland Я начал использовать аналогичный подход когда-то назад ... хотя он не работает во всех случаях ... спасибо за совет!
RANK()/ROW_NUMBER()) набор результатов и «ограничить» (фильтровать) его. Или смоделируйте его с помощью пользовательских переменных MySQL в более низких версиях. Это должно расширить возможные пользовательские случаи по сравнению с другим методом. Фактически использование оконной функции для «ограничения» наборов результатов было одним из единственно допустимых стандартных решений ANSI/ISO SQL до тех пор, пока SQL 2016 определил FETCH ограничение наборов результатов или доставленных таблиц.
Ответы 3
Попробуйте это ORDER BY предложение:
SELECT *
FROM locations
ORDER BY
CASE WHEN state = '' AND city = '' THEN 0
WHEN city = '' THEN 1
ELSE 2 END,
country,
state,
city;
Эта логика сортировки помещает в первую очередь те записи, в которых отсутствуют как штат, так и город. Далее следуют записи только с отсутствующим городом, за которыми следуют записи, содержащие непустые данные для всех трех полей.
Для версий до 8.0...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,country VARCHAR(20) NOT NULL
,state VARCHAR(20) NULL
,city VARCHAR(20) NULL
);
INSERT INTO my_table VALUES
(1,'US','Georgia','Atlanta'),
(2,'US','California',NULL),
(3,'US',NULL,NULL),
(4,'Canada','Ontario',NULL),
(5,'Canada','Manitoba','Winnipeg'),
(6,'Canada',NULL,NULL);
SELECT id
, country
, state
, city
FROM
( SELECT x.*
, CASE WHEN @prev=country THEN @i:=@i+1 ELSE @i:=1 END i
, @prev:=country
FROM my_table x
, (SELECT @prev:=null,@i:=0) vars
ORDER
BY country
, city
, state
) a
ORDER
BY i
, country;
+----+---------+------------+----------+
| id | country | state | city |
+----+---------+------------+----------+
| 6 | Canada | NULL | NULL |
| 3 | US | NULL | NULL |
| 4 | Canada | Ontario | NULL |
| 2 | US | California | NULL |
| 5 | Canada | Manitoba | Winnipeg |
| 1 | US | Georgia | Atlanta |
+----+---------+------------+----------+
Спасибо за попытку. я попробую это позже (поскольку у меня уже есть принятый ответ). если это сработает, я могу вместо этого дать вам награду.
select *
from locations
order by
state <> '',
city <> '',
country,
state,
city
Обратите внимание, что в MySQL логическое выражение возвращает 0 (для FALSE) или 1 (для TRUE). Это означает, что для пустой строки statestate <> '' вернет 0 и, таким образом, упорядочится первым.
Это больше похоже на чередующийся порядок