作者：简-单贤_917 | 来源：互联网 | 2023-05-17 19:28
Goodday.IhaveanSQLqueryinC#asshown.美好的一天。我在C＃中有一个SQL查询，如图所示。using(SQLiteConnectioncon
Good day. I have an SQL query in C# as shown.
美好的一天。我在C＃中有一个SQL查询，如图所示。
using (SQLiteConnection con = new SQLiteConnection(Connection.DatabaseLocationString))
{
SQLiteCommand cmd = null;
string query =
String.Format("SELECT MONTH(SaleDate) month,
SUM(AmountPaid) sum_amountpaid
FROM {0}
WHERE YEAR(SaleDate) = @1
GROUP BY MONTH(SaleDate) ", Sale.TABLE_NAME);
cmd = new SQLiteCommand(query, con);
cmd.Parameters.Add(
new SQLiteParameter("@1", Properties.Settings.Default.ChartYearlyDisplay));
con.Open();
SQLiteDataReader reader = cmd.ExecuteReader();
con.Close();
}
My challenge is, i have never done nor used a query like this. But what i want to achieve is, i want too Get the value of SUM(AmountPaid) for each month, like this.
我的挑战是，我从未做过，也没有使用像这样的查询。但我想要实现的是，我也想要获得每月的SUM（AmountPaid）值，就像这样。
- January = 20000.00
- 1月= 20000.00
- Febuary = 18000.00
- 2月= 18000.00
- March = 10000.00 .......and so on.
- 三月= 10000.00 ......等等。
But i really dont know how too come of that. please i need your help, Thanks.
但我真的不知道那是怎么回事。拜托，我需要你的帮助，谢谢。
1 个解决方案
1
You just need to loop over the returned results using the SQLiteDataReader
您只需要使用SQLiteDataReader循环返回的结果
SQLiteDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
Console.WriteLine(reader["month"].ToString());
Console.WriteLine(reader["sum_amountpaid"].ToString());
}
con.Close();
Of course, if you need to return this data, you need a data structure where you can store the results like a List
当然，如果需要返回此数据，则需要一个数据结构，您可以在其中存储结果，如List
// The class where you keep the value for a single month...
public class MonthAmount
{
public int Month {get;set;}
public decimal Amount {get;set;}
}
....
// A List where each month of data will be added...
List amountData = new List();
while(reader.Read())
{
// Create the instance of MonthAmount for the current month..
MonthAmount m = new MonthAmount()
{
MOnth= Convert.ToInt32(reader["month"]);
Amount = Convert.ToDecimal(reader["sum_amountpaid"]);
}
// Add it to the list...
amountData.Add(m);
}
reader.Close();
// Return the info to the caller....
return amountData;
Also according to SQLite docs, there is no MONTH or YEAR functions available, you should use strftime with an appropriate settings. You could try with
同样根据SQLite文档，没有可用的MONTH或YEAR函数，您应该使用具有适当设置的strftime。你可以试试
string query = $"SELECT strftime('%', SaleDate) month,
SUM(AmountPaid) sum_amountpaid
FROM {Sale.TABLE_NAME}
WHERE strftime('%Y', SaleDate) = @1
GROUP BY strftime('%m', SaleDate)";
And if I am not wrong, the result of this strftime function is a string not an integer (IE '03' for March, '2017' for year) so perhaps you should create a parameter with the correct datatype.
如果我没有错，那么这个strftime函数的结果是一个字符串而不是一个整数（三月的IE'03'，一年的'2017'）所以也许你应该创建一个具有正确数据类型的参数。
京公网安备 11010802041100号