C# SQL查询-尝试使用sum(weekly_slot) FROM group by

Question

我正在尝试根据GroupOdd数据库表中的Teacher_Name对weekly_slot求和，但是我得到了所有行的10个值。如果能纠正我，我将不胜感激。除此之外，如何更新GroupOdd dbtable以首先根据较大的sum_weekly_slot进行排序？

谢谢。

cmd2 = new SQLiteCommand();
cmd2 = dbConnect.CreateCommand();
//cmd2.CommandText = "DELETE FROM GroupOdd";
//cmd2.ExecuteNonQuery();
cmd2.CommandText = "SELECT Sum(Weekly_Slot) AS Sum_Weekly_Slot FROM GroupOdd group by Teacher_Name";
DataTable dt2 = new DataTable();
SQLiteDataAdapter da2 = new SQLiteDataAdapter(cmd2);
da2.Fill(dt2);
foreach (DataRow dr2 in dt2.Rows)
{
    //cmd.CommandText = "INSERT INTO GroupOdd (Teacher_Name, Standard, Subject, Weekly_Slot, Balance_Slot) VALUES (@Teacher_Name, @Standard, @Subject, @Weekly_Slot, @Balance_Slot)";
    cmd2.CommandText = "UPDATE GroupOdd SET Sum_Weekly_Slot = @Sum_Weekly_Slot";
    //cmd2.Parameters.AddWithValue("@Sum_Weekly_Slot", dr2["Sum(Weekly_Slot)"].ToString());
    cmd2.Parameters.AddWithValue("@Sum_Weekly_Slot", dr2["Sum_Weekly_Slot"].ToString());
    cmd2.ExecuteNonQuery();
}

Answer 1

您只需要一个带有MAX聚合函数的GROUP BY子句：

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

而不是相应地更新它。

如果

Sum(Weekly_Slot);

不是你想要的

试一试

Count(Weekly_Slot)

Answer 2

SELECT Teacher_Name, Sum(Weekly_Slot) AS Sum_Weekly_Slot FROM GroupOdd 
group by Teacher_Name order by Sum(Weekly_Slot) desc

检查Teacher_Names是否唯一，否则请尝试使用教师的Id (TeacherId)。order by Sum(Weekly_Slot) desc应该首先按最大值对表排序。