UsedDay MTID maxDate TMoney
1 1 2011-07-04 10:35:29.330 12
3 1 2011-07-05 09:46:38.277 108
5 1 2011-07-05 09:46:04.793 60
30 1 2011-07-05 09:46:04.793 204
90 1 2011-07-05 09:46:13.590 612
365 1 2011-07-04 18:53:16.650 568
730 1 2011-07-04 18:52:57.903 1136
1095 1 2011-07-04 18:52:05.623 1704
1460 1 2011-06-30 09:28:16.220 0
3 2 2011-07-05 09:47:57.123 36
5 2 2011-07-05 09:48:07.470 60
30 2 2011-07-05 09:47:50.850 68
1095 2 2011-07-01 17:05:25.810 1704
数据库返回DataTable 要求显示UsedDay 1-29天 表示日统计 范围30-365 30的倍数显示月统计 365的倍数显示年统计
按照类型MTID 显示年月日的总个数 以及总钱数 统计结果为
UsedDay MTID maxDate TMoney Count
1 1 2011-07-05 09:46:04.793 180 9
30 1 2011-07-05 09:46:04.793 816 2
365 1 2011-07-05 09:46:04.793 3408 4
1 2 2011-07-05 09:46:04.793 96 2
30 2 2011-07-05 09:46:04.793 68 1
365 2 2011-07-05 09:46:04.793 1704 1
1 1 2011-07-04 10:35:29.330 12
3 1 2011-07-05 09:46:38.277 108
5 1 2011-07-05 09:46:04.793 60
30 1 2011-07-05 09:46:04.793 204
90 1 2011-07-05 09:46:13.590 612
365 1 2011-07-04 18:53:16.650 568
730 1 2011-07-04 18:52:57.903 1136
1095 1 2011-07-04 18:52:05.623 1704
1460 1 2011-06-30 09:28:16.220 0
3 2 2011-07-05 09:47:57.123 36
5 2 2011-07-05 09:48:07.470 60
30 2 2011-07-05 09:47:50.850 68
1095 2 2011-07-01 17:05:25.810 1704
数据库返回DataTable 要求显示UsedDay 1-29天 表示日统计 范围30-365 30的倍数显示月统计 365的倍数显示年统计
按照类型MTID 显示年月日的总个数 以及总钱数 统计结果为
UsedDay MTID maxDate TMoney Count
1 1 2011-07-05 09:46:04.793 180 9
30 1 2011-07-05 09:46:04.793 816 2
365 1 2011-07-05 09:46:04.793 3408 4
1 2 2011-07-05 09:46:04.793 96 2
30 2 2011-07-05 09:46:04.793 68 1
365 2 2011-07-05 09:46:04.793 1704 1
解决方案 »
- C# 画图
- COOKIE写不进去
- 关于AxMSChart20Lib.AxMSChart曲线图中x轴y轴步长问题
- 求各位大哥给点指点。。。
- 怎样将SQL语句转化成二进制数据进行传输
- 我的qq号码丢了,被要100元
- 请问在new了一个类class1后,如何在使用完时主动释放class1所占的资源!
- 求教:C#调用C++写的DLL 关于 unsigned char*的声明问题
- TcpClient如何指定某个IP时行链接
- windows 服务发生unhandled win32 exception
- 要对内存中的Datatable做多次更新,怎么只对datatable进行更新。。。
- 求一段js删除cookie代码
UsedDay MTID maxDate TMoney Count
1 1 2011-07-05 09:46:04.793 180 9
30 1 2011-07-05 09:46:04.793 816 2
365 1 2011-07-05 09:46:04.793 3408 4
1 2 2011-07-05 09:46:04.793 96 2
30 2 2011-07-05 09:46:04.793 68 1
365 2 2011-07-05 09:46:04.793 1704 1第一行的9 应该是 3 吧?
void Main()
{
DataTable dt=new DataTable();
dt.Columns.Add("UsedDay",typeof(int));
dt.Columns.Add("MTID",typeof(int));
dt.Columns.Add("maxDate");
dt.Columns.Add("TMoney",typeof(int));
dt.Rows.Add(1,1,"2011-07-04 10:35:29.330",12);
dt.Rows.Add(3,1,"2011-07-05 09:46:38.277",108);
dt.Rows.Add(5,1,"2011-07-05 09:46:04.793",60);
dt.Rows.Add(30,1,"2011-07-05 09:46:04.793",204);
dt.Rows.Add(90,1,"2011-07-05 09:46:13.590",612);
dt.Rows.Add(365,1,"2011-07-04 18:53:16.650",568);
dt.Rows.Add(730,1,"2011-07-04 18:52:57.903",1136);
dt.Rows.Add(1095,1,"2011-07-04 18:52:05.623",1704);
dt.Rows.Add(1460,1,"2011-06-30 09:28:16.220",0);
dt.Rows.Add(3,2,"2011-07-05 09:47:57.123",36);
dt.Rows.Add(5,2,"22011-07-05 09:48:07.470",60);
dt.Rows.Add(30,2,"2011-07-05 09:47:50.850",68);
dt.Rows.Add(1095,2,"2011-07-01 17:05:25.810",1704);
var query=from r in dt.AsEnumerable()
let temp=r.Field<int>("UsedDay")<30?1:r.Field<int>("UsedDay")%30==0?30:r.Field<int>("UsedDay")%365==0?365:1
group r by new {UsedDay=temp,MTID=r.Field<int>("MTID")} into g
select new
{
UsedDay=g.Key.UsedDay,
MTID=g.Key.MTID,
maxDate=g.FirstOrDefault().Field<string>("maxDate"),
TMoney=g.Sum(t=>t.Field<int>("TMoney")),
Count=g.Count()
};
Console.WriteLine("UsedDay\tMTID\t\tmaxDate\t\tTMoney\tCount");
query.ToList().ForEach(q=>Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}",q.UsedDay,q.MTID,q.maxDate,q.TMoney,q.Count));
/*
UsedDay MTID maxDate TMoney Count
1 1 2011-07-04 10:35:29.330 180 3
30 1 2011-07-05 09:46:04.793 816 2
365 1 2011-07-04 18:53:16.650 3408 4
1 2 2011-07-05 09:47:57.123 96 2
30 2 2011-07-05 09:47:50.850 68 1
365 2 2011-07-01 17:05:25.810 1704 1
*/
}
倒是懒得写前边这些一大段的基础数据:DataTable dt=new DataTable();
dt.Columns.Add("UsedDay",typeof(int));
dt.Columns.Add("MTID",typeof(int));
dt.Columns.Add("maxDate");
dt.Columns.Add("TMoney",typeof(int));
dt.Rows.Add(1,1,"2011-07-04 10:35:29.330",12);
dt.Rows.Add(3,1,"2011-07-05 09:46:38.277",108);
dt.Rows.Add(5,1,"2011-07-05 09:46:04.793",60);
dt.Rows.Add(30,1,"2011-07-05 09:46:04.793",204);
dt.Rows.Add(90,1,"2011-07-05 09:46:13.590",612);
dt.Rows.Add(365,1,"2011-07-04 18:53:16.650",568);
dt.Rows.Add(730,1,"2011-07-04 18:52:57.903",1136);
dt.Rows.Add(1095,1,"2011-07-04 18:52:05.623",1704);
dt.Rows.Add(1460,1,"2011-06-30 09:28:16.220",0);
dt.Rows.Add(3,2,"2011-07-05 09:47:57.123",36);
dt.Rows.Add(5,2,"22011-07-05 09:48:07.470",60);
dt.Rows.Add(30,2,"2011-07-05 09:47:50.850",68);
dt.Rows.Add(1095,2,"2011-07-01 17:05:25.810",1704);希望楼主下次有类似问题时,能在提问时,随手奉上以上的基础数据
DataTable dt = new DataTable();
dt.Columns.Add("UsedDay", typeof(int));
dt.Columns.Add("MTID", typeof(int));
dt.Columns.Add("TypeID", typeof(int)); //新增字段
dt.Columns.Add("maxDate");
dt.Columns.Add("TMoney", typeof(int));
dt.Rows.Add(1, 1, "2011-07-04 10:35:29.330", 12);
dt.Rows.Add(1, 1, 2, " 2011-06-30 09:26:15.530", 0);
dt.Rows.Add(1, 3, 1, " 2011-07-05 09:46:01.287", 36);
dt.Rows.Add(1, 3, 2, "2011-07-05 09:46:38.277", 72);
dt.Rows.Add(1, 5, 1, "2011-07-05 09:46:04.793", 60);
dt.Rows.Add(1, 30, 1, "2011-07-04 14:45:35.363", 136);
dt.Rows.Add(1, 30, 2, "2011-07-04 14:32:06.007", 68);
dt.Rows.Add(1, 90, 1, "2011-06-30 12:43:23.940", 0);
dt.Rows.Add(1, 90, 2, "2011-07-05 09:46:13.590", 612);
dt.Rows.Add(1, 365, 1, "2011-07-04 18:53:16.650", 568);
dt.Rows.Add(1, 730, 2, "2011-07-04 18:52:57.903", 1136);
dt.Rows.Add(1, 1095, 2, "2011-07-04 18:52:05.623", 1704);
dt.Rows.Add(1, 1460, 1, "2011-06-30 09:28:16.220", 0);
dt.Rows.Add(2, 3, 1, "2011-07-05 09:47:57.123", 36);
dt.Rows.Add(2, 5, 2, "2011-07-05 09:48:07.470", 60);
dt.Rows.Add(2, 30, 1, "2011-07-01 17:10:15.817", 0);
dt.Rows.Add(2, 30, 2, "2011-07-05 09:47:50.850", 68);
dt.Rows.Add(2, 1095, "2, 2011-07-01 17:05:25.810", 0);
统计如下
MTID USedDay TypeId maxDate TMoney count
1 1 1 2011-07-04 10:35:29.330 108 9
1 1 2 2011-07-04 10:35:29.330 72 4
1 30 1 2011-07-04 10:35:29.330 136 3
1 30 2 2011-07-04 10:35:29.330 680 3
1 365 1 2011-07-04 10:35:29.330 568 5
1 365 2 2011-07-04 10:35:29.330 2840 62 1 1 2011-07-04 10:35:29.330 36 3
2 1 2 2011-07-04 10:35:29.330 60 5
2 30 1 2011-07-04 10:35:29.330 136 1
2 30 2 2011-07-04 10:35:29.330 380 1
2 365 1 2011-07-04 10:35:29.330 0 3
2 365 2 2011-07-04 10:35:29.330 0 0
在之前的统计下在按照TypeId分组 count字段 是UsedDay / 日(1)月(30)年(365)
手工输入的希望没错
let temp=r.Field<int>("UsedDay")<30?1:r.Field<int>("UsedDay")%30==0?30:r.Field<int>("UsedDay")%365==0?365:1
group r by new {UsedDay=temp,MTID=r.Field<int>("MTID"),TypeID=r.Field<int>("TypeID")} into g
select new
{
UsedDay=g.Key.UsedDay,
MTID=g.Key.MTID,
TypeID=g.Key.TypeID,
maxDate=g.FirstOrDefault().Field<string>("maxDate"),
TMoney=g.Sum(t=>t.Field<int>("TMoney")),
Count=g.Count()
};