把Q1Percent、Q2Percent、Q3Percent和Q4Percent 放入datatable
时间: 2023-08-15 14:03:24 浏览: 104
可以使用 SqlDataAdapter 和 Fill 方法将查询结果填充到 DataTable 中,然后再添加四列 Q1Percent、Q2Percent、Q3Percent 和 Q4Percent。
示例代码如下:
```csharp
string connectionString = "YourConnectionString"; // 数据库连接字符串
string query = "SELECT COUNT(CASE WHEN 季度 = 'Q1' THEN 1 ELSE NULL END) AS Q1Count, " +
"COUNT(CASE WHEN 季度 = 'Q2' THEN 1 ELSE NULL END) AS Q2Count, " +
"COUNT(CASE WHEN 季度 = 'Q3' THEN 1 ELSE NULL END) AS Q3Count, " +
"COUNT(CASE WHEN 季度 = 'Q4' THEN 1 ELSE NULL END) AS Q4Count, " +
"COUNT(*) AS TotalCount, " +
"CONCAT(ROUND(SUM(CASE WHEN 季度 = 'Q1' THEN 1 ELSE 0 END)/COUNT(*)*100,2), '%') AS Q1Percent, " +
"CONCAT(ROUND(SUM(CASE WHEN 季度 = 'Q2' THEN 1 ELSE 0 END)/COUNT(*)*100,2), '%') AS Q2Percent, " +
"CONCAT(ROUND(SUM(CASE WHEN 季度 = 'Q3' THEN 1 ELSE 0 END)/COUNT(*)*100,2), '%') AS Q3Percent, " +
"CONCAT(ROUND(SUM(CASE WHEN 季度 = 'Q4' THEN 1 ELSE 0 END)/COUNT(*)*100,2), '%') AS Q4Percent " +
"FROM FAIsum " +
"GROUP BY 季度";
DataTable dataTable = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
adapter.Fill(dataTable);
}
dataTable.Columns.Add("Q1Percent", typeof(string));
dataTable.Columns.Add("Q2Percent", typeof(string));
dataTable.Columns.Add("Q3Percent", typeof(string));
dataTable.Columns.Add("Q4Percent", typeof(string));
foreach (DataRow row in dataTable.Rows)
{
double q1Count = Convert.ToDouble(row["Q1Count"]);
double q2Count = Convert.ToDouble(row["Q2Count"]);
double q3Count = Convert.ToDouble(row["Q3Count"]);
double q4Count = Convert.ToDouble(row["Q4Count"]);
double totalCount = Convert.ToDouble(row["TotalCount"]);
row["Q1Percent"] = Math.Round(q1Count / totalCount * 100, 2) + "%";
row["Q2Percent"] = Math.Round(q2Count / totalCount * 100, 2) + "%";
row["Q3Percent"] = Math.Round(q3Count / totalCount * 100, 2) + "%";
row["Q4Percent"] = Math.Round(q4Count / totalCount * 100, 2) + "%";
}
```
首先,定义数据库连接字符串和查询语句。然后创建一个 DataTable 对象,并使用 SqlDataAdapter 和 Fill 方法将查询结果填充到该对象中。
接着,使用 DataTable 的 Columns 属性添加四列 Q1Percent、Q2Percent、Q3Percent 和 Q4Percent。
最后,遍历 DataTable 的每一行,计算出每个季度所占的百分比,并赋值给相应的列。注意,计算百分比时需要将 Q1Count、Q2Count、Q3Count 和 Q4Count 转换为 double 类型,否则会出现除不尽的情况。
阅读全文