我在 SQL Server 中有一個表：
| 日期_蒂姆| 機器|案例_錯誤|
|:---------|:--------:|:----------:|
|07/03/21 16:53:PM|測驗1|1|
|07/03/21 16:58:PM|測驗1|1|
|07/03/21 16:59:PM|測驗1|1|
|07/03/21 16:58:PM|測驗2|1|
|07/03/21 16:59:PM|測驗2|1|
|07/03/21 17:00:PM|測驗2|1|
|07/03/21 17:01:PM|測驗3|1|
|08/03/21 16:58:PM|測驗3|1|
|08/03/21 16:58:PM|測驗2|1|
我想從日期 07/03/22 總結列機器所有機器并填充到圖表我嘗試代碼
private void loadchart()
{
var Today = DateTime.Now.ToString("dd/MM/yy");
ChartTop.Series[0].Points.Clear();
ChartTop.ChartAreas["ChartArea1"].AxisX.Interval = 1;
string constring = ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString;
SqlConnection con = new SqlConnection(constring);
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = con;
sqlCmd.CommandType = CommandType.Text;
SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);
try
{
sqlCmd.CommandText = sqlCmd.CommandText = "SELECT TOP 10 Machine, Sum(Case_wrong) as Case_wrong FROM tbl_Count_" cbWorkcell.Text " group by Machine order by SUM(Case_wrong)";
DataSet dtRecord = new DataSet();
sqlDataAdap.Fill(dtRecord);
ChartTop.DataSource = dtRecord;
//set the member of the chart data source used to data bind to the X-values of the series
ChartTop.Series["Series1"].XValueMember = "Machine";
//set the member columns of the chart data source used to data bind to the X-values of the series
ChartTop.Series["Series1"].YValueMembers = "Case_wrong";
// ChartTop.Series[0].ChartType = SeriesChartType.Pie;
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
它有效，但它需要我表中的所有資料進行計算。有沒有辦法按日期過濾資料并在那里求和？請幫我！謝謝。
uj5u.com熱心網友回復：
看起來 date_Tim 是一個日期時間欄位，如果是這種情況，那么您需要where在查詢中添加一個子句，我假設您提到的日期（07/03/22）是 DD/MM/YY 基于查詢需要更新以添加如下子句
"SELECT TOP 10 Machine, Sum(Case_wrong) as Case_wrong FROM tbl_Count_" cbWorkcell.Text " WHERE date_Tim >='2022-03-07 00:00:00.000' group by Machine order by SUM(Case_wrong)"
請注意@madreflection 已指出嚴重錯誤，請在此處修復有關 SQL 注入的更多詳細資訊
編輯 1：如果您只是在尋找特定的一天
"SELECT TOP 10 Machine, Sum(Case_wrong) as Case_wrong FROM tbl_Count_" cbWorkcell.Text " WHERE CAST(date_Tim AS DATE) ='2022-03-07' group by Machine order by SUM(Case_wrong)"
如果您正在尋找當前日期
"SELECT TOP 10 Machine, Sum(Case_wrong) as Case_wrong FROM tbl_Count_" cbWorkcell.Text " WHERE CAST(date_Tim AS DATE) = GETDATE() group by Machine order by SUM(Case_wrong)"
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/428039.html