如何输出这个分层的Mysql结构？-HowtooutputthishierarchicalMysqlStructure?

0  

Given the way you're storing the data, you probably can't do this in one SQL query. Or else it would be so brain-meltingly complicated that it's a bad idea to do so. Remember the advice of Brian Kernighan:

鉴于您存储数据的方式，您可能无法在一个SQL查询中执行此操作。否则它会如此复杂，以至于这样做是个坏主意。记住Brian Kernighan的建议：

Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

调试是首先编写代码的两倍。因此，如果您尽可能巧妙地编写代码，那么根据定义，您不够智能来调试它。

You can do the first query pretty easily:

您可以非常轻松地执行第一个查询：

SELECT layer1, layer2, COUNT(layer3) AS dotdotdot
FROM categories
WHERE layer1 = 'echo'
GROUP BY layer1, layer2

If dotdotdot is non-null, then there's a nonzero number of subcategories in layer 3.

如果dotdotdot为非null，则第3层中的子类别数量为非零。

If the user has highlighted "brain", then as you display the result of the first query in a loop, run one more query:

如果用户突出显示“大脑”，那么当您在循环中显示第一个查询的结果时，再运行一个查询：

SELECT layer3
FROM categories
WHERE layer2 = 'brain'

And that's it. It may seem so dull to run two queries instead of finding some ingenious way of doing it all in one query, but you'd be surprised how frequently a complex problem becomes both simpler and more efficient when you break it down into smaller problems.

就是这样。运行两个查询而不是在一个查询中找到一些巧妙的方法来执行它可能看起来很乏味，但是当您将其分解为更小的问题时，复杂问题变得更简单和更有效的频率会让您感到惊讶。

If you want something more streamlined, check out my presentation Models for Hierarchical Data with SQL and PHP. I show an example of the kind of problem you're trying to implement: selective expansion of a given level in a hierarchy. But I solve the problem by storing the data differently than you are.

如果您想要更精简的内容，请查看我的演示文稿使用SQL和PHP进行分层数据的模型。我展示了您尝试实现的问题类型的示例：在层次结构中选择性扩展给定级别。但我通过以不同的方式存储数据来解决问题。