[DOCS] I notice Postgresql 'HAVING' doesn't work.
От | nnj@riseup.net |
---|---|
Тема | [DOCS] I notice Postgresql 'HAVING' doesn't work. |
Дата | |
Msg-id | 20170105010904.10139.25730@wrigleys.postgresql.org обсуждение исходный текст |
Список | pgsql-docs |
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/static/tutorial-agg.html Description: Following this tutorial The Nested Set Model on http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ Section: Depth of a Sub-Tree. SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name, (COUNT(parent.name) - 1) AS depth FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'PORTABLE ELECTRONICS' GROUP BY node.name, node.lft ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name, node.lft, sub_tree.depth ORDER BY node.lft; +----------------------+---------+ | name | depth | |----------------------+---------| | PORTABLE ELECTRONICS | 0 | | MP3 PLAYERS | 1 | | FLASH | 2 | | CD PLAYERS | 1 | | 2 WAY RADIOS | 1 | +----------------------+---------+ Section: Find the Immediate Subordinates of a Node. SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name, (COUNT(parent.name) - 1) AS depth FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'PORTABLE ELECTRONICS' GROUP BY node.name, node.lft ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name, node.lft, sub_tree.depth HAVING depth <= 1 ORDER BY node.lft; Adding 'HAVING depth <= 1' to the query still return the same results as above instead of this: +----------------------+---------+ | name | depth | |----------------------+---------| | PORTABLE ELECTRONICS | 0 | | MP3 PLAYERS | 1 | | FLASH | 1 | | CD PLAYERS | 1 | | 2 WAY RADIOS | 1 | +----------------------+---------+ NOTE: I edited the query by add node.lft, sub_tree.depth to the GROUP BY. I don't know if I'm doing anything wrong?
В списке pgsql-docs по дате отправления: