仿位操作分类算法

此算法折衷于位运算操作算法的执行效率和算法复杂度

高效分类算法见：http://blog.csdn.net/sea0x/archive/2008/01/07/2028196.aspx

参照位操作原理，以固定位数做为某层级分类区域组成分类编码，以体现分类的顺序，层级，父子关系情况。

如下使用四位为一层级的实例来分析其应用：

表一：
CREATE TABLE [tbl_GiftProductClass] (
 [GiftProductClassID] [int] IDENTITY (1, 1) NOT NULL ,
 [GiftProductClassName] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [GiftProductClassCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

GiftProductClassID  GiftProductClassName    GiftProductClassCode
------------------ --------------------------- --------------------
11                 数码电子                     0001
28                 钟表                  00010001
188                时钟                         000100010001
189                手表                        000100010002

以上数据我们很容易看出

“时钟”和“手表” 是属于“钟表”的子类，而“钟表”又是“数码电子”的子类

基于分类的各种应用操用SQL如下：

取得子类，含孙子节点
SELECT * FROM [tbl_GiftProductClass] WHERE GiftProductClassCode LIKE '0001%' ORDER BY GiftProductClassCode ASC

仅取得子类，不含孙子节点
SELECT * FROM [tbl_GiftProductClass] WHERE GiftProductClassCode LIKE '0001____' ORDER BY GiftProductClassCode ASC

仅取得孙子节点 不含子类节点
SELECT * FROM [tbl_GiftProductClass] WHERE GiftProductClassCode LIKE '0001________' ORDER BY GiftProductClassCode ASC

可归纳为取得相对某分类某层(level)所有节点
WHERE GiftProductClassCode LIKE '0001" + new string('_',level*4) + "'

取得某类所有长辈类
SELECT * FROM [tbl_GiftProductClass]  WHERE (GiftProductClassCode = LEFT('000100010002', LEN(GiftProductClassCode))) ORDER BY GiftProductClassCode

 

修改一个分类：

public static bool Update(clsGiftProductClass prmEntity, int parentid) { string now_code = prmEntity.GiftProductClassCode; clsGiftProductClass parentcls = clsGiftProductClass.GetByKey(parentid); string parentcode = parentcls.GiftProductClassCode; if (parentcode != now_code.Substring(0, now_code.Length - 4)) { string new_code = ""; new_code = GetNextCode(parentcode); //更新与之有关的变动编码 doUpdateCode(now_code, new_code); prmEntity.GiftProductClassCode = new_code; } return doUpdate(prmEntity); } /// <summary> /// 更新有关的变动编码 /// </summary> /// <param name="now_code"></param> /// <param name="new_code"></param> private static void doUpdateCode(string now_code, string new_code) { DataTableManage SaveDTM = new DataTableManage(); string sql = "Update [tbl_GiftProductClass] set GiftProductClassCode='" + new_code + "'+Right(GiftProductClassCode,Len(GiftProductClassCode)-" + now_code.Length + ") where GiftProductClassCode like '%" + now_code + "%'"; SaveDTM.Sql = sql; SaveDTM.Update(); }

添加分类时取得下一个分类代码的方法（C#）：
private static string GetNextCode(string parentcode) { string nextcode = ""; int iCode = 0; DBase dbo = new DBase(); string sql = string.Format("SELECT TOP 1 GiftProductClassCode FROM [tbl_GiftProductClass] WHERE GiftProductClassCode LIKE '{0}____' ORDER BY GiftProductClassCode DESC", parentcode); IDataReader dr = dbo.ExecuteReader(sql); if (dr.Read()) { iCode = Convert.ToInt32(dr["GiftProductClassCode"].ToString().Substring(parentcode.Length)) + 1; nextcode = "000" + iCode.ToString(); nextcode = nextcode.Substring(nextcode.Length - 4); nextcode = parentcode + nextcode; } else { nextcode = parentcode + "0001"; } dr.Dispose(); dr = null; dbo.CloseConn(); dbo = null; return nextcode; }

上移下移某个分类方法（C#）：
private static bool MoveGo(bool isUp, clsGiftProductClass prmEntity) { bool ret = false; string code = prmEntity._giftproductclasscode; int codelen = code.Length; if (codelen == 0 || codelen % 4 != 0) return false; string strNB = new string('-', codelen); string strWhere = ""; if (codelen > 4) { strWhere = string.Format("and left(GiftProductClassCode,{0})='{1}'", codelen - 4, code.Substring(0, codelen - 4)); } string sql = ""; if (isUp) //上移 { sql = string.Format("select top 1 GiftProductClassCode from tbl_GiftProductClass where len(GiftProductClassCode)={0} and GiftProductClassCode<'{1}' {2} order by GiftProductClassCode desc", codelen, code, strWhere); } else //下移 { sql = string.Format("select top 1 GiftProductClassCode from tbl_GiftProductClass where len(GiftProductClassCode)={0} and GiftProductClassCode>'{1}' {2} order by GiftProductClassCode asc", codelen, code, strWhere); } string strTmp = ""; DBase dbo = new DBase(); IDataReader dr = dbo.ExecuteReader(sql); if (dr.Read()) { strTmp = dr[0].ToString(); } dr.Dispose(); dr = null; if (strTmp != "" && (strTmp.Length % 4 == 0)) { //移动上层栏目 string MoveParentSQL = string.Format("Update tbl_GiftProductClass set GiftProductClassCode='{0}'+SUBSTRING(GiftProductClassCode,{1},len(GiftProductClassCode)) WHERE left(GiftProductClassCode,{2})='{3}'", strNB, codelen + 1, codelen, strTmp); dbo.ExecSql(MoveParentSQL); //更新目标栏目 string UpdateTargetSQL = string.Format("Update tbl_GiftProductClass set GiftProductClassCode='{0}'+SUBSTRING(GiftProductClassCode,{1},len(GiftProductClassCode)) WHERE left(GiftProductClassCode,{2})='{3}'", strTmp, codelen + 1, codelen, code); dbo.ExecSql(UpdateTargetSQL); //更新上层栏目 string UpdateParentSQL = string.Format("Update tbl_GiftProductClass set GiftProductClassCode='{0}'+SUBSTRING(GiftProductClassCode,{1},len(GiftProductClassCode)) WHERE left(GiftProductClassCode,{2})='{3}'", code, codelen + 1, codelen, strNB); dbo.ExecSql(UpdateParentSQL); ret = true; } dbo.CloseConn(); dbo = null; return ret; }

对于产品应用呢，再来建实例表。

表二：
CREATE TABLE [tbl_GiftProduct] (
 [GiftProductID] [int] IDENTITY (1, 1) NOT NULL ,
 [GiftProductName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [GiftProductClassID] [int] NULL
) ON [PRIMARY]
GO

GiftProductID   GiftProductName      GiftProductClassID
------------------ --------------------- --------------------
213                指南针石英钟               188
321                罗西尼手表                  189

 

另建立一个视图方便后面使用：

CREATE VIEW dbo.vi_GiftProdcut
AS
SELECT dbo.tbl_GiftProduct.*, dbo.tbl_GiftProductClass.GiftProductClassName,
      dbo.tbl_GiftProductClass.GiftProductClassCode
FROM dbo.tbl_GiftProductClass INNER JOIN
      dbo.tbl_GiftProduct ON
      dbo.tbl_GiftProductClass.GiftProductClassID = dbo.tbl_GiftProduct.GiftProductClassID
GO

现在我们要查询出产品就比较方便了

查所有“数码电子”产品
SELECT * FROM [vi_GiftProdcut] WHERE GiftProductClassCode LIKE '0001%'

查所有“钟表”产品
SELECT * FROM [vi_GiftProdcut] WHERE GiftProductClassCode LIKE '00010001%'