@sonikk 2013-7-16 0:27:38 ^_^
这是数据库:
mysql> use db_test1;
Database changed
mysql> select * from tb_tree;
+----+-------+------+-------+-------+--------+-------------+
| id | posnr | upp | downp | leftp | rightp | txt |
+----+-------+------+-------+-------+--------+-------------+
| 61 | 1 | 0 | 4 | NULL | 2 | 工程1 |
| 62 | 2 | 0 | 7 | 1 | 3 | 工程2 |
| 63 | 3 | 0 | NULL | 2 | NULL | 工程3 |
| 64 | 4 | 1 | 9 | NULL | 5 | 工程1-1 |
| 65 | 5 | 1 | 12 | 4 | 17 | 工程1-2 |
| 66 | 6 | 1 | NULL | 17 | NULL | 工程1-4 |
| 67 | 7 | 2 | 15 | NULL | 8 | 工程2-1 |
| 68 | 8 | 2 | NULL | 7 | NULL | 工程2-2 |
| 69 | 9 | 4 | NULL | NULL | 10 | 工程1-1-1 |
| 70 | 10 | 4 | 14 | 9 | 11 | 工程1-1-2 |
| 71 | 11 | 4 | NULL | 10 | NULL | 工程1-1-3 |
| 72 | 12 | 5 | NULL | NULL | 13 | 工程1-2-1 |
| 73 | 13 | 5 | NULL | 12 | NULL | 工程1-2-2 |
| 74 | 14 | 10 | NULL | NULL | NULL | 工程1-1-2-1 |
| 75 | 15 | 7 | NULL | NULL | 16 | 工程2-1-1 |
| 76 | 16 | 7 | NULL | 15 | NULL | 工程2-1-2 |
| 77 | 17 | 1 | NULL | 5 | 6 | 工程1-3 |
| 78 | 0 | NULL | 1 | NULL | NULL | 根节点 |
+----+-------+------+-------+-------+--------+-------------+
18 rows in set (0.02 sec)
这事对应的脚本文件:
db_test1.sql
/*
Navicat MySQL Data Transfer
Source Server : local
Source Server Version : 50612
Source Host : localhost:3306
Source Database : db_test1
Target Server Type : MYSQL
Target Server Version : 50612
File Encoding : 65001
Date: 2013-07-15 23:42:30
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb_tree`
-- ----------------------------
DROP TABLE IF EXISTS `tb_tree`;
CREATE TABLE `tb_tree` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`posnr` int(11) DEFAULT NULL,
`upp` int(11) DEFAULT NULL,
`downp` int(11) DEFAULT NULL,
`leftp` int(11) DEFAULT NULL,
`rightp` int(11) DEFAULT NULL,
`txt` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `posnr_UNIQUE` (`posnr`)
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_tree
-- ----------------------------
INSERT INTO `tb_tree` VALUES ('61', '1', '0', '4', null, '2', '工程1');
INSERT INTO `tb_tree` VALUES ('62', '2', '0', '7', '1', '3', '工程2');
INSERT INTO `tb_tree` VALUES ('63', '3', '0', null, '2', null, '工程3');
INSERT INTO `tb_tree` VALUES ('64', '4', '1', '9', null, '5', '工程1-1');
INSERT INTO `tb_tree` VALUES ('65', '5', '1', '12', '4', '17', '工程1-2');
INSERT INTO `tb_tree` VALUES ('66', '6', '1', null, '17', null, '工程1-4');
INSERT INTO `tb_tree` VALUES ('67', '7', '2', '15', null, '8', '工程2-1');
INSERT INTO `tb_tree` VALUES ('68', '8', '2', null, '7', null, '工程2-2');
INSERT INTO `tb_tree` VALUES ('69', '9', '4', null, null, '10', '工程1-1-1');
INSERT INTO `tb_tree` VALUES ('70', '10', '4', '14', '9', '11', '工程1-1-2');
INSERT INTO `tb_tree` VALUES ('71', '11', '4', null, '10', null, '工程1-1-3');
INSERT INTO `tb_tree` VALUES ('72', '12', '5', null, null, '13', '工程1-2-1');
INSERT INTO `tb_tree` VALUES ('73', '13', '5', null, '12', null, '工程1-2-2');
INSERT INTO `tb_tree` VALUES ('74', '14', '10', null, null, null, '工程1-1-2-1');
INSERT INTO `tb_tree` VALUES ('75', '15', '7', null, null, '16', '工程2-1-1');
INSERT INTO `tb_tree` VALUES ('76', '16', '7', null, '15', null, '工程2-1-2');
INSERT INTO `tb_tree` VALUES ('77', '17', '1', null, '5', '6', '工程1-3');
INSERT INTO `tb_tree` VALUES ('78', '0', null, '1', null, null, '根节点');
这是程序执行结果:
=======================
[table]:
id=61, posnr= 1, up= 0, down= 4, left= , right= 2, txt=工程1
id=62, posnr= 2, up= 0, down= 7, left= 1, right= 3, txt=工程2
id=63, posnr= 3, up= 0, down= , left= 2, right= , txt=工程3
id=64, posnr= 4, up= 1, down= 9, left= , right= 5, txt=工程1-1
id=65, posnr= 5, up= 1, down=12, left= 4, right=17, txt=工程1-2
id=66, posnr= 6, up= 1, down= , left=17, right= , txt=工程1-4
id=67, posnr= 7, up= 2, down=15, left= , right= 8, txt=工程2-1
id=68, posnr= 8, up= 2, down= , left= 7, right= , txt=工程2-2
id=69, posnr= 9, up= 4, down= , left= , right=10, txt=工程1-1-1
id=70, posnr=10, up= 4, down=14, left= 9, right=11, txt=工程1-1-2
id=71, posnr=11, up= 4, down= , left=10, right= , txt=工程1-1-3
id=72, posnr=12, up= 5, down= , left= , right=13, txt=工程1-2-1
id=73, posnr=13, up= 5, down= , left=12, right= , txt=工程1-2-2
id=74, posnr=14, up=10, down= , left= , right= , txt=工程1-1-2-1
id=75, posnr=15, up= 7, down= , left= , right=16, txt=工程2-1-1
id=76, posnr=16, up= 7, down= , left=15, right= , txt=工程2-1-2
id=77, posnr=17, up= 1, down= , left= 5, right= 6, txt=工程1-3
id=78, posnr= 0, up= , down= 1, left= , right= , txt=根节点
=======================
[travel]:
id=78, posnr= 0, up= , down= 1, left= , right= , txt=根节点
id=61, posnr= 1, up= 0, down= 4, left= , right= 2, txt=工程1
id=62, posnr= 2, up= 0, down= 7, left= 1, right= 3, txt=工程2
id=63, posnr= 3, up= 0, down= , left= 2, right= , txt=工程3
id=64, posnr= 4, up= 1, down= 9, left= , right= 5, txt=工程1-1
id=65, posnr= 5, up= 1, down=12, left= 4, right=17, txt=工程1-2
id=77, posnr=17, up= 1, down= , left= 5, right= 6, txt=工程1-3
id=66, posnr= 6, up= 1, down= , left=17, right= , txt=工程1-4
id=67, posnr= 7, up= 2, down=15, left= , right= 8, txt=工程2-1
id=68, posnr= 8, up= 2, down= , left= 7, right= , txt=工程2-2
id=69, posnr= 9, up= 4, down= , left= , right=10, txt=工程1-1-1
id=70, posnr=10, up= 4, down=14, left= 9, right=11, txt=工程1-1-2
id=71, posnr=11, up= 4, down= , left=10, right= , txt=工程1-1-3
id=72, posnr=12, up= 5, down= , left= , right=13, txt=工程1-2-1
id=73, posnr=13, up= 5, down= , left=12, right= , txt=工程1-2-2
id=75, posnr=15, up= 7, down= , left= , right=16, txt=工程2-1-1
id=76, posnr=16, up= 7, down= , left=15, right= , txt=工程2-1-2
id=74, posnr=14, up=10, down= , left= , right= , txt=工程1-1-2-1
=======================
[result]:
id=78, posnr= 0, up= , down= 1, left= , right= , txt=根节点
id=61, posnr= 1, up= 0, down= 4, left= , right= 2, txt=工程1
id=64, posnr= 4, up= 1, down= 9, left= , right= 5, txt=工程1-1
id=69, posnr= 9, up= 4, down= , left= , right=10, txt=工程1-1-1
id=70, posnr=10, up= 4, down=14, left= 9, right=11, txt=工程1-1-2
id=74, posnr=14, up=10, down= , left= , right= , txt=工程1-1-2-1
id=71, posnr=11, up= 4, down= , left=10, right= , txt=工程1-1-3
id=65, posnr= 5, up= 1, down=12, left= 4, right=17, txt=工程1-2
id=72, posnr=12, up= 5, down= , left= , right=13, txt=工程1-2-1
id=73, posnr=13, up= 5, down= , left=12, right= , txt=工程1-2-2
id=77, posnr=17, up= 1, down= , left= 5, right= 6, txt=工程1-3
id=66, posnr= 6, up= 1, down= , left=17, right= , txt=工程1-4
id=62, posnr= 2, up= 0, down= 7, left= 1, right= 3, txt=工程2
id=67, posnr= 7, up= 2, down=15, left= , right= 8, txt=工程2-1
id=75, posnr=15, up= 7, down= , left= , right=16, txt=工程2-1-1
id=76, posnr=16, up= 7, down= , left=15, right= , txt=工程2-1-2
id=68, posnr= 8, up= 2, down= , left= 7, right= , txt=工程2-2
id=63, posnr= 3, up= 0, down= , left= 2, right= , txt=工程3
程序的关键算法主要是树的层次遍历(队列) + 链表预算位置+偏移量插入:
树的逻辑结构:
向逆时针方向旋转90度后进行找规律:
DBHelper.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Data;
namespace DataBaseTree
{
class DBHelper
{
public const string server = "127.0.0.1";
public const string username = "root";
public const string password = "";
public const string database = "db_test1";
MySqlConnection mConn = null;
public DBHelper()
{
if (mConn == null)
{
mConn = getConn();
}
}
~DBHelper()
{
if (mConn != null)
{
mConn.Close();
mConn.Dispose();
mConn = null;
}
}
public void exeNonSql(string sql)
{
exeNonSql(mConn, sql);
}
// 绑定并查看数据
public void bindData(DataGridView dgv, string sql)
{
bindData(dgv, mConn, sql);
}
// 执行sql语句获取内存数据表格
public DataTable queryDataTable(string sql, string table_name)
{
return queryDataTable(mConn, sql, table_name);
}
// ----------------------------------------------------------------
// 获取链接
private MySqlConnection getConn()
{
MySqlConnection conn = new MySqlConnection();
string connStr = String.Format("server={0};uid={1};pwd={2};database={3}", server, username, password, database);
conn.ConnectionString = connStr;
conn.Open();
return conn;
}
// 指定一条数据库语句
private void exeNonSql(MySqlConnection conn, string sql)
{
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
// 绑定并查看数据
private void bindData(DataGridView dgv, MySqlConnection conn, string sql)
{
MySqlDataAdapter mda = new MySqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
mda.Fill(ds, "tb1");
dgv.DataSource = ds.Tables["tb1"];
}
// 执行sql语句查询得到内存数据表格
private DataTable queryDataTable(MySqlConnection conn, string sql, string table_name)
{
MySqlDataAdapter mda = new MySqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
mda.Fill(ds, table_name);
return ds.Tables[table_name];
}
}
}
TreeNode.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataBaseTree
{
// 定义tb_tree的表结构
public class TreeNode
{
public int id;
public string posnr; // 不重复字段
public string up;
public string down;
public string left;
public string right;
public string txt;
// 构造函数
public TreeNode()
{
this.id = 0;
this.posnr = "";
this.up = "";
this.down = "";
this.left = "";
this.right = "";
this.txt = "";
}
// 赋值
public void set(TreeNode rdata)
{
this.id = rdata.id;
this.posnr = rdata.posnr;
this.up = rdata.up;
this.down = rdata.down;
this.left = rdata.left;
this.right = rdata.right;
this.txt = rdata.txt;
}
}
}
TreeTable.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
namespace DataBaseTree
{
class TreeTable
{
// 获取数组
public TreeNode[] getMemoryTableArr(DataTable tb)
{
TreeNode[] arr = new TreeNode[tb.Rows.Count];
for (int i = 0; i < tb.Rows.Count; i++)
{
//Console.WriteLine("posnr: " + );
TreeNode rdata = new TreeNode();
rdata.id = Convert.ToInt32(tb.Rows[i]["id"]);
rdata.posnr = tb.Rows[i]["posnr"].ToString();
rdata.up = tb.Rows[i]["upp"].ToString();
rdata.down = tb.Rows[i]["downp"].ToString();
rdata.left = tb.Rows[i]["leftp"].ToString();
rdata.right = tb.Rows[i]["rightp"].ToString();
rdata.txt = tb.Rows[i]["txt"].ToString();
arr[i] = rdata;
}
return arr;
}
public TreeNode[] getTreeData()
{
DBHelper db = new DBHelper();
// 执行sql语句
db.exeNonSql("set names utf8;");
db.exeNonSql("use db_test1;");
string str_sql = "select * from tb_tree;";
// mDb.bindData(dgv1, str_sql);
DataTable tb1 = db.queryDataTable(str_sql, "tb1");
// 释放数据库类
db = null;
// 从内存表中提取数据,转化为对象数组
TreeNode[] arr_table = getMemoryTableArr(tb1);
return arr_table;
}
// 入队列
private void enQueue(ArrayList arr, TreeNode rdata)
{
arr.Insert(arr.Count, rdata);
}
// 出队列
private TreeNode deQueue(ArrayList arr)
{
TreeNode rdata = null;
if (arr.Count > 0)
{
rdata = (TreeNode)arr[0];
arr.RemoveAt(0);
}
return rdata;
}
// 根据Posnr查找节点
TreeNode findPosnr(TreeNode[] arr, string posnr)
{
TreeNode rdata = null;
for (int i = 0; i < arr.Length; i++)
{
if (arr[i].posnr == posnr)
{
rdata = arr[i];
break;
}
}
return rdata;
}
// 查找posnr对应 在队列中的位置
private int findIndexByPosnr(ArrayList arr, string posnr)
{
int index = -1;
for (int i = 0; i < arr.Count; i++)
{
TreeNode rdata = (TreeNode)arr[i];
if (rdata.posnr == posnr)
{
index = i;
break;
}
}
return index;
}
// 按照right链表连接排序
public TreeNode[] sortLinkByRight(TreeNode[] arr)
{
// 没有数据不排序,返回null
if (arr == null || arr.Length == 0)
return null;
// 只有一个子节点,返回本身,不排序
if (arr.Length == 1)
{
return arr;
}
TreeNode[] arr_ret = new TreeNode[arr.Length];
// 找left为空的posnr
string next_posnr = null;
for (int i = 0; i < arr.Length; i++)
{
if (arr[i].left == "")
{
next_posnr = arr[i].posnr;
break;
}
}
int j = 0;
do
{
TreeNode rdata = findPosnr(arr, next_posnr);
arr_ret[j] = rdata;
j++;
next_posnr = rdata.right;
} while (next_posnr != "");
return arr_ret;
}
public TreeNode[] findChildByUp(TreeNode[] arr, string up)
{
/*
int count = 0;
// 统计儿子个数
for (int i = 0; i < arr.Length; i++)
{
if (arr[i].up == up)
{
count++;
}
}
// 创建儿子数组
RData[] arr_ret = new RData[count];
int j = 0;
for (int i = 0; i < arr.Length; i++)
{
if (arr[i].up == up)
{
arr_ret[j] = arr[i];
j++;
}
}
return arr_ret;
*/
ArrayList arr_ret = new ArrayList();
for (int i = 0; i < arr.Length; i++)
{
if (arr[i].up == up)
{
arr_ret.Add(arr[i]);
}
}
return (TreeNode[])arr_ret.ToArray(typeof(TreeNode));
}
// 查找up,并返回有序的数据集
public TreeNode[] findChildByUpOrdered(TreeNode[] arr, string up)
{
// 查找
TreeNode[] arr_ret = findChildByUp(arr, up);
//orderByLeft(arr_ret);
arr_ret = sortLinkByRight(arr_ret);
return arr_ret;
}
// 层次遍历
public void travel(TreeNode[] arr_table, ArrayList queue, ArrayList list_result)
{
TreeNode rdata = null;
do
{
// 出队一个节点
rdata = deQueue(queue);
if (rdata == null)
continue;
// 按层次遍历输出
Console.WriteLine("id={0,2}, posnr={1,2}, up={2,2}, down={3,2}, left={4,2}, right={5,2}, txt={6,2}", rdata.id, rdata.posnr, rdata.up, rdata.down, rdata.left, rdata.right, rdata.txt);
// 查找出队节点的儿子节点
TreeNode[] children = findChildByUpOrdered(arr_table, rdata.posnr); // up = null
// 查找posnr对应的的位置
int insert_index = findIndexByPosnr(list_result, rdata.posnr);
if (children != null)
{
for (int i = 0; i < children.Length; i++)
{
// 儿子入队
enQueue(queue, children[i]);
// 将儿子插入到posnr节点之后,计算: 插入位置 = 父位置 + 偏移量
list_result.Insert(insert_index + i + 1, children[i]);
}
}
} while (rdata != null);
}
// 格式化输出全部数组
public void printArray(TreeNode[] arr)
{
for (int i = 0; i < arr.Length; ++i)
{
TreeNode rdata = arr[i];
Console.WriteLine("id={0,2}, posnr={1,2}, up={2,2}, down={3,2}, left={4,2}, right={5,2}, txt={6}", rdata.id, rdata.posnr, rdata.up, rdata.down, rdata.left, rdata.right, rdata.txt);
}
}
// 格式化输出ArrayList
public void printArray(ArrayList al)
{
for (int i = 0; i < al.Count; ++i)
{
TreeNode rdata = (TreeNode)al[i];
Console.WriteLine("id={0,2}, posnr={1,2}, up={2,2}, down={3,2}, left={4,2}, right={5,2}, txt={6}", rdata.id, rdata.posnr, rdata.up, rdata.down, rdata.left, rdata.right, rdata.txt);
}
}
// [main] 生成数据树形结构的菜单
public void GenerateTreeMenu()
{
TreeNode[] arr_table = getTreeData();
Console.WriteLine("=======================");
Console.WriteLine("[table]:");
printArray(arr_table);
// 创建队列
ArrayList queue = new ArrayList();
// 创建结果List
ArrayList list_result = new ArrayList();
// 建立初始队列, 插入根节点 (指定up为NULL的)
TreeNode[] children = findChildByUpOrdered(arr_table, ""); // up = null
for (int i = 0; i < children.Length; i++)
{
enQueue(queue, children[i]);
// 插入结果
list_result.Add(children[i]);
}
Console.WriteLine("=======================");
Console.WriteLine("[travel]:");
travel(arr_table, queue, list_result);
Console.WriteLine("=======================");
Console.WriteLine("[result]:");
printArray(list_result);
}
}
}
@sonikk 2013-7-16 0:27:38 ^_^ /~