mysql - 选择最佳输出

Question

这个问题与我最近的问题有关 here .

注意:这道题是Oracle中的，但也有可能是MySQL中的解答，所以你可以选择你喜欢的。

我想出了一个查询来生成我的模型之间的层次结构依赖关系，结果是这样的输出:

FIRST_MODEL SECOND_MODEL THIRD_MODEL FOURTH_MODEL FIFTH_MODEL SIXTH_MODEL SEVENTH_MODEL EIGHTH_MODEL NINTH_MODEL
----------- ------------ ----------- ------------ ----------- ----------- ------------- ------------ -----------
test1       test                                                                                                 
test2       test
test2       test6                                                                                                 
test3       test                                                                                                 
test3       test2                                                                                                
test3       test2        test     
test3       test2        test6                                                                                    
test4       test3                                                                                                
test4       test3        test                                                                                    
test4       test3        test2                                                                                   
test4       test3        test2       test   
test4       test3        test2       test6 
.....      

ETC..，这意味着 FIRST_MODEL 等待 SECOND_MODEL 等等。

问题是我必须从这里选择most completed输出，这基本上意味着只有“叶子”并且只有填充了最多数据的“叶子”，一个例子关于数据将在稍后提供。这是一个虚拟示例:

First | Second | Third | Forth | Fifth ....
 1       2        null    null    null
 1       2        6       null    null
 1       2        5       null    null
 1       2        5       7       null
 1       3        null    null    null
 1       4        6       null

结果应该是

First | Second | Third | Forth | Fifth ....
 1       2        6       null    null
 1       2        5       7       null
 1       4        6       null    null

所有其他东西都被排除在外，因为它们是所选输出的一部分。

所以使用 ROW_NUMBER() 或其他东西的查询将不起作用，

SELECT t.*,
       ROW_NUMBER() OVER(PARTITION BY ????? ORDER BY ???
FROM <Another Query>

我不知道如何按一组未知的列进行分区。

例如这里的输出应该是:

test4       test3        test2       test   
test4       test3        test2       test6   

因为其他一切都只是这两个结果的一部分。我真的坚持这一点，我看不出我可以使用哪个窗口函数，因为每个组每次都在变化，层次结构的级别是未知的并且可以改变。

我更喜欢避免使用动态 SQL，我知道它可以使用一个过程来完成，我也知道如何构建一个，但我有一个要求要避免它。

我还将提供创建数据的查询，它是在 Oracle 中使用分层查询构建的，也许有人会想从一开始就只获取最完整的行:

SELECT distinct REGEXP_SUBSTR( tests, '[^|]+', 1, 1 ) AS first_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 2 ) AS second_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 3 ) AS third_model,
       ......
FROM   (
  SELECT SYS_CONNECT_BY_PATH( wait_4_model_name, '|' ) || '|' ||  grand_model AS tests
  FROM  (SELECT * FROM Tab_Name)
  CONNECT BY NOCYCLE PRIOR grand_model = wait_4_model_name
)

有关数据的更多信息，您可以在上面的链接中输入我的最后一个问题。

提前致谢。

Answer 1

Oracle 设置:

CREATE TABLE table_name ( GRAND_MODEL, WAIT_4_MODEL_NAME ) AS
SELECT 'test',  'test1' FROM DUAL UNION ALL
SELECT 'test',  'test2' FROM DUAL UNION ALL
SELECT 'test',  'test3' FROM DUAL UNION ALL
SELECT 'test2', 'test3' FROM DUAL UNION ALL
SELECT 'test3', 'test4' FROM DUAL UNION ALL
SELECT 'test4', 'test5' FROM DUAL;

查询:

SELECT REGEXP_SUBSTR( tests, '[^|]+', 1, 1 ) AS first_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 2 ) AS second_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 3 ) AS third_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 4 ) AS fourth_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 5 ) AS fifth_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 6 ) AS sixth_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 7 ) AS seventh_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 8 ) AS eighth_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 9 ) AS ninth_model
FROM   (
  SELECT SYS_CONNECT_BY_PATH( wait_4_model_name, '|' ) || '|' ||  grand_model AS tests
  FROM  table_name
  WHERE CONNECT_BY_ISLEAF = 1
  START WITH wait_4_model_name NOT IN ( SELECT grand_model FROM table_name )
  CONNECT BY PRIOR grand_model = wait_4_model_name
);

输出:

FIRST_MODEL SECOND_MODEL THIRD_MODEL FOURTH_MODEL FIFTH_MODEL SIXTH_MODEL SEVENTH_MODEL EIGHTH_MODEL NINTH_MODEL
----------- ------------ ----------- ------------ ----------- ----------- ------------- ------------ -----------
test1       test                                                                                                 
test5       test4        test3       test                                                                        
test5       test4        test3       test2        test