mysql - Oracle语法和选择自反关系中的最大和最小行

我正在努力处理一个涉及自反关系和Oracle语法的数据库情况。对于我目前的问题，我能想到的最好的类比是：关系表：------------------------------------------------------------| PERSON1 | PERSON2 | STARTED | ENDED |------------------------------------------------------------| Some Guy | Some Lady | 1998 | 2000 || Some Lady | Some Guy | 1998 | 2000 || .... | .... | .... | .... || .... | .... | .... | .... || .... | .... | .... | .... || .... | .... | .... | .... || Another Guy | Another Lady | 1992 | 2005 || Another Lady | Another Guy | 1992 | 2005 |------------------------------------------------------------查询：我试图选择的是最老和最新的行（W.R.T.“STARTED”）。如果这不是自反关系，那么很容易返回：------------------------------------------------------------| PERSON1 | PERSON2 | STARTED | ENDED |------------------------------------------------------------| Some Guy | Some Lady | 1998 | 2000 || Another Guy | Another Lady | 1992 | 2005 |------------------------------------------------------------由此：SELECT PERSON1, PERSON2, STARTED, ENDEDFROM RELATIONSHIPSWHERE (STARTED) IN( SELECT MAX(STARTED) AS START_YEAR FROM RELATIONSHIPS UNION SELECT MIN(STARTED) AS START_YEAR FROM RELATIONSHIPS);这将返回：------------------------------------------------------------| PERSON1 | PERSON2 | STARTED | ENDED |------------------------------------------------------------| Some Guy | Some Lady | 1998 | 2000 || Another Guy | Another Lady | 1992 | 2005 || Some Lady | Some Guy | 1998 | 2000 || Another Lady | Another Guy | 1992 | 2005 |------------------------------------------------------------... 当我真的只需要前两行时（不保证订单）如果我没有使用甲骨文，我知道我可以通过使用（除非有人也知道更好的方法）获得一些可以接受的东西：SELECT PERSON1, PERSON2, STARTED, ENDEDFROM RELATIONSHIPSORDER BY STARTED DESCLIMIT 1UNIONSELECT PERSON1, PERSON2, STARTED, ENDEDFROM RELATIONSHIPSORDER BY STARTED ASCLIMIT 1;非常感谢您的帮助！谢谢您！ 最佳答案 这将只返回一个最大值和一个最小行（类似于UNION 查询）：Oracle设置：CREATE TABLE relationships (PERSON1, PERSON2, STARTED, ENDED) as SELECT 'Some Guy' ,'Some Lady' ,1998, 2000 FROM DUAL UNION ALL SELECT 'Some Lady' ,'Some Guy' ,1998, 2000 FROM DUAL UNION ALL SELECT 'Another Guy' ,'Another Lady',1992, 2005 FROM DUAL UNION ALL SELECT 'Another Lady' ,'Another Guy' ,1992, 2005 FROM DUAL;查询：SELECT person1, person2, started, endedFROM ( SELECT r.*, ROW_NUMBER() OVER ( ORDER BY started ASC ) AS rn_asc, ROW_NUMBER() OVER ( ORDER BY started DESC ) AS rn_desc FROM relationships r)WHERE rn_asc = 1 OR rn_desc = 1;输出：PERSON1 PERSON2 STARTED ENDED------------ ----------- ------- -----Some Guy Some Lady 1998 2000Another Lady Another Guy 1998 2005关于mysql - Oracle语法和选择自反关系中的最大和最小行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42338816/