mysql：按列按特定顺序分组(不只是asc或desc)

eoigrqb6 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(384)

我有一个查询正在运行。。。不过，我希望能够以特定的方式进行排序/排序。我当前的查询（例如）返回如下日期：

CenterID, OrgName, ID, Relationship, REL_START_YEAR, REL_END_YEAR, Full_Name

(84864, 'Twin Cities Spine Center', '19100', 'Fellow', '1971', '1971', 'Robert B. Winter, MD'),
(84864, 'Twin Cities Spine Center', '19100', 'Founder', '1971', '1972', 'Robert C. Summer, MD'),
(84864, 'Twin Cities Spine Center', '19100', 'Faculty', '1971', '1974', 'Robert D. Spring, MD'),
(84864, 'Twin Cities Spine Center', '19100', 'Fellow', '1972', '1974', 'Robert A. Fall, MD'),
(84864, 'Twin Cities Spine Center', '19100', 'Faculty', '1975', '1976', 'Ginger B. Jones, MD'),
(84864, 'Twin Cities Spine Center', '19100', 'Founder', '1977', '1978', 'Mary F. Johnson, MD'),
(84864, 'Twin Cities Spine Center', '19100', 'Faculty', '1971', '1972', 'Shawn S. Dimmer, MD'),
(84864, 'Twin Cities Spine Center', '19100', 'Fellow', '1978', '1978', 'Scotty B. Raw, MD'),
(84864, 'Twin Cities Spine Center', '19100', 'Fellow', '1978', '1981', 'Lou V. Louis, MD'),
(84864, 'Twin Cities Spine Center', '19100', 'Founder', '1984', '1984', 'Paula L. Davis, MD'),
(84864, 'Twin Cities Spine Center', '19100', 'Fellow', '1981', '1982', 'Nadine H. Ericson, MD');

不过，我想把东西分类/排序/分组
首先是关系栏。。但不仅仅是阿尔法排序。。我想把它列为
创始人第一教员第二研究员第三。。。
然后上面的每一个“组”都按相对的开始年份排序
这让我很接近。。但不是对关系列顺序的粒度控制：

SELECT o.CenterID
     , o.OrgName
     , r.ID
     , r.Relationship
     , r.REL_START_YEAR
     , r.REL_END_YEAR
     , u.ID
     , u.Full_Name 
  FROM genealogy_orgs o
  JOIN genealogy_relations r
    ON r.CenterID = o.CenterID 
  JOIN genealogy_users u
    ON u.ID = r.ID 
 WHERE o.CenterID = 84864
 ORDER 
    BY r.Relationship DESC
     , r.REL_START_YEAR ASC;

为了能够在“关系”列中按唯一顺序进行排序/分组，我在寻找什么？in（）函数的某种用法？

mysql unique sorting

来源：https://stackoverflow.com/questions/50139945/mysql-group-by-column-in-specific-order-not-just-asc-or-desc

2条答案

按热度按时间

relj7zay1#

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(relationship VARCHAR(12) NOT NULL
,year INT NOT NULL
,PRIMARY KEY (relationship,year)
);

INSERT INTO my_table VALUES
('Fellow' ,1971),
('Founder',1971),
('Faculty',1971),
('Fellow' ,1972),
('Faculty',1975),
('Founder',1977),
('Faculty',1974),
('Fellow' ,1978),
('Founder',1984),
('Fellow' ,1981); 

SELECT * 
  FROM my_table 
 ORDER 
    BY FIELD(relationship,'Founder','Faculty','Fellow')
     , year;
+--------------+------+
| relationship | year |
+--------------+------+
| Founder      | 1971 |
| Founder      | 1977 |
| Founder      | 1984 |
| Faculty      | 1971 |
| Faculty      | 1974 |
| Faculty      | 1975 |
| Fellow       | 1971 |
| Fellow       | 1972 |
| Fellow       | 1978 |
| Fellow       | 1981 |
+--------------+------+

但是请注意，您可以在应用程序代码（php/javascript等）中处理这类事情

赞(0）回复(0）举报 2021-06-21

sycxhyv72#

创始人第一教员第二研究员第三。。

ORDER BY CASE Relationship 
  WHEN 'Founders' THEN 1 WHEN 'Faculty' THEN 2 WHEN 'Fellow' THEN 3 ELSE 4 END, 
  REL_START_YEAR -- Not entirely sure what your requirements are here..

赞(0）回复(0）举报 2021-06-21

我来回答

mysql：按列按特定顺序分组(不只是asc或desc)

2条答案

相关问题

热门标签

最新问答