SQL查询-按列中的前两个字母排序

Question

我有一个SQL查询，当前显示以下结果：

Product ID Vendor Site Location 
-----------------------------------------------
ANTIFOAM 51 CHEMICAL COMPANY MANUFACTURING SITE
ANTIFOAM 51 CHEMICAL COMPANY MANUFACTURING SITE
ANTIFOAM 51 CHEMICAL COMPANY HQ HQ-FOOD
ACID 509    CSPA MANUFACTURING SITE
ACID 509    CSPA HQ HQ-FOOD
ACID 509    NORTH MANUFACTURING SITE
ACID 509    NORTH HQ HQ-FOOD

我需要先按总部下单，然后按生产地点下单。

任何帮助都是非常感谢的！

所以它应该看起来像这样：

Product ID Vendor Site Location
----------------------------------------
ANTIFOAM 51 CHEMICAL COMPANY HQ HQ-FOOD
ANTIFOAM 51 CHEMICAL COMPANY MANUFACTURING SITE
ANTIFOAM 51 CHEMICAL COMPANY MANUFACTURING SITE
ACID 509    CSPA HQ HQ-FOOD
ACID 509    CSPA MANUFACTURING SITE
ACID 509    NORTH HQ HQ-FOOD
ACID 509    NORTH MANUFACTURING SITE

Answer 1

在en-us语言环境中，"HQ“排在"MA”之前，因此假设"HQ-FOOD“和"MANUFACTURING SITE”是该列中仅有的两个值，您可以只使用默认的排序顺序：

select * from table order by ID, SiteLocation, Vendor

Answer 2

在PostgreSQL中：

select * from t1
order by
    id,
    regexp_replace(vendor,'\s+HQ$',''),
    case when site_location='HQ-FOOD' then 1 else 2 end
;

​

​

有关regexp_replace()的文档，请参阅here。

测试装置

drop table if exists t1;
create table t1 (product text, id int, vendor text, site_location text );
insert into t1 (product,id,vendor,site_location) values
    ('ANTIFOAM',51,'CHEMICAL COMPANY','MANUFACTURING SITE'),
    ('ANTIFOAM',51,'CHEMICAL COMPANY','MANUFACTURING SITE'),
    ('ANTIFOAM',51,'CHEMICAL COMPANY HQ','HQ-FOOD'),
    ('ACID',509,'CSPA ','MANUFACTURING SITE'),
    ('ACID',509,'CSPA  HQ','HQ-FOOD'),
    ('ACID',509,'NORTH','MANUFACTURING SITE'),
    ('ACID',509,'NORTH HQ','HQ-FOOD')
;

Answer 3

您可以看到我的代码(在SQL Server中)：

create table #t2 (product varchar(100), id int, vendor varchar(100), site_location varchar(100) );
insert into #t2 (product,id,vendor,site_location) values
    ('ANTIFOAM',51,'CHEMICAL COMPANY','MANUFACTURING SITE'),
    ('ANTIFOAM',51,'CHEMICAL COMPANY','MANUFACTURING SITE'),
    ('ANTIFOAM',51,'CHEMICAL COMPANY HQ','HQ-FOOD'),
    ('ACID',509,'CSPA ','MANUFACTURING SITE'),
    ('ACID',509,'CSPA  HQ','HQ-FOOD'),
    ('ACID',509,'NORTH','MANUFACTURING SITE'),
    ('ACID',509,'NORTH HQ','HQ-FOOD')

select product,id,vendor,site_location
from #t2
order by vendor desc

drop table #t2

输出：

ACID    509 NORTH HQ    HQ-FOOD
ACID    509 NORTH   MANUFACTURING SITE
ACID    509 CSPA  HQ    HQ-FOOD
ACID    509 CSPA    MANUFACTURING SITE
ANTIFOAM    51  CHEMICAL COMPANY HQ HQ-FOOD
ANTIFOAM    51  CHEMICAL COMPANY    MANUFACTURING SITE
ANTIFOAM    51  CHEMICAL COMPANY    MANUFACTURING SITE