议：如何将树形菜单形式的数据转化成HTML的二维表（相同内容需合并单元格）_html/css_WEB-ITnose

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

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

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

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

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

的行数；

3.将查到的末端子节点编号的所有父节点编号也查出来,拼接起来,就知道了

的每行的列数；

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 }

以及需要用到的表（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