我用access建了个表report,数据项有ID(自动编号),number(数字),time(时间)。现在我想要的是:按提取时间里的月份聚集,计算每月number的总数、显示月份、同月份的数据的条数。
我的代码是:
private DataSet ExecuteAccessNonQuery()
{
string ACCESS_CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\data.mdb;"; OleDbConnection conn = new OleDbConnection(ACCESS_CONN_STRING);
//按提取时间里的月份,然后按月份聚集,计算每月number的总数、显示月份、每个月的条数
OleDbCommand cmd = new OleDbCommand("select sum(number) as 'sum' ,datepart(mm,time) as 'month' count(*) as 'count' from report group by datepart(mm,time) ", conn); cmd.CommandType = CommandType.Text; try
{
OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds);
return ds; }
catch
{
conn.Close();
throw;
}
}
运行后出错:SELECT 子句中包含一个保留字、拼写错误或丢失的参数,或标点符号不正确。
我的代码是:
private DataSet ExecuteAccessNonQuery()
{
string ACCESS_CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\data.mdb;"; OleDbConnection conn = new OleDbConnection(ACCESS_CONN_STRING);
//按提取时间里的月份,然后按月份聚集,计算每月number的总数、显示月份、每个月的条数
OleDbCommand cmd = new OleDbCommand("select sum(number) as 'sum' ,datepart(mm,time) as 'month' count(*) as 'count' from report group by datepart(mm,time) ", conn); cmd.CommandType = CommandType.Text; try
{
OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds);
return ds; }
catch
{
conn.Close();
throw;
}
}
运行后出错:SELECT 子句中包含一个保留字、拼写错误或丢失的参数,或标点符号不正确。
"select sum(number) as sum ,datepart(mm,[time]) as month,count(*) as 记录条数 from report group by datepart(mm,[time])"
运行后:SELECT 子句中包含一个保留字、拼写错误或丢失的参数,或标点符号不正确。
"select sum(number) as 总数 ,datepart(mm,[time]) as 月份,count(*) as 记录条数 from report group by datepart(mm,[time])"
试试这个吧
运行后出现:至少一个参数没有被指定值。
drop table report
create table report(time datetime,number int)
insert into report(time,number)values(getdate(),12)
insert into report(time,number)values(getdate(),123)
insert into report(time,number)values(datediff(dd,30,getdate()),11)
insert into report(time,number)values(datediff(dd,30,getdate()),21)select sum(number) as 总数 ,datepart(mm,[time]) as 月份,count(*) as 记录条数 from report group by datepart(mm,[time])总数 月份 记录条数
32 4 2
135 5 2