代码之家 › 专栏 › 技术社区 › user1506104

最后用空字符串对结果排序

database mysql

user1506104 · 技术社区 · 7 年前

我有这个 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) 位置 表):

SELECT * FROM locations
ORDER BY 
    country,state,city

0 回复 | 直到 7 年前

Tim Biegeleisen 7 年前

试试这个 ORDER BY

SELECT *
FROM locations
ORDER BY
    CASE WHEN state = '' AND city = '' THEN 0
         WHEN city = '' THEN 1
         ELSE 2 END,
    country,
    state,
    city;

这种排序逻辑首先放置那些同时缺少州和市的记录。接下来是记录缺失城市,最后是所有三个字段都有非空数据的记录。

Strawberry 7 年前

对于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  |
+----+---------+------------+----------+

Paul Spiegel 7 年前

select *
from locations
order by 
  state <> '',
  city  <> '',
  country,
  state,
  city

db-fiddle

注意,在MySQL中,布尔表达式返回 0 (用于 FALSE )或 1 TRUE ).那意味着一个空的 state 一串 state <> '' 会回来的