现在有一个根据条件查询得到的数据集,在遍历该数据集在生成一个新的数据集,在新数据集中要动态的插入新行.这个问题本来昨天我在SQL区发了贴.请大家看看这里能更好的理解我的需求.谢谢.
http://community.csdn.net/Expert/topic/5474/5474861.xml?temp=.3563349paoluo(一天到晚游泳的鱼)
chuifengde(树上的鸟儿) 俩位朋友给了我一个SQL.不过个人技术菜没有理解好.目的也没有完全达到.所以我就又把之前的思路改了下.
现在我直接得到按月份查询的数据集,就是想根据RIV_NAME来分组了.RIV_NAME Username RIV_NUM RIV2_EXAMINE RIV2_DATE
A 张山 71 杨行镇 2007-3-12 11:10:37
A 张山 83 杨行镇 2007-3-12 13:57:28
A 张山 77 杨行镇 2007-3-12 13:58:08
B 李四 91 杨行镇 2007-3-23 13:52:14
B 李四 80 月浦镇 2007-3-23 14:41:35
B 张山 73 杨行镇 2007-3-23 15:05:05
C 王五 90 杨行镇 2007-3-27 13:13:11
C 王五 94 杨行镇 2007-3-27 13:19:12
C 王五 92 杨行镇 2007-3-27 13:19:36
C 王五 95 杨行镇 2007-3-27 13:23:16 我想显示成
RIV_NAME NUM1 NUM2 NUM3 NUM4 AVG RIV2_EXAMINE RIV2_DATE
A 71 83 77 空 77 杨行镇 3月
B 91 80 73 空 81 杨行镇 3月
C 90 94 92 95 92 杨行镇 3月现在我就是无法生成上面的结果.请大家能给出具体的解决步骤.本人菜鸟.请大家帮忙了。 谢谢!
http://community.csdn.net/Expert/topic/5474/5474861.xml?temp=.3563349paoluo(一天到晚游泳的鱼)
chuifengde(树上的鸟儿) 俩位朋友给了我一个SQL.不过个人技术菜没有理解好.目的也没有完全达到.所以我就又把之前的思路改了下.
现在我直接得到按月份查询的数据集,就是想根据RIV_NAME来分组了.RIV_NAME Username RIV_NUM RIV2_EXAMINE RIV2_DATE
A 张山 71 杨行镇 2007-3-12 11:10:37
A 张山 83 杨行镇 2007-3-12 13:57:28
A 张山 77 杨行镇 2007-3-12 13:58:08
B 李四 91 杨行镇 2007-3-23 13:52:14
B 李四 80 月浦镇 2007-3-23 14:41:35
B 张山 73 杨行镇 2007-3-23 15:05:05
C 王五 90 杨行镇 2007-3-27 13:13:11
C 王五 94 杨行镇 2007-3-27 13:19:12
C 王五 92 杨行镇 2007-3-27 13:19:36
C 王五 95 杨行镇 2007-3-27 13:23:16 我想显示成
RIV_NAME NUM1 NUM2 NUM3 NUM4 AVG RIV2_EXAMINE RIV2_DATE
A 71 83 77 空 77 杨行镇 3月
B 91 80 73 空 81 杨行镇 3月
C 90 94 92 95 92 杨行镇 3月现在我就是无法生成上面的结果.请大家能给出具体的解决步骤.本人菜鸟.请大家帮忙了。 谢谢!
2.使用游标查询数据库 order by RIV_NAME
3.记录上次RIV_NAME 判断RIV_NAME,插入或更新数据表#table
4,select * from #table
DataSet ddd =bas.TGetDataSet("select RIV_NAME ,Username ,RIV_NUM ,RIV2_EXAMINE,RIV2_DATE from Manager_Analyze where DATEPART(year,Manager_Analyze.RIV2_DATE)='2007'and (Manager_Analyze.RIV2_EXAMINE='杨行镇'or Manager_Analyze.RIV2_EXAMINE='月浦镇' or Manager_Analyze.RIV2_EXAMINE='罗泾镇'or Manager_Analyze.RIV2_EXAMINE='罗店镇' or Manager_Analyze.RIV2_EXAMINE='顾村镇' or Manager_Analyze.RIV2_EXAMINE='大场镇' or Manager_Analyze.RIV2_EXAMINE='庙行镇' or Manager_Analyze.RIV2_EXAMINE='凇南镇'or Manager_Analyze.RIV2_EXAMINE='高境镇')and DATEPART(month,Manager_Analyze.RIV2_DATE)='3' order by RIV_NAME");
DataSet dd=new DataSet();
DataTable dt = new DataTable();
dt = ddd.Tables[0].Copy();
int index = 0, icount = 1;
string riv_name = string.Empty;
int sum = 0;
foreach (DataRow dr in ddd.Tables[0].Rows)
{
if (riv_name == dr["RIV_NAME"].ToString())
{
icount++;
sum += int.Parse(dr["RIV_NUM"].ToString());
}
else
{
DataColumn dr2=new DataColumn();//
dr2.Caption = "平均值";
dr2.DefaultValue = sum / icount;
dt.Columns.Add(dr2);//
icount = 1;
riv_name = dr["RIV_NAME"].ToString();
}
index++;
}
GridView2.DataSource = dt;
GridView2.DataBind();
这是我现在写的遍历方法.有问题.出来的结果惨不忍睹啊....
{
if (riv_name == dr["RIV_NAME"].ToString())
{
icount++;
sum += int.Parse(dr["RIV_NUM"].ToString());
}
else
{
DataColumn dr2 = new DataColumn();// if (icount == 1)
{
dr2.DefaultValue = dr["RIV_NUM"].ToString();
}
else
{
dr2.DefaultValue = sum / icount;
icount = 1;
riv_name = dr["RIV_NAME"].ToString();
}
dt.Columns.Add(dr2);//
}
index++;
}改成这样了................
(RIV_NAME Varchar(10),
Username Nvarchar(10),
RIV_NUM Int,
RIV2_EXAMINE Nvarchar(10),
RIV2_DATE DateTime)
Insert 表 Select 'A', N'张山', 71, N'杨行镇', '2007-3-12 11:10:37'
Union All Select 'A', N'张山', 83, N'杨行镇', '2007-3-12 13:57:28'
Union All Select 'A', N'张山', 77, N'杨行镇', '2007-3-12 13:58:08'
Union All Select 'B', N'李四', 91, N'杨行镇', '2007-3-23 13:52:14'
Union All Select 'B', N'李四', 80, N'月浦镇', '2007-3-23 14:41:35'
Union All Select 'B', N'张山', 73, N'杨行镇', '2007-3-23 15:05:05'
Union All Select 'C', N'王五', 90, N'杨行镇', '2007-3-27 13:13:11'
Union All Select 'C', N'王五', 94, N'杨行镇', '2007-3-27 13:19:12'
Union All Select 'C', N'王五', 92, N'杨行镇', '2007-3-27 13:19:36'
Union All Select 'C', N'王五', 95, N'杨行镇', '2007-3-27 13:23:16'
GO
Declare @S Varchar(8000)
Select @S = 'Select RIV_NAME'
Select @S = @S + ', Max(Case ID When ' + Rtrim(ID) + ' Then Rtrim(RIV_NUM) Else '''' End) As [RIV_NUM' + Rtrim(ID) + ']' From
(Select (Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = 3) As ID, * From 表 A Where Month(RIV2_DATE) = 3) A Group By ID
Select @S = @S+ ' , AVG(RIV_NUM) As [AVG], Max(RIV2_EXAMINE) As RIV2_EXAMINE, DateName(mm, RIV2_DATE) As RIV2_DATE From (Select (Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = 3) As ID, * From 表 A Where Month(RIV2_DATE) = 3) A Where Month(RIV2_DATE) = 3 Group By RIV_NAME, DateName(mm, RIV2_DATE)' --加上AVG
EXEC(@S)
GO
Drop Table 表
--Result
/*
RIV_NAME RIV_NUM1 RIV_NUM2 RIV_NUM3 RIV_NUM4 AVG RIV2_EXAMINE RIV2_DATE
A 71 83 77 77 杨行镇 三月
B 91 80 73 81 杨行镇 三月
C 90 94 92 95 92 杨行镇 三月
*/
Select @S = 'Select RIV_NAME'
Select @S = @S + ', Max(Case ID When ' + Rtrim(ID) + ' Then Rtrim(RIV_NUM) Else '''' End) As [RIV_NUM' + Rtrim(ID) + ']' From
(Select (Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE) As ID, * From 表 A) B Group By ID
Select @S = @S+ ' , AVG(RIV_NUM) As [AVG], Max(RIV2_EXAMINE) As RIV2_EXAMINE, DateName(mm, RIV2_DATE) As RIV2_DATE From (Select (Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE) As ID, * From 表 A ) B Group By RIV_NAME, DateName(mm, RIV2_DATE)' --加上AVG
EXEC(@S)
A 83 77 杨行镇 03
B 91 81 月浦镇 03
C 95 92 杨行镇 03
A 71 83 77 空 77 杨行镇 3月
B 91 80 73 空 81 杨行镇 3月
C 90 94 92 95 92 杨行镇 3月
但是 NUM1 NUM2 NUM3 NUM4 没有啊我求的是这些啊.......
RIV_NAME NUM1 NUM2 NUM3 NUM4 AVG RIV2_EXAMINE RIV2_DATE
A 71 83 77 空 77 杨行镇 3月
B 91 80 73 空 81 杨行镇 3月
C 90 94 92 95 92 杨行镇 3月
但是 NUM1 NUM2 NUM3 NUM4 没有啊我求的是这些啊.......
-----------
什麼意思?A那行NUM1 是71,NUM2是83,NUM3是77,NUM4是空,AVG是77,我上面那段代碼得到的結果就是這樣的啊。怎麼就沒有?你有沒有測試這段代碼?將以下代碼直接復制到查詢分析器中執行,就可以得到你要的結果。Create Table 表
(RIV_NAME Varchar(10),
Username Nvarchar(10),
RIV_NUM Int,
RIV2_EXAMINE Nvarchar(10),
RIV2_DATE DateTime)
Insert 表 Select 'A', N'张山', 71, N'杨行镇', '2007-3-12 11:10:37'
Union All Select 'A', N'张山', 83, N'杨行镇', '2007-3-12 13:57:28'
Union All Select 'A', N'张山', 77, N'杨行镇', '2007-3-12 13:58:08'
Union All Select 'B', N'李四', 91, N'杨行镇', '2007-3-23 13:52:14'
Union All Select 'B', N'李四', 80, N'月浦镇', '2007-3-23 14:41:35'
Union All Select 'B', N'张山', 73, N'杨行镇', '2007-3-23 15:05:05'
Union All Select 'C', N'王五', 90, N'杨行镇', '2007-3-27 13:13:11'
Union All Select 'C', N'王五', 94, N'杨行镇', '2007-3-27 13:19:12'
Union All Select 'C', N'王五', 92, N'杨行镇', '2007-3-27 13:19:36'
Union All Select 'C', N'王五', 95, N'杨行镇', '2007-3-27 13:23:16'
GO
Declare @S Varchar(8000)
Select @S = 'Select RIV_NAME'
Select @S = @S + ', Max(Case ID When ' + Rtrim(ID) + ' Then Rtrim(RIV_NUM) Else '''' End) As [RIV_NUM' + Rtrim(ID) + ']' From
(Select (Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = 3) As ID, * From 表 A Where Month(RIV2_DATE) = 3) A Group By ID
Select @S = @S+ ' , AVG(RIV_NUM) As [AVG], Max(RIV2_EXAMINE) As RIV2_EXAMINE, DateName(mm, RIV2_DATE) As RIV2_DATE From (Select (Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = 3) As ID, * From 表 A Where Month(RIV2_DATE) = 3) A Where Month(RIV2_DATE) = 3 Group By RIV_NAME, DateName(mm, RIV2_DATE)' --加上AVG
EXEC(@S)
GO
Drop Table 表
--Result
/*
RIV_NAME RIV_NUM1 RIV_NUM2 RIV_NUM3 RIV_NUM4 AVG RIV2_EXAMINE RIV2_DATE
A 71 83 77 77 杨行镇 三月
B 91 80 73 81 杨行镇 三月
C 90 94 92 95 92 杨行镇 三月
*/
我得到的是..A 83 77 杨行镇 03
B 91 81 月浦镇 03
C 95 92 杨行镇 03