mysql 使用同一表中的行作为条件？
我有一个表结构，看起来像
id | parent | online | name
1 | 1 | 1 | first
2 | 1 | 1 | second
3 | 1 | 1 | third
基本上我想要的是，如果列设置为脱机，那么我们不会选择该列， 很简单，
Select * from `table` where `online` = 1;
接下来的部分就变成了，如果列父级设置为脱机，我们也不想选择该列。
我的工作是，
SELECT t1.* FROM `table` t1 LEFT JOIN `table` t2 ON t1.`parent` = t2.`id`
WHERE t1.`online` = 1 AND t2.`online` = 1;
如果嵌套最多一层深，则效果很好。
如果嵌套只有一层深，是否有更好的方法来做到这一点？
另一点是，是否有更好的方法来做到这一点，以便嵌套可以无限深？
如果存在结构，则需要允许简单（加载明智和代码明智）查询来获取行的所有子项，并获取具有相对于子项的嵌套级别的行的所有父项（例如，第一个父项的级别将是1，父母的父母将是2）。
I have a table structure which looks like
id | parent | online | name
1 | 1 | 1 | first
2 | 1 | 1 | second
3 | 1 | 1 | third
Basically what I want is, if a column is set to offline then we don't select that column,
easy enough
Select * from `table` where `online` = 1;
Next part of it becomes, if a columns parent is set to offline, we don't want to select the column either.
My working is
SELECT t1.* FROM `table` t1 LEFT JOIN `table` t2 ON t1.`parent` = t2.`id`
WHERE t1.`online` = 1 AND t2.`online` = 1;
Which works fine if the nesting is at most one level deep.
Is there a better way of doing this if the nesting is only one level deep?
other point is, is there a better way of doing this so that the nesting can be an unlimited amount of levels deep?
If there is the structure would need to allow simple (both load wise and code wise) queries to get all children of a row, and to get all parents of a row with the nesting level relative to the child (e.g. first parent's level would be 1, parent of parent would be 2).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我可以建议第一部分的答案，其中嵌套为一层深：
我相信会稍微更有效，因为您增加了语句的基数。
不幸的是，多层嵌套是一个棘手的主题，我会使用 CTE 在 SQL Server 上完成它，但我不知道 MySQL 中的这一点，抱歉。
I can suggest an answer the first part where the nesting is one-level deep:
I believe will be slightly more efficient, for the reason that you're increasing the cardinality of the statement.
Unfortunately, multi-level nesting is a tricky subject, I'd do it on SQL Server using a CTE, but I don't know about this in MySQL, sorry.