- 浏览: 217542 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
q10000000:
你好 适用madMadia点击上传到服务器 没有反应啊LZ
在项目中整合FCKeditor文本编辑器 -
liweixxxx1990:
spring本版是3.0的(和版本也有关系吗?) 配置用到了s ...
struts2+spring发送mail -
floger:
liweixxxx1990 写道我照着你这个写的出现了下面的错 ...
struts2+spring发送mail -
liweixxxx1990:
我照着你这个写的出现了下面的错误,怎么解决啊??:Messag ...
struts2+spring发送mail -
jueyue:
不错,把问题解决了
Myeclipse下java.lang.OutOfMemoryError: Java heap space的解决
转:http://dev.mysql.com/tech-resources/articles/hierarchical-data.html By Mike Hillyer Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table. For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. For our purposes we will use the following product category hierarchy from an fictional electronics store: These categories form a hierarchy in much the same way as the other examples cited above. In this article we will examine two models for dealing with hierarchical data in MySQL, starting with the traditional adjacency list model. Typically the example categories shown above will be stored in a table like the following (I'm including full CREATE and INSERT statements so you can follow along): In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this case electronics, has a NULL value for its parent. The adjacency list model has the advantage of being quite simple, it is easy to see that FLASH is a child of mp3 players, which is a child of portable electronics, which is a child of electronics. While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can be more problematic in pure SQL. The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join: We can find all the leaf nodes in our tree (those with no children) by using a LEFT JOIN query: The self-join also allows us to see the full path through our hierarchies: The main limitation of such an approach is that you need one self-join for every level in the hierarchy, and performance will naturally degrade with each level added as the joining grows in complexity. Working with the adjacency list model in pure SQL can be difficult at best. Before being able to see the full path of a category we have to know the level at which it resides. In addition, special care must be taken when deleting nodes because of the potential for orphaning an entire sub-tree in the process (delete the portable electronics category and all of its children are orphaned). Some of these limitations can be addressed through the use of client-side code or stored procedures. With a procedural language we can start at the bottom of the tree and iterate upwards to return the full tree or a single path. We can also use procedural programming to delete nodes without orphaning entire sub-trees by promoting one child element and re-ordering the remaining children to point to the new parent. What I would like to focus on in this article is a different approach, commonly referred to as the Nested Set Model. In the Nested Set Model, we can look at our hierarchy in a new way, not as nodes and lines, but as nested containers. Try picturing our electronics categories this way: Notice how our hierarchy is still maintained, as parent categories envelop their children.We represent this form of hierarchy in a table through the use of left and right values to represent the nesting of our nodes: We use lft and rgt because left and right are reserved words in MySQL, see http://dev.mysql.com/doc/mysql/en/reserved-words.html for the full list of reserved words. So how do we determine left and right values? We start numbering at the leftmost side of the outer node and continue to the right: This design can be applied to a typical tree as well: When working with a tree, we work from left to right, one layer at a time, descending to each node's children before assigning a right-hand number and moving on to the right. This approach is called the modified preorder tree traversal algorithm. We can retrieve the full tree through the use of a self-join that links parents with nodes on the basis that a node's lft value will always appear between its parent's lft and rgt values: Unlike our previous examples with the adjacency list model, this query will work regardless of the depth of the tree. We do not concern ourselves with the rgt value of the node in our BETWEEN clause because the rgt value will always fall within the same parent as the lft values. Finding all leaf nodes in the nested set model even simpler than the LEFT JOIN method used in the adjacency list model. If you look at the nested_category table, you may notice that the lft and rgt values for leaf nodes are consecutive numbers. To find the leaf nodes, we look for nodes where rgt = lft + 1: With the nested set model, we can retrieve a single path without having multiple self-joins: We have already looked at how to show the entire tree, but what if we want to also show the depth of each node in the tree, to better identify how each node fits in the hierarchy? This can be done by adding a COUNT function and a GROUP BY clause to our existing query for showing the entire tree: We can use the depth value to indent our category names with the CONCAT and REPEAT string functions: Of course, in a client-side application you will be more likely to use the depth value directly to display your hierarchy. Web developers could loop through the tree, adding <li></li> and <ul></ul> tags as the depth number increases and decreases. When we need depth information for a sub-tree, we cannot limit either the node or parent tables in our self-join because it will corrupt our results. Instead, we add a third self-join, along with a sub-query to determine the depth that will be the new starting point for our sub-tree: This function can be used with any node name, including the root node. The depth values are always relative to the named node. Imagine you are showing a category of electronics products on a retailer web site. When a user clicks on a category, you would want to show the products of that category, as well as list its immediate sub-categories, but not the entire tree of categories beneath it. For this, we need to show the node and its immediate sub-nodes, but no further down the tree. For example, when showing the PORTABLE ELECTRONICS category, we will want to show MP3 PLAYERS, CD PLAYERS, and 2 WAY RADIOS, but not FLASH. This can be easily accomplished by adding a HAVING clause to our previous query: If you do not wish to show the parent node, change the HAVING depth <= 1 line to HAVING depth = 1. Let's add a table of products that we can use to demonstrate aggregate functions with: Now let's produce a query that can retrieve our category tree, along with a product count for each category: This is our typical whole tree query with a COUNT and GROUP BY added, along with a reference to the product table and a join between the node and product table in the WHERE clause. As you can see, there is a count for each category and the count of subcategories is reflected in the parent categories. Now that we have learned how to query our tree, we should take a look at how to update our tree by adding a new node. Let's look at our nested set diagram again: If we wanted to add a new node between the TELEVISIONS and PORTABLE ELECTRONICS nodes, the new node would have lft and rgt values of 10 and 11, and all nodes to its right would have their lft and rgt values increased by two. We would then add the new node with the appropriate lft and rgt values. While this can be done with a stored procedure in MySQL 5, I will assume for the moment that most readers are using 4.1, as it is the latest stable version, and I will isolate my queries with a LOCK TABLES statement instead: If we instead want to add a node as a child of a node that has no existing children, we need to modify our procedure slightly. Let's add a new FRS node below the 2 WAY RADIOS node: In this example we expand everything to the right of the left-hand number of our proud new parent node, then place the node to the right of the left-hand value. As you can see, our new node is now properly nested: The last basic task involved in working with nested sets is the removal of nodes. The course of action you take when deleting a node depends on the node's position in the hierarchy; deleting leaf nodes is easier than deleting nodes with children because we have to handle the orphaned nodes. When deleting a leaf node, the process if just the opposite of adding a new node, we delete the node and its width from every node to its right: And once again, we execute our indented tree query to confirm that our node has been deleted without corrupting the hierarchy: This approach works equally well to delete a node and all its children: And once again, we query to see that we have successfully deleted an entire sub-tree: The other scenario we have to deal with is the deletion of a parent node but not the children. In some cases you may wish to just change the name to a placeholder until a replacement is presented, such as when a supervisor is fired. In other cases, the child nodes should all be moved up to the level of the deleted parent: In this case we subtract two from all elements to the right of the node (since without children it would have a width of two), and one from the nodes that are its children (to close the gap created by the loss of the parent's left value). Once again, we can confirm our elements have been promoted: Other scenarios when deleting nodes would include promoting one of the children to the parent position and moving the child nodes under a sibling of the parent node, but for the sake of space these scenarios will not be covered in this article. While I hope the information within this article will be of use to you, the concept of nested sets in SQL has been around for over a decade, and there is a lot of additional information available in books and on the Internet. In my opinion the most comprehensive source of information on managing hierarchical information is a book called Joe Celko's Trees and Hierarchies in SQL for Smarties, written by a very respected author in the field of advanced SQL, Joe Celko. Joe Celko is often credited with the nested sets model and is by far the most prolific author on the subject. I have found Celko's book to be an invaluable resource in my own studies and highly recommend it. The book covers advanced topics which I have not covered in this article, and provides additional methods for managing hierarchical data in addition to the Adjacency List and Nested Set models. In the References / Resources section that follows I have listed some web resources that may be of use in your research of managing hierarchal data, including a pair of PHP related resources that include pre-built PHP libraries for handling nested sets in MySQL. Those of you who currently use the adjacency list model and would like to experiment with the nested set model will find sample code for converting between the two in the Storing Hierarchical Data in a Database resource listed below.Introduction
The Adjacency List Model
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);
INSERT INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);
SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)
Retrieving a Full Tree
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)
Finding all the Leaf Nodes
SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;
+--------------+
| name |
+--------------+
| TUBE |
| LCD |
| PLASMA |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+--------------+
Retrieving a Single Path
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';
+-------------+----------------------+-------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)
Limitations of the Adjacency List Model
The Nested Set Model
CREATE TABLE nested_category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);
INSERT INTO nested_category
VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),
(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);
SELECT * FROM nested_category ORDER BY category_id;
+-------------+----------------------+-----+-----+
| category_id | name | lft | rgt |
+-------------+----------------------+-----+-----+
| 1 | ELECTRONICS | 1 | 20 |
| 2 | TELEVISIONS | 2 | 9 |
| 3 | TUBE | 3 | 4 |
| 4 | LCD | 5 | 6 |
| 5 | PLASMA | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 |
| 7 | MP3 PLAYERS | 11 | 14 |
| 8 | FLASH | 12 | 13 |
| 9 | CD PLAYERS | 15 | 16 |
| 10 | 2 WAY RADIOS | 17 | 18 |
+-------------+----------------------+-----+-----+
Retrieving a Full Tree
SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;
+----------------------+
| name |
+----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+----------------------+
Finding all the Leaf Nodes
SELECT name
FROM nested_category
WHERE rgt = lft + 1;
+--------------+
| name |
+--------------+
| TUBE |
| LCD |
| PLASMA |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+--------------+
Retrieving a Single Path
SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY parent.lft;
+----------------------+
| name |
+----------------------+
| ELECTRONICS |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
+----------------------+
Finding the Depth of the Nodes
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
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| ELECTRONICS | 0 |
| TELEVISIONS | 1 |
| TUBE | 2 |
| LCD | 2 |
| PLASMA | 2 |
| PORTABLE ELECTRONICS | 1 |
| MP3 PLAYERS | 2 |
| FLASH | 3 |
| CD PLAYERS | 2 |
| 2 WAY RADIOS | 2 |
+----------------------+-------+
SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+-----------------------+
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
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
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
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
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
HAVING depth <= 1
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
Aggregate Functions in a Nested Set
CREATE TABLE product(
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40),
category_id INT NOT NULL
);
INSERT INTO product(name, category_id) VALUES('20" TV',3),('36" TV',3),
('Super-LCD 42"',4),('Ultra-Plasma 62"',5),('Value Plasma 38"',5),
('Power-MP3 5gb',7),('Super-Player 1gb',8),('Porta CD',9),('CD To go!',9),
('Family Talk 360',10);
SELECT * FROM product;
+------------+-------------------+-------------+
| product_id | name | category_id |
+------------+-------------------+-------------+
| 1 | 20" TV | 3 |
| 2 | 36" TV | 3 |
| 3 | Super-LCD 42" | 4 |
| 4 | Ultra-Plasma 62" | 5 |
| 5 | Value Plasma 38" | 5 |
| 6 | Power-MP3 128mb | 7 |
| 7 | Super-Shuffle 1gb | 8 |
| 8 | Porta CD | 9 |
| 9 | CD To go! | 9 |
| 10 | Family Talk 360 | 10 |
+------------+-------------------+-------------+
SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,
nested_category AS parent,
product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;
+----------------------+---------------------+
| name | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS | 10 |
| TELEVISIONS | 5 |
| TUBE | 2 |
| LCD | 1 |
| PLASMA | 2 |
| PORTABLE ELECTRONICS | 5 |
| MP3 PLAYERS | 2 |
| FLASH | 1 |
| CD PLAYERS | 2 |
| 2 WAY RADIOS | 1 |
+----------------------+---------------------+
Adding New Nodes
LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
UNLOCK TABLES;
We can then check our nesting with our indented tree query:
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| GAME CONSOLES |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+-----------------------+
LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft FROM nested_category
WHERE name = '2 WAY RADIOS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| GAME CONSOLES |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+-----------------------+
Deleting Nodes
LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';
DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
UNLOCK TABLES;
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+-----------------------+
LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'MP3 PLAYERS';
DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
UNLOCK TABLES;
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+-----------------------+
LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';
DELETE FROM nested_category WHERE lft = @myLeft;
UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;
UNLOCK TABLES;
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+---------------+
| name |
+---------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+---------------+
Final Thoughts
发表评论
-
SQL语句大全 (转)
2011-05-16 13:42 1258原文地址:http://topic.csdn.net/u/20 ... -
(转)SQL 查询效率
2011-04-25 09:36 1015很多人不知道SQL语句 ... -
数据库索引 类型 (转载)
2010-10-13 09:02 1000http://www.zoomhoo.com/viewthre ... -
索引的优点和缺点,设计数据库的人员必看--转载
2010-10-13 09:00 1015一、为什么要创建索引 ... -
破解 sql prompt 4 的方法
2010-03-31 10:58 45631.首先安装sql prompt 的原文件 ... -
java web和servlet结合使用为数据库备份
2009-09-21 09:41 1168使用Runtime类的方法备份数据库和web下载结合起来 ... -
存储过程的使用
2009-09-16 08:28 673=================分页========= ... -
oracle常用函数3
2009-09-16 08:26 857105。STDDEV_POP 功能描 ... -
oracle常用函数
2009-09-16 08:20 7511. ASCII 返回与指定的字符对应的十进制数; ... -
sql强人使用数据库计算身份证算法
2009-07-14 09:43 1154一、 --SQL SERVER 15位身 ... -
sql的大全代码(7)
2009-04-17 12:14 933各种字符串合并处理示例.sql SQL code & ... -
sql的大全代码(6)
2009-04-17 12:13 805统计--交叉表+日期+优先.sql SQL code ... -
sql的大全代码(5)
2009-04-17 12:12 870复杂年月处理.sql SQL code <!- ... -
sql的大全代码(4)
2009-04-17 12:11 1037工作日处理函数(标准节假日).sql SQL code ... -
sql的大全代码(3)
2009-04-17 12:10 860特殊日期加减函数.sql SQL code < ... -
sql的大全代码(2)
2009-04-17 12:09 857CONVERT在日期转换中的使用示例.sql: SQL c ... -
sql的大全代码(1)
2009-04-17 12:08 1092设置内存选项: SQL code <!-- ... -
SQL命令大全-中英文对照
2009-03-24 15:03 2085--语 句 功 能 --数据操作 SELECT --从数据库表 ...
相关推荐
《管理MySQL中的层次数据》是关于在MySQL数据库中有效地存储和操作树形或层级结构数据的主题。层级数据在很多业务场景中都非常常见,比如组织结构、产品目录、地理位置等。MySQL作为广泛使用的开源关系型数据库管理...
开源解决方案可以帮助开发者更高效地管理这类数据,而标题"Managing Hierarchical Data in MySQL-开源"暗示我们正在讨论一种开源方法来解决这个问题。 描述中的“嵌套集模型”是一种广泛使用的处理层次数据的技术。...
This framework offers a clear hierarchical structure, facilitating developers in rapid development and maintenance. The system primarily consists of a frontend for display and operation, and a ...
包括:源程序工程文件、Proteus仿真工程文件、配套技术手册等 1、采用51/52单片机作为主控芯片; 2、采用1602液晶显示; 3、采用5*8矩阵键盘输入; 4、功能键包括:复位键(RST),回删键(DEL),确定键(OK),第二功能切换(2U),背光灯键(LED); 5、运算均为单精度浮点数,包括: 加(+),减(-),乘(x),除(÷), e底指数(e^n),N次方(x^n),开N次方(sqrt), 正弦(sin),余弦(cos),正切(tan), 对数(log), 阶乘(n!)(n<35), 排列(Arn), 累加(∑), *开启第二功能(2U)后可用: 反正弦(asin),反余弦(acos),反正切(atan), 组合(Crn)
内容概要:本文详细介绍了如何利用三菱FX2N系列PLC构建机械手控制系统。主要内容涵盖电路图设计、IO表配置、源程序编写以及单机组态。文中提供了具体的梯形图编程实例,展示了如何通过PLC精确控制机械手的各种动作,如抓取、移动和放置。此外,还分享了许多实用的调试技巧和注意事项,强调了传感器状态交叉验证和关键动作的时间守护机制。通过这些内容,读者可以全面了解PLC在机械手控制中的应用。 适合人群:从事工业自动化领域的工程师和技术人员,尤其是对PLC编程和机械手控制感兴趣的初学者和有一定经验的研发人员。 使用场景及目标:适用于需要设计和实施机械手控制系统的工业场合,帮助工程师掌握PLC编程技巧,提高机械手控制系统的稳定性和可靠性。 其他说明:文章不仅提供理论指导,还包括大量实战代码和调试经验,有助于读者快速上手并在实践中不断优化系统性能。
内容概要:本文档提供了用于生成具有时尚性感元素的美女跳舞图像的提示词指南。文档内容包括角色设定为擅长描绘时尚与超现实主义图片的创作者,背景设定强调女性形象,偏好展现性感漂亮女孩的镜头表达。目标在于根据用户指令创作三幅统一风格的图像,注重色彩搭配和高清效果,同时确保每张图片都具备半身像、真实感和电影效果的特点。文档还给出了具体的输出示例,详细描述了人物形象、服装搭配以及场景布置等要素,旨在为用户提供满意的图像生成服务。; 适合人群:对图像生成感兴趣,尤其是喜欢带有时尚性感元素的美女图像的用户。; 使用场景及目标:①根据用户提供的简单场景信息(如户外或室内)生成三幅不同场景但风格统一的赛博朋克风格美女跳舞图像;②确保生成的图像符合特定的要求,如半身像、真实感、电影效果、性感服装、特定灯光效果等;③通过询问用户对生成图像的满意度来保证服务质量。; 其他说明:文档明确了图像生成的工作流程,从接收用户指令到根据反馈调整生成内容,确保整个过程高效且满足用户需求。同时,文档还限制了生成图像的具体条件,如场景必须为赛博朋克风格、不能出现鞋子和其他人等,以保证图像的独特性和一致性。
题目描述 1.问题描述 一个正整数如果任何一个数位不大于右边相邻的数位,则称为一个数位递增的 数,例如1135是一个数位递增的数,而1024不是一个数位递增的数。 给定正整数n,请问在整数1至n中有多少个数位递增的数? 输入格式 输入的第一行包含一个整数n。 输出格式 输出一行包含一个整数,表示答案。 样例输入 30 样例输出
内容概要:本文详细介绍了基于非对称纳什谈判的多微网电能共享运行优化策略及其MATLAB代码实现。首先阐述了纳什谈判和合作博弈的基本理论,然后将多微网电能共享合作运行模型分解为微网联盟效益最大化和合作收益分配两个子问题。文中展示了如何通过交替方向乘子法(ADMM)进行分布式求解,确保各微网隐私安全。此外,还探讨了电转气(P2G)和碳捕集(CCS)设备的应用,以实现低碳调度。最后,通过具体代码示例解释了模型的构建、求解及优化过程。 适合人群:对电力系统优化、博弈论、MATLAB编程感兴趣的科研人员和技术开发者。 使用场景及目标:适用于希望深入了解多微网电能共享优化策略的研究者,旨在提高微网联盟的整体效益并实现公平合理的收益分配。同时,该策略有助于降低碳排放,提升系统的环境友好性和经济性。 其他说明:文章提供了详细的代码注释和调试技巧,帮助读者更好地理解和实现这一复杂的优化策略。
内容概要:本文详细介绍了如何利用MATLAB进行六轴机械臂的视觉控制系统仿真。首先,通过MATLAB的图像处理工具箱捕捉并处理实时视频流,使用HSV颜色空间进行颜色阈值处理,从而定位红色小球的位置。然后,借助Robotics Toolbox中的逆运动学模块,将摄像头获取的目标位置转换为机械臂的关节角度,确保机械臂能够精准地追踪目标。此外,还讨论了路径规划的方法,如使用五次多项式插值和平滑滤波器,使机械臂的动作更加流畅。文中强调了实际应用中可能遇到的问题及其解决方法,如奇异点处理、坐标系转换和机械臂的速度限制等。 适合人群:具有一定编程基础和技术背景的研究人员、工程师以及对机器人视觉控制感兴趣的开发者。 使用场景及目标:适用于希望在MATLAB环境中快速搭建和测试机械臂视觉控制系统的科研人员和工程师。主要目标是掌握从图像处理到机械臂控制的完整流程,理解各模块的工作原理,并能够在实际项目中应用。 其他说明:本文不仅提供了详细的代码示例,还分享了许多实用的经验和技巧,帮助读者更好地理解和优化仿真系统。同时提醒读者注意仿真与现实之间的差异,如摄像头延迟、机械臂传动误差等问题。
KUKA机器人相关文档
KUKA机器人相关文档
内容概要:本文详细介绍了三相变流器的模型预测控制(MPC)在Matlab/Simulink环境下的实现过程。首先,初始化程序设置了关键参数,如直流母线电压、开关频率和控制周期等,确保系统的稳定性和效率。接着,通过MPC_sfun.c实现了核心控制算法,采用状态空间模型进行滚动预测,提高了系统的动态响应能力。最后,利用out.m生成高质量的仿真结果图,展示了负载突变时的快速恢复特性,并提供了优化建议,如调整代价函数权重和引入软约束等。 适合人群:电力电子工程师、控制系统研究人员以及对MPC感兴趣的科研工作者。 使用场景及目标:适用于需要精确控制电压电流的场合,如电动汽车充电站、风力发电系统等。主要目标是提高系统的动态响应速度、降低总谐波失真(THD),并在性能和计算负担之间取得平衡。 其他说明:文中提到了一些实用技巧,如控制周期的选择、预测步长的优化、图形绘制的最佳实践等,有助于读者更好地理解和应用MPC控制策略。同时,强调了在实际应用中需要注意的问题,如避免过高开关频率导致器件损坏等。
网络炒作策划要点解析.ppt
内容概要:本文详细介绍了三菱Q03UDE PLC使用SFC(顺序功能图)编程方法在16轴伺服控制系统中的应用。文章首先概述了硬件配置,包括500个IO点、16轴伺服控制以及触摸屏的画面编程。接着深入探讨了SFC编程的具体实现方式,如将复杂的轴控制分解为独立的流程块,利用并行结构解决多轴同步问题,通过触摸屏实时监控和反馈SFC步状态,以及如何高效管理和复用输出点。此外,文章还讨论了SFC在状态管理和报警处理方面的优势,并提供了具体的代码示例来展示其实现细节。最后,作者分享了一些实用技巧和注意事项,强调了SFC编程相比传统梯形图的优势。 适合人群:从事工业自动化控制系统的工程师和技术人员,尤其是对三菱PLC和SFC编程感兴趣的读者。 使用场景及目标:适用于需要进行复杂多轴伺服控制项目的工程师,旨在提高调试效率、减少信号冲突、缩短新人培养周期,并提供一种更加直观和高效的编程方法。 其他说明:文中提到的实际项目经验有助于读者更好地理解和应用SFC编程技术,同时也提醒了一些常见的错误和陷阱,帮助读者避免不必要的麻烦。
内容概要:本文详细介绍了如何使用LabVIEW实现与三菱FX3U PLC的串口通讯,采用Modbus无协议通讯方式进行简单读写操作。主要内容包括PLC通讯参数配置、LabVIEW工程结构搭建、Modbus报文构造方法以及具体的读写数据模块实现。文中提供了详细的代码示例和注意事项,帮助读者快速理解和实践这一通讯过程。 适合人群:对工业自动化有一定兴趣的技术人员,尤其是熟悉LabVIEW和三菱PLC的工程师。 使用场景及目标:适用于需要将LabVIEW作为上位机与三菱FX3U PLC进行串口通讯的应用场合,如工业控制系统、实验教学等。主要目标是掌握Modbus协议的基础知识及其在LabVIEW中的具体实现。 其他说明:文章还提供了一些常见的错误排查方法和实用技巧,如CRC校验的处理、地址偏移量的注意事项等。此外,附带了完整的源码供读者下载和参考。
图像检索_基于零样本开集的草图图像检索系统实现_附项目源码+流程教程_优质项目实战
基于C语言写的电话簿程序
包括:源程序工程文件、Proteus仿真工程文件、配套技术手册等 1、采用51单片机作为主控芯片; 2、采用1602液晶显示检测电压值,范围0~20V; 3、采用ADC0808进行模数转换;
内容概要:本文介绍了一个专业的剧本杀创作作家AI。它能根据客户需求创作各种风格和难度的剧本杀剧本,并提供创作建议和修改意见。其目标是创造引人入胜、逻辑严密的剧本体验。它的工作流程包括接收理解剧本要求、创作剧本框架情节、设计角色背景线索任务剧情走向、提供修改完善建议、确保剧本可玩性和故事连贯性。它需保证剧本原创、符合道德法律标准并在规定时间内完成创作。它具备剧本创作技巧、角色构建理解、线索悬念编织、文学知识和创意思维、不同文化背景下剧本风格掌握以及剧本杀游戏机制和玩家心理熟悉等技能。; 适合人群:有剧本杀创作需求的人群,如剧本杀爱好者、创作者等。; 使用场景及目标:①为用户提供符合要求的剧本杀剧本创作服务;②帮助用户完善剧本杀剧本,提高剧本质量。; 阅读建议:此资源详细介绍了剧本杀创作作家AI的功能和服务流程,用户可以依据自身需求与该AI合作,明确表达自己的创作需求并配合其工作流程。
内容概要:本文详细介绍了如何利用Matlab进行静态图片的美颜和特效处理。首先通过Viola-Jones算法进行人脸定位,然后采用双边滤波对皮肤进行磨皮处理,在HSV色彩空间中调整亮度以达到美白效果,最后运用小波变换将星空图等特效融合到图片中。整个过程中涉及多个图像处理技术和算法,如Haar特征、双边滤波、HSV转换、小波变换等。 适合人群:对图像处理感兴趣的初学者以及有一定Matlab基础的研发人员。 使用场景及目标:适用于希望深入了解图像处理原理并掌握具体实现方法的学习者;目标是能够独立完成简单的图像美化任务,如人像磨皮、美白、特效添加等。 其他说明:文中提供了完整的代码示例,帮助读者更好地理解和实践相关技术。同时强调了参数选择的重要性,并给出了合理的建议范围。