需要查询以对树层次结构项进行分组

kt06eoxx 于 2021-06-25 发布在 Mysql

关注(0)|答案(3)|浏览(351)

我有一张mysql表 indexlistapp 看起来是这样的：

Index     Seq    Tree     App                                 Idx  
| 791525 | 139 |    1  |    1.8L (turbo gas)                 |  .NULL.  
| 791525 | 140 |    2  |    VIN C (5th digit, engine ID AWD  |  300-76318B  
| 791525 | 141 |    2  |    VIN D (5th digit, engine ID AWW) |  300-67718B  
| 791525 | 142 |    1  |    1.9L (turbo diesel)              |  .NULL.  
| 791525 | 143 |    2  |    VIN F (5th digit)                |  .NULL.  
| 791525 | 144 |    3  |    MT                               |  300-76119A  
| 791525 | 145 |    3  |    AT                               |  300-76119B  
| 791525 | 146 |    2  |    VIN P (5th digit)                |  .NULL.  
| 791525 | 147 |    3  |    MT                               |  300-76119A  
| 791525 | 148 |    3  |    AT                               |  300-76119B  
| 791525 | 149 |    1  |    2.0L (gasoline)                  |  .NULL.  
| 791525 | 150 |    2  |    VIN S (5th digit, engine ID AEG) |  300-76120  
| 791525 | 151 |    2  |    VIN T (5th digit, engine ID AEG) |  300-76120  
| 791525 | 152 |    2  |    VIN B (5th digit)                |  300-67995  
| 791525 | 153 |    2  |    VIN K (5th digit)                |  300-67995  
| 791525 | 154 |    1  |    2.8L (VIN G, 5th digit)          |  300-67328B

idx列中带有值的记录表示完成的“节点”或序列（如果您愿意的话）。
我想用一个查询输出数据，这样每个选项显示为一行，如下所示：

App                                                        Idx
| 1.9L (turbo diesel), VIN F (5th digit), AT              |  300-76119B         
| 1.9L (turbo diesel), VIN F (5th digit), MT              |  300-76119A  
| 1.9L (turbo diesel), VIN P (5th digit), AT              |  300-76119B  
| 1.9L (turbo diesel), VIN P (5th digit), MT              |  300-76119A  
| 1.8L (turbo gas), VIN C (5th digit, engine ID AWD)      |  300-76318B  
| 1.8L (turbo gas), VIN D (5th digit, engine ID AWW)      |  300-67718B  
| 2.0L (gasoline), VIN S (5th digit, engine ID AEG)       |  300-76120  
| 2.0L (gasoline), VIN T (5th digit, engine ID AEG)       |  300-76120  
| 2.8L (VIN G, 5th digit)                                 |  300-67328B  
| 2.0L (gasoline), VIN B (5th digit)                      |  300-67995  
| 2.0L (gasoline), VIN K (5th digit)                      |  300-67995

app description和idx值表示一个唯一的项，在本例中，此列表显示了2001年大众捷达的所有可用特定发动机类型。

mysql

来源：https://stackoverflow.com/questions/49803829/need-query-to-group-tree-hierarchy-items

3条答案

按热度按时间

fcg9iug31#

我使用oracle11g sql单语句来实现它，如下所示：

select decode(instr(apps, '/', 2), 0, substr(apps, 2), 
substr(apps, 2, instr(apps, '/', 2)-2)||', '||substr(apps, instr(apps, '/', 2)+1)),  idx 
from (
select sys_connect_by_path(app, '/') as apps, idx
from (
select x.*, 
(case when tree = 1 then ROW_NUMBER else ceil(ROW_NUMBER/tree) end) lvl, 
min(seq) OVER (partition by (case when tree = 1 then ROW_NUMBER else ceil(ROW_NUMBER/tree) end)) AS parent_tree
from (
select a.*, ROW_NUMBER() over (partition by tree order by seq) as ROW_NUMBER
FROM INDEXLISTAPP a
) x
)
where idx is not null
start with seq = parent_tree 
connect by NOCYCLE PRIOR seq = parent_tree
order by apps
)
;

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

vuktfyat2#

我开始使用MySQL5.7来解决这个问题，如下所示：

set @pk1 ='';
set @rn1 =1;
SELECT  tree, seq, App, indx, rowNumber
FROM
(
SELECT  tree, seq, app, indexlistapp.Index as indx, 
@rn1 := if(@pk1=tree, @rn1+1,tree) as rowNumber, 
@pk1 := tree
FROM    indexlistapp
    ORDER BY seq
) A ;

下一次我会做的，所以我现在很忙，
谢谢您

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

qzlgjiam3#

我得到了一些关于这个查询和函数的帮助。

SELECT get_path(seqnbr, treelevel,indexlistid) app, InterchangeNumber FROM 
Indexlistapp WHERE InterchangeNumber is not null and indexlistid = 791525 order by 
app asc

FUNCTION get_path(_seqnbr,_treelevel,_indexlistid)
BEGIN
DECLARE _parentseqnbr decimal(6,0);   
DECLARE _application TEXT;

SET @ret = '';    

-- Loop until the _treelevel is 0
WHILE _treelevel > 0 DO
    -- Select the record at the _treelevel the is <= seqnbr
    SELECT seqnbr, application
    INTO _parentseqnbr, _application
    FROM indexlistapp
    WHERE seqnbr <= _seqnbr AND treelevel = _treelevel and indexlistid = _indexlistid
    ORDER BY seqnbr DESC
    LIMIT 1;        

    -- Concatenate the application field with the previous values in the loop        
    SET @ret := CONCAT(_application, ', ', @ret);

    -- Set the values for the next iteration of the loop
    SET _seqnbr := _parentseqnbr;
    SET _treelevel := _treelevel - 1;    

end WHILE;

-- Remove the extra ', ' from the end of the string
RETURN TRIM(TRAILING ', ' FROM @ret);

结束

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

我来回答

需要查询以对树层次结构项进行分组

3条答案

相关问题

热门标签

最新问答