时间戳

首页 > 技术文章 > 议：如何将树形菜单形式的数据转化成HTML的二维表（相同内容需合并单元格）

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

wyfblog 2016-03-25 16:07 原文

　　一般做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         else
129         {
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             else
146             {
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             else
182             {
183                 return false;
184             }
185         }
186         else
187         {
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                 else
206                 {
207                     break;
208                 }
209             }
210             else
211             {
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) NULL
111 ) ON [PRIMARY]
112 
113 GO
114 /****** Object:  Table [dbo].[KpiTree]    Script Date: 2016/3/25 16:06:04 ******/
115 SET ANSI_NULLS ON
116 GO
117 SET QUOTED_IDENTIFIER ON
118 GO
119 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] ASC
136 )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 GO
140 /****** Object:  Table [dbo].[kpitree1447055501128]    Script Date: 2016/3/25 16:06:04 ******/
141 SET ANSI_NULLS ON
142 GO
143 SET QUOTED_IDENTIFIER ON
144 GO
145 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] NULL
154 ) ON [PRIMARY]
155 
156 GO
157 /****** Object:  Table [dbo].[KpiTree20150002]    Script Date: 2016/3/25 16:06:04 ******/
158 SET ANSI_NULLS ON
159 GO
160 SET QUOTED_IDENTIFIER ON
161 GO
162 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] NULL
171 ) ON [PRIMARY]
172 
173 GO
174 /****** Object:  Table [dbo].[KpiValue]    Script Date: 2016/3/25 16:06:04 ******/
175 SET ANSI_NULLS ON
176 GO
177 SET QUOTED_IDENTIFIER ON
178 GO
179 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] NULL
187 ) ON [PRIMARY]
188 
189 GO
190 /****** Object:  Table [dbo].[KpiValue1447055501128]    Script Date: 2016/3/25 16:06:04 ******/
191 SET ANSI_NULLS ON
192 GO
193 SET QUOTED_IDENTIFIER ON
194 GO
195 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] NULL
203 ) ON [PRIMARY]
204 
205 GO
206 /****** Object:  Table [dbo].[KpiValue20150002]    Script Date: 2016/3/25 16:06:04 ******/
207 SET ANSI_NULLS ON
208 GO
209 SET QUOTED_IDENTIFIER ON
210 GO
211 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] NULL
219 ) ON [PRIMARY]
220 
221 GO
222 ALTER TABLE [dbo].[Kpi] ADD  CONSTRAINT [DF_Kpi_KpiWeight]  DEFAULT ((1)) FOR [KpiWeight]
223 GO
224 ALTER TABLE [dbo].[Kpi] ADD  CONSTRAINT [DF_Kpi_KpiAreaNum]  DEFAULT ((2)) FOR [KpiAreaNum]
225 GO
226 ALTER TABLE [dbo].[Kpi] ADD  CONSTRAINT [DF_Kpi_KpiSampleNum]  DEFAULT ((10)) FOR [KpiSampleNum]
227 GO
228 ALTER TABLE [dbo].[Kpi] ADD  CONSTRAINT [DF_Kpi_IsDLT]  DEFAULT ((0)) FOR [IsDLT]
229 GO
230 ALTER TABLE [dbo].[kpi20150002] ADD  CONSTRAINT [DF_kpi20150002_KpiWeight]  DEFAULT ((1)) FOR [KpiWeight]
231 GO
232 ALTER TABLE [dbo].[KpiTree] ADD  CONSTRAINT [DF_KpiTree_KpiWeight]  DEFAULT ((1)) FOR [KpiWeight]
233 GO
234 ALTER TABLE [dbo].[KpiTree] ADD  CONSTRAINT [DF_KpiTree_IsDLT]  DEFAULT ((0)) FOR [IsDLT]
235 GO
236 ALTER TABLE [dbo].[KpiTree20150002] ADD  CONSTRAINT [DF_KpiTree20150002_KpiWeight]  DEFAULT ((1)) FOR [KpiWeight]
237 GO
238 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 GO
240 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 GO
242 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 GO
244 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 GO
246 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 GO
248 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 GO
250 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

 

推荐阅读