MySQL - 从前一行获取值

Question

我正在尝试重建具有树结构的数据。

示例 - 国家/城市:

1) USA
1.1) New York
1.2) Chicago 
2) France
2.1) Paris
2.2) Lyon 
3) China

在我的数据库中，它看起来像这样:

|  Element | Level | Row |
|:--------:|:-----:|:---:|
|    USA   |   1   |  1  |
| New York |   2   |  2  |
|  Chicago |   2   |  3  |
|  France  |   1   |  4  |
|   Paris  |   2   |  5  |
|   Lyon   |   2   |  6  |
|   China  |   1   |  7  |

根据我的条目的顺序(行)，我可以重建树结构。对于每一行，我都会查找最近的具有 Level-1 的前一行。

max(pre.Row)/pre.Row < cur.Row/pre.Level = cur.Level-1

以下代码正在运行，并且返回正确的结果。我的问题是该表有 700 万行，因此需要很长时间。这就像比较 700 万乘以 700 万行......

SELECT cur.`Row`, (
    SELECT max(pre.`Row`)
    FROM `abc`.`def` AS pre
    WHERE pre.`Row` < cur.`Row` 
    AND pre.`Level`=cur.`Level`-1
) AS prev_row
FROM `abc`.`def` AS cur
;

有没有更快的方法来实现这个？

也许有循环或用户变量？我可以想象您实际上从当前行开始，然后测试前一行是否满足条件，否则查找下一行，依此类推。这将减少操作次数至 700 万次~5。我从来没有使用过循环，所以我不知道这在 SQL 中是否可行。有什么想法吗？

Answer 1

这是我对 3 个级别的尝试，如果您有更多级别，您可以添加级别，不知道为什么它会返回看起来像编码值的奇怪值，但 CAST() AS unSIGNED 会像您的查询一样获取 prev_row 。

SELECT Row,
       CAST(ELT(level-1,@level_1,@level_2,@level_3) as UNSIGNED) as prev_row,
       @level_1 := IF(`level` = 1, row, @level_1),
       @level_2 := IF(`level` = 2, row, @level_2),
       @level_3 := IF(`level` = 3, row, @level_3)
FROM `def`
ORDER BY Row ASC

http://sqlfiddle.com/#!9/719b2/22