c#: Select语句没有显示正确的结果
本文关键字:结果 显示 Select 语句 | 更新日期: 2023-09-27 17:54:04
我创建了函数分析学生成绩表。目的是:找到按内容类型分组的最高平均结果。例如:
S_Id ContentType Result
1 T 50
1 V 70
1 G 30
1 G 40
1 V 60
我需要的输出是V,因为V的平均值是最高的。
这是我的代码,但没有显示正确的结果:我的输出显示表中的第五行。
public string analyzeResultTable(string studentId)
{
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["AHSConnection"].ToString();
DataSet ds = new DataSet();
DataSet dsAns = new DataSet();
string BestPrefrence = "";
using (MySqlConnection conn = new MySqlConnection(connStr))
{
conn.Open();
MySqlCommand cmd2 =
new MySqlCommand(
"Select ContentType, MAX(avgContent) from (select ContentType, AVG(result) as avgContent from dopractice where S_Id='" +
studentId + "' GROUP BY ContentType) AS T", conn);
MySqlDataAdapter da = new MySqlDataAdapter(cmd2);
da.Fill(ds);
conn.Close();
}
if (ds.Tables[0].Rows.Count > 0)
{
BestPrefrence = ds.Tables[0].Rows[0]["ContentType"].ToString();
}
return BestPrefrence;
}
这将给出每个ContentType
的平均结果select ContentType, AVG(result) as avgContent from dopractice where S_Id='" + studentId + "' GROUP BY ContentType order by AVG(result) desc limit 1;
添加order by后,您将得到您想要的
您的sql查询不正确。它可以像下面这样:
MySqlCommand cmd2 = new MySqlCommand("select ContentType, AVG(Result) as AvgContent
from dopractice
where S_Id='" + studentId + "'
group by ContentType order by 2 desc limit 1;", conn);
我强烈建议您使用参数化查询,以避免sql注入。
MySqlCommand cmd2 = new MySqlCommand("select ContentType, AVG(Result) as AvgContent
from dopractice
where S_Id= @S_Id
group by ContentType order by 2 desc limit 1;", conn);
cmd2.Parameters.AddWithValue("@S_Id", studentId);