如何添加SQL总结的数据 [英] How do I add data which has been summed by the SQL

问题描述

我有这样一种情况：从这张桌子：

|名称|数量|序列号|

|监视器| 1 | ADJ1DAJAK |

|监视器| 1 | AMBJA1ASL |





我的列表视图一直被视为：

|数量可用|名称|

| 2 |监控|



现在，即我将两台监控器添加到请购单表中。麻烦的是，它在数据网格视图中显示为：

|数量可用|名称|

| 2 |监控|



但我真正想要的是它在我的数据网格视图中显示为：

|名称|数量|序列号|

|显示器| 1 | ADJ1DAJAK |

|显示器| 1 | AMBJA1ASL |



使用的查询：

I have a situation where from this table:
|Name |Qty|serial|
|Monitor |1 |ADJ1DAJAK |
|Monitor |1 |AMBJA1ASL |


It's been viewed as this by my listview:
|Qty Available|Name |
| 2 |Monitor |

Now, i.e. I add two monitors to a requisition table. The trouble is, it is shown on the data grid view as this:
|Qty Available|Name |
| 2 |Monitor |

but what I really want is that for it to be shown as this in my data grid view:
|Name |Qty|serial|
|Monitor |1 |ADJ1DAJAK |
|Monitor |1 |AMBJA1ASL |

Used Query:

SELECT 
    SUM(Supp_Qty) AS Quantity,
    Supp_ProdName,
    Supp_UMeasure,
    Supp_Desc,
    Supp_LotBatch, 
    Supp_ProdBrand, 
    Supp_SuppName, 
    Supp_ID 
FROM 
    `supply_record` 
WHERE 
    Supply_type = 'Assets' 
GROUP BY 
    Supp_ProdName

我是什么尝试过：



没有真正尝试过任何事情因为我真的不知道该怎么做。请帮助。

What I have tried:

Haven't really tried anything since I don't really know what to do. Please help.

推荐答案

MySql不像MSSQL那样使用隐式分组。事实上，在MSSQL中你会得到一个聚合错误。



你只是按一列分组。只有该列和您的聚合（SUM）才是准确的。其他列只显示组中第一行的值。



如果您想让其他列唯一的计数，那么您必须将它们包括在Group By子句：

MySql does not use implicit grouping like MSSQL does. In fact, in MSSQL you would get an aggregate error here.

You are only grouping by one column. Only that column and your aggregate (SUM) will be accurate. The other columns will just show the value of the first row in the group.

If you want to have the count unique to other columns then you must include them in the Group By clause:

SELECT 
    SUM(Supp_Qty) AS Quantity,
    Supp_ProdName,
    Supp_UMeasure,
    Supp_Desc,
    Supp_LotBatch, 
    Supp_ProdBrand, 
    Supp_SuppName, 
    Supp_ID 
FROM 
    `supply_record` 
WHERE 
    Supply_type = 'Assets' 
GROUP BY 
    Supp_ProdName,
    Supp_UMeasure,
    Supp_Desc,
    Supp_LotBatch, 
    Supp_ProdBrand, 
    Supp_SuppName, 
    Supp_ID 

希望有所帮助^ _ ^

Andy

Hope that helps ^_^
Andy

我找到了解决方案的人。我尽力想到一个计划。我创建了一个数据网格视图，并在listview的mousedoubleclick函数上放置了一个代码，以查看该数据网格视图上的所有序列号。然后在添加数据时我只是放了一个while循环，并根据用户请求的数量添加所有序列号和相同的列。

这里是代码：

I've found the solution guys. I did my best to think of a plan . I created a data grid view and placed a code on the mousedoubleclick function of the listview to view all the serial numbers on that data grid view. Then in the adding of data I just put a while loop that and adds all the serial numbers with the identical columns depending on the quantity the user requested.
here is the code:

while(i < qty)
                  {
                      con.Close();


                      MySqlCommand cmd = new MySqlCommand("insert into suppreq_temp(Req_Desc, Req_Brand, Req_Qty, Req_Supplier, RIS_DATE, Req_Date, Person_Incharge, Section_Belong, Tempcnt, Req_Serial) values ('" + txtProdName.Text + "', '" + txtProdBrand.Text + "', '" + txtQty.Text + "', '" + txtSupp.Text + "', '" + reqdate.Text + "', '" + dtprealdate.Text + "','" + txtPerson.Text + "','" + SecCombo.Text + "', '" + txtID.Text + "', '" + dataGridView3.Rows[i].Cells[0].Value.ToString() + "')", con.con);
                      con.Open();
                      reader = cmd.ExecuteReader();
                      
                      con.Close();
                      
                      i++;
                  }

这篇关于如何添加SQL总结的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！