你的位置:首页 > ASP.net教程

[ASP.net教程]议:如何将树形菜单形式的数据转化成HTML的二维表(相同内容需合并单元格)


  一般做OA类管理系统,经常涉及到“组织架构”的概念,那么像这种有上下层级关系的数据一般会做成树形菜单的方式显示,底层代码必定会用到递归算法。这篇随笔的目的就是要谈谈除了用树形菜单来显示这种上下层级关系的数据,还有其他的显示方式吗?答案是有的,例如即将要谈到的二维表显示方式,同时也是本随笔的核心内容。

  首先来看二维表的显示效果图:

如果看到这里,你觉得这就是你想要的显示效果,或者对此比较感兴趣。请接着往下看的实现步骤:

1.取出所有的数据临时保存到DataTable中,即内存中,拼html时直接查DataTable中的数据,不用去反复读取数据库,提高效率;

2.根据节点编号获取该节点下所有的末端子节点编号,因为末端子节点的个数就决定了<table>的行数;

3.将查到的末端子节点编号的所有父节点编号也查出来,拼接起来,就知道了<table>的每行的列数;

4.对节点的编号进行排序,这样可以把每列下的相同行的节点编号集中在一起,方便后面的合并单元格;

5.遍历行和列,合并每列相同行的单元格;

6.最后一步,拼接空白的列。

