c# - 如何简化mysql中的更新查询？

Question

我正在做一个大学考勤项目(Winforms、Mysql、C#)

我想在所有周期列中将 0 更新为 P，将 1 更新为 Ab，将 2 更新为 OD。为此，我编写了一个 for 循环和三个更新查询。

有没有可能在一个查询中执行此操作的想法？

请帮帮我...

 for (int i = 1; i <= 8; i++)
            {
                string period = "Period" + i;
                string t1p = period1;

                command.CommandText = "update attendance_daily_rpt set " + t1p + " = 'P' where " + t1p + " = 0";
                connection.Open();
                command.ExecuteNonQuery();                
                connection.Close();

                command.CommandText = "update attendance_daily_rpt set " + t1p + " = 'Ab' where " + t1p + " = 1";
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                command.CommandText = "update attendance_daily_rpt set " + t1p + " = 'OD' where " + t1p + " = 2";
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }

提前致谢...

Answer 1

创建用于维护映射的表(attendance_daily_rpt_map):

+-----------+---------+| FromValue | ToValue |+-----------+---------+| 0         | P       || 1         | Ab      || 2         | OD      |+-----------+---------+

And then change your query:

UPDATE
    attendance_daily_rpt
    INNER JOIN attendance_daily_rpt_map ON attendance_daily_rpt.t1p = attendance_daily_rpt_map.FromValue
SET
    attendance_daily_rpt.t1p = attendance_daily_rpt_map.ToValue