如何使用sql过程将唯一代码的数量求和并将其显示为datagridview中的一条记录 [英] how to sum the amount of unique codes and show it as one record in datagridview using sql procedure

问题描述

CODE      NAME          AMT       PER
480530    TESTASSET1    100.00    2.00
480530    TESTASSET2    200.00    2.00
480535    TESTASSET3    300.00    5.00
480535    TESTASSET4    400.00    5.00

我正在使用名为T_TEST的表.在datagridview中，我仅显示如下所示的唯一代码的记录.

Im using a table named T_TEST..In a datagridview im showing records of unique codes only as given below..

 CODE      NAME          AMT       PER
480530    TESTASSET1    100.00    2.00
480535    TESTASSET3    300.00    5.00

但是我必须总结唯一代码的数量，并将其显示为如下所示的一条记录...

But i have to sum the amount of unique codes and show it as one record as given below...

 CODE      NAME          AMT       PER
480530    TESTASSET1    300.00    2.00
480535    TESTASSET3    700.00    5.00

请给我查询...

:固定格式

pls give me query for that...

: fixed formatting

推荐答案

以下LINQ 查询可用于对每个代码的金额求和并填充DataTable，可对其进行分配到DataGridView
的DataSource 属性

The following LINQ query can be used to sum the amounts for each code and populate a DataTable, which can be assigned to the DataSource property of DataGridView

//Let us say the Data is read into a DataTable from the SQL DB
//The following code is given here only to run the sample
DataTable ttest = new DataTable();
ttest.Columns.Add("Code",typeof(long),null);
ttest.Columns.Add("Name",typeof(string),null);
ttest.Columns.Add("Amt",typeof(decimal),null);
ttest.Columns.Add("Per",typeof(double),null);
ttest.Rows.Add(480530,"TESTASSET1",100.00,2.00);
ttest.Rows.Add(480530,"TESTASSET2",200.00,2.00);
ttest.Rows.Add(480535,"TESTASSET3",300.00,5.00);
ttest.Rows.Add(480535,"TESTASSET4",400.00,5.00);
//The code upto here is given only to run sample

DataTable uniqueCodes = ttest.Clone();

ttest.AsEnumerable().GroupBy (t => t.Field<long>("Code")).Select (row => 
{DataRow uRow = uniqueCodes.NewRow();
 		uRow[0]=row.Key;
		DataRow gRow = row.FirstOrDefault ();
		uRow[1]=gRow[1];
		uRow[3]=gRow[3];
		uRow[2]=row.Sum (r => r.Field<decimal>("Amt"));
		return uRow;
}
).CopyToDataTable(uniqueCodes,LoadOption.OverwriteChanges);
//The uniqueCodes can be assigned to the DataGridView
dataGridView1.DataSource = uniqueCodes;
//The contents of uniqueCodes will be
//Code  Name        Amt Per 
//480530 TESTASSET1 300 2 
//480535 TESTASSET3 700 5 

SQL查询以汇总amt，添加了[/Edit]

SQL Query to sum amt, added [/Edit]

SELECT CODE, NAME, SUM(AMT) AS AMT, PER
FROM T_TEST
GROUP BY CODE

这篇关于如何使用sql过程将唯一代码的数量求和并将其显示为datagridview中的一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！