c# - 如何使用 C# 查找 MS Access 数据库中不同记录的计数？

Question

我有一个 MS Access 数据库，如下所示

Filing_Year     |  Name
------------------------
02/01/2008      | AAA             
02/01/2008      | AAA             
02/03/2008      | AAA             
03/01/2008      | BBB       

我需要一个 C# 查询，它将得到如下输出并将其显示在 gridview 中

Filing_Year     | file_count
----------------------------
02/01/2008      | 2  
02/03/2008      | 1  
03/01/2008      | 1

这是我试过的代码:

dataGridView1.Columns.Add("Column", "FileCount");    // file count is not an element in database

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=|DataDirectory|\RMS_Database.mdb");

con.Open();

OleDbDataAdapter sda = new OleDbDataAdapter("select Filing_Year,count(*) from Checklist_Data group by Filing_Year ", con);

DataTable dt = new DataTable();
sda.Fill(dt);

BindingSource bSource = new BindingSource();
bSource.DataSource = dt;

dataGridView1.DataSource = bSource;
sda.Update(dt);

有了这个，我只能看到唯一的 filing_year，但看不到计数。

提前致谢。非常感谢您的帮助。

Answer 1

您需要像这样修改您的 SQL 查询:

SELECT Filing_Year, COUNT(Name) as file_count
FROM Checklist_Data 
GROUP BY Filing_Year, Name