如下是具体代码实现过程:

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Data; 6 using System.Text; 7  8 /// <summary> 9 ///KpiTable 的摘要说明 10 /// </summary> 11 public class KpiTable 12 { 13   DBUtility.SQLHelper sqlhelper = new DBUtility.SQLHelper(); 14   public string GetKpiTable(string kpino, string businessno, string tenderno) 15   { 16     //1.取出所有的数据临时保存到dt2,即内存中,拼html时直接查dt2中的数据,不用去反复读取数据库,提高效率 17     DataTable dt2 = new DataTable(); 18     { 19       DataSet ds = new DataSet(); 20       int i = sqlhelper.RunSQL(string.Format("select count(1) from sys.objects where name = 'KpiValue{0}'", businessno)); 21       if (string.IsNullOrEmpty(tenderno) || i<1)//如果有标段编号就要把KpiValueXXX表里的KpiValue1查出来显示 22       { 23         sqlhelper.RunSQL(string.Format(@"select KpiNo,KpiName,KpiInfo,ISNULL(KpiParentNo,0) KpiParentNo,KpiWeight,0 KpiValue1,'' KpiMethod,'' KpiSampleType,'' KpiRule,'' KpiCriterion,'' KpiAreaRule,'' KpiSampleRule from KpiTree{0} 24 union all select KpiNo,KpiName,KpiInfo,ISNULL(KpiParentNo,0) KpiParentNo,KpiWeight,0 KpiValue1,KpiMethod,KpiSampleType,KpiRule,KpiCriterion,KpiAreaRule,KpiSampleRule from Kpi{0}",businessno), ref ds); 25       } 26       else 27       { 28         sqlhelper.RunSQL(string.Format(@"select * from ( 29 select KpiNo,KpiName,KpiInfo,ISNULL(KpiParentNo,0) KpiParentNo,KpiWeight,0 KpiValue1,'' KpiMethod,'' KpiSampleType,'' KpiRule,'' KpiCriterion,'' KpiAreaRule,'' KpiSampleRule from KpiTree{0} 30 union all select KpiNo,KpiName,KpiInfo,ISNULL(KpiParentNo,0) KpiParentNo,KpiWeight,0 KpiValue1,KpiMethod,KpiSampleType,KpiRule,KpiCriterion,KpiAreaRule,KpiSampleRule from Kpi{0} 31 ) a left join KpiValue{0} b on a.kpino = b.kpino and TenderNo='{1}'", businessno, tenderno), ref ds); 32       } 33       dt2 = ds.Tables[0]; 34     } 35     //2.根据节点编号获取该节点下所有的末端子节点编号,因为末端子节点的个数就决定了table的行数 36     DataTable dt = new DataTable(); 37     { 38       DataSet ds = new DataSet(); 39       if (string.IsNullOrEmpty(kpino) || kpino == "0") 40       { 41         sqlhelper.RunSQL(string.Format(@"select kpino from (select kpino from kpitree{0} union all select kpino from Kpi{0}) t where kpino not in (select isnull(KpiParentNo,0) from (select KpiParentNo from kpitree{0} union all select KpiParentNo from Kpi{0}) t)", businessno), ref ds); 42       } 43       else 44       { 45         string endKpiNo = RecursionEndKpiNo(dt2, kpino).Trim(','); 46         endKpiNo = endKpiNo == "" ? "0" : endKpiNo; 47         string kpinos = string.Empty; 48         foreach (string str in endKpiNo.Split(',')) { kpinos += "'" + str + "',"; } 49         kpinos = kpinos.Trim(','); 50         sqlhelper.RunSQL(string.Format(@"select kpino from (select kpino from kpitree{0} union all select kpino from Kpi{0}) t where kpino not in (select isnull(KpiParentNo,0) from (select KpiParentNo from kpitree{0} union all select KpiParentNo from Kpi{0}) t) and kpino in ({1})", businessno, kpinos), ref ds); 51       } 52       dt = ds.Tables[0]; 53     } 54     //3.将查到的末端子节点编号的所有父节点编号也查出来,拼接起来,就知道了table的每行的列数 55     foreach (DataRow row in dt.Rows) 56     { 57       row["kpino"] = Recursion(dt2, row["kpino"]); 58     } 59     //4.对编号进行排序,这样可以把每列下的相同行的节点编号集中在一起,方便后面的合并单元格 60     var drArray = dt.Select("1=1", "kpino"); 61     //5.限制输出kpino之前的父节点信息 62     foreach (DataRow row in drArray) 63     { 64       int index = row["kpino"].ToString().IndexOf(kpino); 65       if (index > -1) 66       { 67         row["kpino"] = row["kpino"].ToString().Substring(index); 68       } 69     } 70     //6.遍历行和列 71     int maxCount = GetMaxCount(drArray); 72     StringBuilder sbJson = new StringBuilder(); 73     for (int i = 0; i < drArray.Length; i++) 74     { 75       DataRow row = drArray[i]; 76       sbJson.Append("<tr>"); 77       var kpinoArray = row["kpino"].ToString().Trim(',').Split(','); 78       int kpinoArrayLenth = kpinoArray.Length; 79       for (int j = 0; j < kpinoArrayLenth; j++) 80       { 81         string str = kpinoArray[j]; 82         if (str != "0") 83         { 84           var dr = dt2.Select("kpino='" + str + "'"); 85           //合并每列相同行的单元格 86           if (dr.Length > 0 && !EqualUpColumnValue(i, j, drArray)) 87           { 88             double kpiWeight = GetKpiWeight(dt2, str); 89             double kpiValue = GetKpiValue(dt2, str); 90             string kpiValueStr = string.IsNullOrEmpty(tenderno) ? "" : "[" + (kpiValue * kpiWeight).ToString("0.00") + "]"; 91             string kpiDes = GetKpiDes(dt2, str); 92             sbJson.Append(string.Format("<td rowspan='{0}'>{1}({2}%){3}{4}</td>", GetColspan(i, j, drArray), dr[0]["kpiname"], (kpiWeight * 100).ToString("0.00"), kpiValueStr, kpiDes)); 93           } 94         } 95       } 96       //拼接空白的列 97       for (int j = 0; j < maxCount - kpinoArrayLenth; j++) 98       { 99         sbJson.Append("<td></td>");100       }101       sbJson.Append("</tr>");102     }103     return "<table id='kpitable' border='1px'>" + sbJson.ToString() + "</table>";104   }105 106   private string RecursionEndKpiNo(DataTable dt, object parentId)107   {108     StringBuilder sbJson = new StringBuilder();109 110     DataRow[] rows = dt.Select(string.Format("KpiParentNo = '" + parentId + "'"));111     if (rows.Length > 0)112     {113       foreach (DataRow row in rows)114       {115         string str = RecursionEndKpiNo(dt, row["kpino"]);116         sbJson.Append("" + row["kpino"] + "," + str);117       }118     }119     return sbJson.ToString();120   }121   private bool IsChild(DataTable dt, string parentId)122   {123     DataRow[] rows = dt.Select(string.Format("KpiParentNo = '" + parentId + "'"));124     if (rows.Length > 0)125     {126       return true;127     }128     else129     {130       return false;131     }132   }133 134   private string Recursion(DataTable dt, object parentId)135   {136     StringBuilder sbJson = new StringBuilder();137 138     DataRow[] rows = dt.Select("kpino = '" + parentId + "'");139     if (rows.Length > 0)140     {141       if (rows[0]["KpiParentNo"].ToString() == "0" || rows[0]["KpiParentNo"].ToString() == "")142       {143         sbJson.Append("0,");144       }145       else146       {147         sbJson.Append(Recursion(dt, rows[0]["KpiParentNo"]));148       }149     }150     sbJson.Append(parentId.ToString() + ",");151     return sbJson.ToString();152   }153   private int GetMaxCount(DataRow[] drArray)154   {155     int temp = 0;156     foreach (DataRow row in drArray)157     {158       int count = row["kpino"].ToString().Trim(',').Split(',').Length;159       if (count > temp)160       {161         temp = count;162       }163     }164     return temp;165   }166   private bool EqualUpColumnValue(int rowIndex, int colIndex, DataRow[] drArray)167   {168     if (rowIndex == 0)169     {170       return false;171     }172 173     string[] kpinoArray = drArray[rowIndex - 1]["kpino"].ToString().Trim(',').Split(',');174     if (kpinoArray.Length > colIndex)175     {176       string upColumnValue = drArray[rowIndex]["kpino"].ToString().Trim(',').Split(',')[colIndex];177       if (upColumnValue == kpinoArray[colIndex])178       {179         return true;180       }181       else182       {183         return false;184       }185     }186     else187     {188       return false;189     }190   }191   private int GetColspan(int rowIndex, int colIndex, DataRow[] drArray)192   {193     int colspan = 1;194     string[] kpinoArray = drArray[rowIndex]["kpino"].ToString().Trim(',').Split(',');195 196     while (rowIndex < drArray.Length - 1)197     {198       string[] kpinoArray2 = drArray[rowIndex + 1]["kpino"].ToString().Trim(',').Split(',');199       if (kpinoArray2.Length > colIndex)200       {201         if (kpinoArray[colIndex] == kpinoArray2[colIndex])202         {203           colspan++;204         }205         else206         {207           break;208         }209       }210       else211       {212         break;213       }214       rowIndex++;215     }216     return colspan;217   }218   private double GetKpiWeight(DataTable dt, string kpino)219   {220     double kpiWeight = 0;221     var drArray = dt.Select("kpino='" + kpino + "'");222     if (drArray.Length > 0)223     {224       string kpiParentNo = drArray[0]["KpiParentNo"].ToString();225       double kpino_KpiWeight = Convert.ToDouble(drArray[0]["KpiWeight"]);226       drArray = dt.Select("KpiParentNo='" + kpiParentNo + "'");227       if (drArray.Length > 0)228       {229         double result = 0;230         foreach (DataRow row in drArray)231         {232           result += Convert.ToDouble(row["KpiWeight"]);233         }234         kpiWeight = (kpino_KpiWeight / result);235       }236     }237     return kpiWeight;238   }239   private double GetKpiValue(DataTable dt, string kpino)240   {241     var drArray = dt.Select(string.Format("kpino='{0}'",kpino));242     if (drArray.Length>0)243     {244       return Convert.ToDouble(drArray[0]["KpiValue1"]);245     }246     return 0;247   }248   private string GetKpiDes(DataTable dt, string kpino)249   {250     string des = string.Empty;251     var drArray = dt.Select(string.Format("kpino='{0}'",kpino));252     if (drArray.Length>0)253     {254       string KpiMethod = drArray[0]["KpiMethod"].ToString();255       string KpiSampleType = drArray[0]["KpiSampleType"].ToString();256       string KpiRule = drArray[0]["KpiRule"].ToString();257       string KpiCriterion = drArray[0]["KpiCriterion"].ToString();258       string KpiAreaRule = drArray[0]["KpiAreaRule"].ToString();259       string KpiSampleRule = drArray[0]["KpiSampleRule"].ToString();260 261       //表格样式262       if (!string.IsNullOrEmpty(KpiMethod))263       {264         //des += "<table id='kpitabledes'>";265         //des += "<tr><td>计算方法:</td><td>" + KpiMethod   +"</td></tr>";266         //des += "<tr><td>采样类别:</td><td>" + KpiSampleType + "</td></tr>";267         //des += "<tr><td>评价标准:</td><td>" + KpiRule + "</td></tr>";268         //des += "<tr><td>规范要点:</td><td>" + KpiCriterion + "</td></tr>";269         //des += "<tr><td>测区规则:</td><td>" + KpiAreaRule + "</td></tr>";270         //des += "<tr><td>测点规则:</td><td>" + KpiSampleRule + "</td></tr>";271         //des += "</table>";272       }273                   274       //换行样式        275       //if (!string.IsNullOrEmpty(KpiMethod)) { des += "<br />计算方法:" + KpiMethod; }276       //if (!string.IsNullOrEmpty(KpiSampleType)) { des += "<br />采样类别:" + KpiSampleType; }277       //if (!string.IsNullOrEmpty(KpiRule)) { des += "<br />评价标准:" + KpiRule; }278       //if (!string.IsNullOrEmpty(KpiCriterion)) { des += "<br />规范要点:" + KpiCriterion; }279       //if (!string.IsNullOrEmpty(KpiAreaRule)) { des += "<br />测区规则:" + KpiAreaRule; }280       //if (!string.IsNullOrEmpty(KpiSampleRule)) { des += "<br />测点规则:" + KpiSampleRule; } 281     }282     return des;283   }284 285 286 287   288 }

