从一张表当中取出数据包括timest,nowdata。要求创建一张新表,添加新的字段endtime,nowdata互相比较,当nowdata相同时,只需要用endtime表示相同记录最后一条记录的时间,这样数据就被压缩了,请问有什么方法实现,最好是给出代码!!谢谢要求
开始时间 数据
2011-8-01 20
2011-8-02 30
2011-8-03 30
2011-8-04 40
2011-8-05 50
2011-8-06 50
2011-8-07 50
转化之后变成
开始时间 数据 结束时间
2011-8-01 20 2011-8-02
2011-8-02 30 2011-8-03
2011-8-04 40 2011-8-05
2011-8-05 50 2011-8-07
开始时间 数据
2011-8-01 20
2011-8-02 30
2011-8-03 30
2011-8-04 40
2011-8-05 50
2011-8-06 50
2011-8-07 50
转化之后变成
开始时间 数据 结束时间
2011-8-01 20 2011-8-02
2011-8-02 30 2011-8-03
2011-8-04 40 2011-8-05
2011-8-05 50 2011-8-07
adapter = new SqlDataAdapter(str, conn);
adapter.Fill(dt);
dt.Columns.Add("EndTime", System.Type.GetType("System.DateTime"));
for (int j = 0; j < dt.Rows.Count; j++)
{
if (j == 0)
{
EndTime = dt.Rows[j+1]["TimeST"].ToString();
str = "insert into " + "[" + TName5 + "]" + "values('" + dt.Rows[j]["TimeST"] + "','" + (int)dt.Rows[j]["CollectNodeId"] + "','" + (int)dt.Rows[j]["CollectId"] + "','" + (int)dt.Rows[j]["SecondTableadr"] + "','" + (int)dt.Rows[j]["ChildSecondtable"] + "','" + (int)dt.Rows[j]["FirstWarn"] + "','" + (int)dt.Rows[j]["SecondWarn"] + "','" + (int)dt.Rows[j]["NowData"] + "','"+EndTime+"')"; //insert into (strat_time,......) values(dt.Rows[i][strat_time])
}
else
{
if (dt.Rows[j][Nowdata].ToString() != dt.Rows[j - 1][Nowdata].ToString())
{
TimeST = dt.Rows[j - 1]["TimeST"].ToString();
EndTime = dt.Rows[j]["TimeST"].ToString();
str = "insert into " + "[" + TName5 + "]" + "values('" + TimeST+ "','" + (int)dt.Rows[j]["CollectNodeId"] + "','" + (int)dt.Rows[j]["CollectId"] + "','" + (int)dt.Rows[j]["SecondTableadr"] + "','" + (int)dt.Rows[j]["ChildSecondtable"] + "','" + (int)dt.Rows[j]["FirstWarn"] + "','" + (int)dt.Rows[j]["SecondWarn"] + "','" + (int)dt.Rows[j]["NowData"] + "','" + dt.Rows[j - 1]["EndTime"] + "')";
MessageBox.Show(str);
}
else
{
EndTime = dt.Rows[j]["TimeST"].ToString();
}
}
现在就是else当中的代码不会弄,帮忙下
{
dt.Rows[i]["endtime"] = dt.Rows[i]["timest"]; if (dt.Rows[i - 1]["nowdata"].ToString() == dt.Rows[i]["nowdata"].ToString())
{
dt.Rows[i]["timest"] = dt.Rows[i - 1]["timest"];
dt.Rows[i - 1].Delete(); i--;
}
}
然后遍历dt中的数据插入新表就可以了
另外有没有这种情况,你还没回答:
开始时间 数据
2011-8-01 20
2011-8-02 50
2011-8-03 30
2011-8-04 40
2011-8-05 20
2011-8-06 50
2011-8-07 50给了一个交换数据的特例,我想知道实际数据会不会这么乱七八糟的,不然最优算法无法确定。
declare @d1 int
declare @d2 int
select @d1=min(num1),@d2=max(num1) from table1
while(@d1<=@d2)
begin
insert into table2 (time1,num1,time2) select min(time1),@d1,max(time1) from table1 where num1=@d1
select @d1=min(num1) from table1 where num1>@d1
end上面是SQL的写法
2011-12-2 12:50:00 1
2011-12-2 12:50:00 2
2011-12-3 12:50:00 3
2011-12-2 12:50:00 4
2011-12-4 12:51:00 6
2011-12-2 12:51:00 8
2011-12-5 12:51:00 8
2011-12-2 12:51:00 8
运行结果表
2011-12-2 12:50:00 1 2011-12-2 12:50:00
2011-12-2 12:50:00 2 2011-12-2 12:50:00
2011-12-3 12:50:00 3 2011-12-3 12:50:00
2011-12-2 12:50:00 4 2011-12-2 12:50:00
2011-12-4 12:51:00 6 2011-12-4 12:51:00
2011-12-2 12:51:00 8 2011-12-5 12:51:00declare @d1 int
declare @d2 int
select @d1=min(num1),@d2=max(num1) from table1
while(@d1<=@d2)
begin
insert into table2 (time1,num1,time2) select min(time1),@d1,max(time1) from table1 where num1=@d1
select @d1=min(num1) from table1 where num1>@d1
end
declare @d1 int
declare @d2 int
select @d1=min(num1),@d2=max(num1) from table1
while(@d1<=@d2)
begin
insert into table2 (time1,num1,time2) select min(time1),@d1,max(time1) from table1 where num1=@d1
select @d1=min(num1) from table1 where num1>@d1
end
还不是你要的结果?table1两个字段,table2三个字段,是多个字段啊
insert into table2 (time1,num1,time2) select min(time1),@d1,max(time1) from table1 where num1=@d1
and min(time1)<>max(time1)
delete table2
declare @d1 int
declare @d2 int
select @d1=min(num1),@d2=max(num1) from table1
while(@d1<=@d2)
begin
insert into table2 (time1,num1,time2) select n1,@d1,n2 from (select min(time1) as n1,max(time1) as n2 from table1 where num1=@d1)t1 where n1<>n2
select @d1=min(num1) from table1 where num1>@d1
end上面错了,这个才对
可惜xlh776216378不肯说明实际运行情况,所以我也不说最优的方法了。