针对查询出来数据表内容，将数量进行累积；

Question

请教一下，在c#,查询数据表后的内容，各行数据中，若有其中几项（产品名称、批号、工序）相同时，则将这些符合条件行的数据，之前的累积产量 + 当天产量的合计结果，做为当天的累积产量，请问这个怎么实现；

代码如下，需要通过foreach循环查询dt吧，查询的内容，若是这几项相同，则累积数量进行累加，方便请给完善一下代码，谢谢；

 private void btnquery_Click(object sender, EventArgs e)
{
conn = new SqlConnection("server= 192.168.100.247; database= Whmesinfo;User= Sa;Password=whyy@2021");

                conn.Open();

                //sqlstring = "select * from w_prohourcount where isnull(process,'') like '%" + comprocess.Text + "%'and isnull(proname,'') like '%"
                //+ comproduct.Text + "%' and isnull(batchNo,'') like '%" + batchNo.Text + "%'and  [date] <= '"
                //+ dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'order by date asc";

                sqlstring = @"select * from w_prohourcount where isnull(batchNo,'') like '%" + batchNo.Text
                            + "%' and isnull(process,'') like '%" + comprocess.Text + "%'  and isnull(proname,'') like '%" + comproduct.Text + "%' and [date] <= '"
                + dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'order by date desc";

                try
                {
                    SqlCommand cmd = new SqlCommand(sqlstring, conn);

                    SqlDataAdapter sda = new SqlDataAdapter();

                    sda.SelectCommand = cmd;

                    DataSet ds = new DataSet();

                    sda.Fill(ds, "w_prohourcount");
                  
                    dt = ds.Tables["w_prohourcount"];

                    dataGridView1.DataSource = dt.DefaultView;

                    this.dataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True;

                    dataGridView1.Columns[0].Visible = false;

                    dataGridView1.Columns[1].HeaderText = "日期";
                    dataGridView1.Columns[2].HeaderText = "车间";
                    dataGridView1.Columns[3].HeaderText = "班组";
                    dataGridView1.Columns[4].HeaderText = "班次";
                    dataGridView1.Columns[5].HeaderText = "工序";
                    dataGridView1.Columns[6].HeaderText = "批号";
                    dataGridView1.Columns[7].HeaderText = "产品批号";
                    dataGridView1.Columns[8].HeaderText = "产品名称";
                    dataGridView1.Columns[9].HeaderText = "规格";
                    dataGridView1.Columns[10].HeaderText = "单位";
                    dataGridView1.Columns[11].HeaderText = "当日产量";
                    dataGridView1.Columns[12].HeaderText = "累积产量";
                    dataGridView1.Columns[13].HeaderText = "完工状态";
                    dataGridView1.Columns[14].HeaderText = "人数";
                    dataGridView1.Columns[15].HeaderText = "工时";
                    dataGridView1.Columns[16].HeaderText = "备注";

                    dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;//点击该行，即全选该行；

                    dataGridView1.AutoGenerateColumns = false;

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally { conn.Close(); }
            }

Answer 1

引自免费微信小程序：皆我百晓生

要解决这个问题，我们可以使用LINQ查询来获取满足条件的所有记录，并计算每天的累计产量。这里是一个可能的解决方案：

首先，我们需要定义一个实体类ProductHourCount，它包含所有必要的属性。

public class ProductHourCount
{
    public int Date { get; set; }
    public string Process { get; set; }
    public string Proname { get; set; }
    public string BatchNo { get; set; }
}

然后，我们创建一个新的方法，用于根据提供的筛选条件查询并计算每日的累计产量。

private static IEnumerable<ProductHourCount> GetDailyCumulativeProductions(ProductHourCount[] data,
                                                                       string process, string product, string batchNo)
{
    var filteredData = data.Where(x => x.Process == process && x.Proname == product && x.BatchNo == batchNo).OrderByDescending(x => x.Date);
    
    var dailyCumulativeProductions = filteredData.Select(x => 
        new ProductHourCount()
        {
            Date = x.Date,
            Process = x.Process,
            Proname = x.Proname,
            BatchNo = x.BatchNo,
            CumulativeProduction = x.CumulativeProduction ?? 0,
        });

    return dailyCumulativeProductions;
}

接下来，我们将上述逻辑整合到您的主方法中。您需要确保已经正确连接到数据库并将数据填充到数组中。

private void btnquery_Click(object sender, EventArgs e)
{
    // Connect to the database
    using (SqlConnection conn = new SqlConnection("server= 192.168.100.247; database= Whmesinfo;User= Sa;Password=whyy@2021"))
    {
        conn.Open();
        
        // Define SQL query with dynamic parameters
        string sql = @"
            SELECT 
                p.Date, 
                p.Process, 
                p.Proname, 
                p.BatchNo, 
                SUM(CASE WHEN p.CumulativeProduction IS NULL THEN 0 ELSE p.CumulativeProduction END) OVER () AS CumulativeProduction
            FROM 
                w_prohourcount p
            WHERE 
                p.isnull(batchNo, '') LIKE N'%{batchNo}' AND p.isnull(process, '') LIKE N'%{process}' AND p.isnull(proname, '') LIKE N'%{product}'
                AND [date] <= '{dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss")}'
            GROUP BY 
                p.Date, 
                p.Process, 
                p.Proname, 
                p.BatchNo;
        ";

        // Create a SqlCommand object for the SQL statement
        SqlCommand cmd = new SqlCommand(sql, conn);

        // Execute the SQL statement
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds, "w_prohourcount");

        // Convert the DataTable to a List of ProductHourCount objects
        var products = ds.Tables[0].DefaultView.ToList();

        // Filter the products based on the given conditions
        var filteredProducts = GetDailyCumulativeProductions(products.ToArray(), process, product, batchNo);

        // Update the DataGridView with the filtered data
        foreach (var product in filteredProducts)
        {
            dataGridView1.Rows.Add(product.Date, product.Process, product.Proname, product.BatchNo, product.CumulativeProduction);
        }

        dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        dataGridView1.AutoGenerateColumns = false;
    }
}

这段代码将遍历所有的ProductHourCount对象，过滤出那些符合指定条件的产品，然后按照日期排序，计算每个产品的累计产量，并在DataGridView中显示结果。