View Code

以及需要用到的表(sql脚本):

 1 USE [Evaluation] 2 GO 3 /****** Object: Table [dbo].[Kpi]  Script Date: 2016/3/25 16:06:04 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 CREATE TABLE [dbo].[Kpi]( 9   [KpiNo] [nvarchar](50) NOT NULL, 10   [KpiName] [nvarchar](50) NULL, 11   [KpiInfo] [nvarchar](100) NULL, 12   [KpiParentNo] [nvarchar](50) NULL, 13   [KpiMethod] [nvarchar](50) NULL, 14   [KpiWeight] [decimal](18, 2) NULL, 15   [KpiRule] [nvarchar](100) NULL, 16   [KpiCriterion] [nvarchar](100) NULL, 17   [KpiAreaRule] [nvarchar](100) NULL, 18   [KpiSampleRule] [nvarchar](100) NULL, 19   [KpiAreaNum] [int] NOT NULL, 20   [KpiSampleNum] [int] NOT NULL, 21   [KpiMinValue] [decimal](18, 2) NULL, 22   [KpiMaxValue] [decimal](18, 2) NULL, 23   [KpiOffset] [decimal](18, 2) NULL, 24   [KpiReferenceVal] [decimal](18, 2) NULL, 25   [KpiValueType] [nvarchar](50) NULL, 26   [KpiFormula] [nvarchar](50) NULL, 27   [KpiFormulaRule] [nvarchar](100) NULL, 28   [KpiMemo] [nvarchar](100) NULL, 29   [KpiGoodMinValue] [decimal](18, 2) NULL, 30   [KpiGoodMaxValue] [decimal](18, 2) NULL, 31   [KpiGoodMethod] [nvarchar](50) NULL, 32   [KpiGoodOffset] [decimal](18, 2) NULL, 33   [KpiSampleType] [nvarchar](50) NULL, 34   [IsDLT] [smallint] NULL, 35   [CrtDate] [datetime] NULL, 36   [CrtUser] [nvarchar](50) NULL, 37   [UpdDate] [datetime] NULL, 38   [UpdUser] [nvarchar](50) NULL, 39 CONSTRAINT [PK_EVKpiInfo] PRIMARY KEY CLUSTERED  40 ( 41   [KpiNo] ASC 42 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 43 ) ON [PRIMARY] 44  45 GO 46 /****** Object: Table [dbo].[kpi1447055501128]  Script Date: 2016/3/25 16:06:04 ******/ 47 SET ANSI_NULLS ON 48 GO 49 SET QUOTED_IDENTIFIER ON 50 GO 51 CREATE TABLE [dbo].[kpi1447055501128]( 52   [KpiNo] [nvarchar](50) NOT NULL, 53   [KpiName] [nvarchar](50) NULL, 54   [KpiInfo] [nvarchar](100) NULL, 55   [KpiParentNo] [nvarchar](50) NULL, 56   [KpiMethod] [nvarchar](50) NULL, 57   [KpiWeight] [decimal](18, 2) NULL, 58   [KpiRule] [nvarchar](100) NULL, 59   [KpiCriterion] [nvarchar](100) NULL, 60   [KpiAreaRule] [nvarchar](100) NULL, 61   [KpiSampleRule] [nvarchar](100) NULL, 62   [KpiAreaNum] [int] NOT NULL, 63   [KpiSampleNum] [int] NOT NULL, 64   [KpiMinValue] [decimal](18, 2) NULL, 65   [KpiMaxValue] [decimal](18, 2) NULL, 66   [KpiOffset] [decimal](18, 2) NULL, 67   [KpiReferenceVal] [decimal](18, 2) NULL, 68   [KpiValueType] [nvarchar](50) NULL, 69   [KpiFormula] [nvarchar](50) NULL, 70   [KpiFormulaRule] [nvarchar](100) NULL, 71   [KpiMemo] [nvarchar](100) NULL, 72   [KpiGoodMinValue] [decimal](18, 2) NULL, 73   [KpiGoodMaxValue] [decimal](18, 2) NULL, 74   [KpiGoodMethod] [nvarchar](50) NULL, 75   [KpiGoodOffset] [decimal](18, 2) NULL, 76   [KpiSampleType] [nvarchar](50) NULL 77 ) ON [PRIMARY] 78  79 GO 80 /****** Object: Table [dbo].[kpi20150002]  Script Date: 2016/3/25 16:06:04 ******/ 81 SET ANSI_NULLS ON 82 GO 83 SET QUOTED_IDENTIFIER ON 84 GO 85 CREATE TABLE [dbo].[kpi20150002]( 86   [KpiNo] [nvarchar](50) NOT NULL, 87   [KpiName] [nvarchar](50) NULL, 88   [KpiInfo] [nvarchar](100) NULL, 89   [KpiParentNo] [nvarchar](50) NULL, 90   [KpiMethod] [nvarchar](50) NULL, 91   [KpiWeight] [decimal](18, 2) NULL, 92   [KpiRule] [nvarchar](100) NULL, 93   [KpiCriterion] [nvarchar](100) NULL, 94   [KpiAreaRule] [nvarchar](100) NULL, 95   [KpiSampleRule] [nvarchar](100) NULL, 96   [KpiAreaNum] [int] NOT NULL, 97   [KpiSampleNum] [int] NOT NULL, 98   [KpiMinValue] [decimal](18, 2) NULL, 99   [KpiMaxValue] [decimal](18, 2) NULL,100   [KpiOffset] [decimal](18, 2) NULL,101   [KpiReferenceVal] [decimal](18, 2) NULL,102   [KpiValueType] [nvarchar](50) NULL,103   [KpiFormula] [nvarchar](50) NULL,104   [KpiFormulaRule] [nvarchar](100) NULL,105   [KpiMemo] [nvarchar](100) NULL,106   [KpiGoodMinValue] [decimal](18, 2) NULL,107   [KpiGoodMaxValue] [decimal](18, 2) NULL,108   [KpiGoodMethod] [nvarchar](50) NULL,109   [KpiGoodOffset] [decimal](18, 2) NULL,110   [KpiSampleType] [nvarchar](50) NULL111 ) ON [PRIMARY]112 113 GO114 /****** Object: Table [dbo].[KpiTree]  Script Date: 2016/3/25 16:06:04 ******/115 SET ANSI_NULLS ON116 GO117 SET QUOTED_IDENTIFIER ON118 GO119 CREATE TABLE [dbo].[KpiTree](120   [KpiNo] [nvarchar](50) NOT NULL,121   [KpiName] [nvarchar](50) NULL,122   [KpiInfo] [nvarchar](50) NULL,123   [KpiParentNo] [nvarchar](50) NULL,124   [KpiCollectMethod] [nvarchar](50) NULL,125   [KpiWeight] [decimal](18, 2) NULL,126   [KpiMemo] [nvarchar](100) NULL,127   [KpiIndex] [int] NULL,128   [IsDLT] [smallint] NULL,129   [CrtDate] [datetime] NULL,130   [CrtUser] [nvarchar](50) NULL,131   [UpdDate] [datetime] NULL,132   [UpdUser] [nvarchar](50) NULL,133 CONSTRAINT [PK_KpiTree] PRIMARY KEY CLUSTERED 134 (135   [KpiNo] ASC136 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]137 ) ON [PRIMARY]138 139 GO140 /****** Object: Table [dbo].[kpitree1447055501128]  Script Date: 2016/3/25 16:06:04 ******/141 SET ANSI_NULLS ON142 GO143 SET QUOTED_IDENTIFIER ON144 GO145 CREATE TABLE [dbo].[kpitree1447055501128](146   [KpiNo] [nvarchar](50) NOT NULL,147   [KpiName] [nvarchar](50) NULL,148   [KpiInfo] [nvarchar](50) NULL,149   [KpiParentNo] [nvarchar](50) NULL,150   [KpiCollectMethod] [nvarchar](50) NULL,151   [KpiWeight] [decimal](18, 2) NULL,152   [KpiMemo] [nvarchar](100) NULL,153   [KpiIndex] [int] NULL154 ) ON [PRIMARY]155 156 GO157 /****** Object: Table [dbo].[KpiTree20150002]  Script Date: 2016/3/25 16:06:04 ******/158 SET ANSI_NULLS ON159 GO160 SET QUOTED_IDENTIFIER ON161 GO162 CREATE TABLE [dbo].[KpiTree20150002](163   [KpiNo] [nvarchar](50) NOT NULL,164   [KpiName] [nvarchar](50) NULL,165   [KpiInfo] [nvarchar](50) NULL,166   [KpiParentNo] [nvarchar](50) NULL,167   [KpiCollectMethod] [nvarchar](50) NULL,168   [KpiWeight] [decimal](18, 2) NULL,169   [KpiMemo] [nvarchar](100) NULL,170   [KpiIndex] [int] NULL171 ) ON [PRIMARY]172 173 GO174 /****** Object: Table [dbo].[KpiValue]  Script Date: 2016/3/25 16:06:04 ******/175 SET ANSI_NULLS ON176 GO177 SET QUOTED_IDENTIFIER ON178 GO179 CREATE TABLE [dbo].[KpiValue](180   [TenderNo] [nvarchar](50) NOT NULL,181   [KpiNo] [nvarchar](50) NOT NULL,182   [KpiValue1] [decimal](18, 2) NULL,183   [KpiValue2] [decimal](18, 2) NULL,184   [SampleNumAll] [int] NULL,185   [SampleNum1] [int] NULL,186   [SampleNum2] [int] NULL187 ) ON [PRIMARY]188 189 GO190 /****** Object: Table [dbo].[KpiValue1447055501128]  Script Date: 2016/3/25 16:06:04 ******/191 SET ANSI_NULLS ON192 GO193 SET QUOTED_IDENTIFIER ON194 GO195 CREATE TABLE [dbo].[KpiValue1447055501128](196   [TenderNo] [nvarchar](50) NOT NULL,197   [KpiNo] [nvarchar](50) NOT NULL,198   [KpiValue1] [decimal](18, 2) NULL,199   [KpiValue2] [decimal](18, 2) NULL,200   [SampleNumAll] [int] NULL,201   [SampleNum1] [int] NULL,202   [SampleNum2] [int] NULL203 ) ON [PRIMARY]204 205 GO206 /****** Object: Table [dbo].[KpiValue20150002]  Script Date: 2016/3/25 16:06:04 ******/207 SET ANSI_NULLS ON208 GO209 SET QUOTED_IDENTIFIER ON210 GO211 CREATE TABLE [dbo].[KpiValue20150002](212   [TenderNo] [nvarchar](50) NOT NULL,213   [KpiNo] [nvarchar](50) NOT NULL,214   [KpiValue1] [decimal](18, 2) NULL,215   [KpiValue2] [decimal](18, 2) NULL,216   [SampleNumAll] [int] NULL,217   [SampleNum1] [int] NULL,218   [SampleNum2] [int] NULL219 ) ON [PRIMARY]220 221 GO222 ALTER TABLE [dbo].[Kpi] ADD CONSTRAINT [DF_Kpi_KpiWeight] DEFAULT ((1)) FOR [KpiWeight]223 GO224 ALTER TABLE [dbo].[Kpi] ADD CONSTRAINT [DF_Kpi_KpiAreaNum] DEFAULT ((2)) FOR [KpiAreaNum]225 GO226 ALTER TABLE [dbo].[Kpi] ADD CONSTRAINT [DF_Kpi_KpiSampleNum] DEFAULT ((10)) FOR [KpiSampleNum]227 GO228 ALTER TABLE [dbo].[Kpi] ADD CONSTRAINT [DF_Kpi_IsDLT] DEFAULT ((0)) FOR [IsDLT]229 GO230 ALTER TABLE [dbo].[kpi20150002] ADD CONSTRAINT [DF_kpi20150002_KpiWeight] DEFAULT ((1)) FOR [KpiWeight]231 GO232 ALTER TABLE [dbo].[KpiTree] ADD CONSTRAINT [DF_KpiTree_KpiWeight] DEFAULT ((1)) FOR [KpiWeight]233 GO234 ALTER TABLE [dbo].[KpiTree] ADD CONSTRAINT [DF_KpiTree_IsDLT] DEFAULT ((0)) FOR [IsDLT]235 GO236 ALTER TABLE [dbo].[KpiTree20150002] ADD CONSTRAINT [DF_KpiTree20150002_KpiWeight] DEFAULT ((1)) FOR [KpiWeight]237 GO238 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'指标编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Kpi', @level2type=N'COLUMN',@level2name=N'KpiNo'239 GO240 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'指标名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Kpi', @level2type=N'COLUMN',@level2name=N'KpiName'241 GO242 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上级指标编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Kpi', @level2type=N'COLUMN',@level2name=N'KpiParentNo'243 GO244 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权重' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Kpi', @level2type=N'COLUMN',@level2name=N'KpiWeight'245 GO246 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上级指标编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Kpi', @level2type=N'COLUMN',@level2name=N'KpiRule'247 GO248 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权重' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Kpi', @level2type=N'COLUMN',@level2name=N'KpiCriterion'249 GO250 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'指标配置表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Kpi'251 GO

View Code