SQL vb.net查询选择字段总计以及按字段分组的总计百分比 [英] Sql vb.net Query Select field Total along with percent total grouped by field

问题描述

我遇到了sql问题，我想看看是否有人知道解决方案.
我试图从sql中提取数据，将其放入datagrid视图中，然后将数据打印到excel电子表格中.

让我解释一下我在哪里遇到麻烦.数据必须从已经格式化的sql中提取，以便进入数据网格视图，然后再转换为excel.

我完全理解如何获取数据，将其放入datagrid视图，然后打印到格式化的Excel工作表.

这是问题所在:

我需要数据网格视图如下所示:

人员名称\员工编号\第1列总计\第1列占第1列最高值的百分比

总共有5列，每列都有百分比.

到目前为止，这就是我所拥有的，对不起，它将继续运行并且不会被格式化为无法正确读取，我会尽力而为.

这是我拥有的sql语句，它没有执行我需要执行的操作:
---------------------

I am having a sql problem and I want to see if anyone knows a solution.
I am attempting to pull data from sql, put it into a datagrid view, then print the data into an excel spread sheet.

Let me explain where i''m having trouble. The data must be pulled from sql already formatted, so that it goes into the data grid view then to excel.

I totally understand how to get the data out, put it into a datagrid view and then print to a formatted excel sheet.

Here is the problem:

I need the data grid view to look like the following:

Person name \ employee number \ Column 1 total \ column 1 total percent of highest value of column 1

There are a total 5 columns each with percentages.

Here is what i have so far, I apologize that it will run on and not be formatted to be read correctly, I will do my best.

Here is the sql statement I have and its not doing what I need it to do:
---------------------

 Declare @MaxVar1 int
  Declare @MaxVar2 int
  Declare @MaxVar3 int
  Declare @MaxVar4 int
  Declare @MaxVar5 int
 SELECT @MaxVar1 = Count(Column1) FROM dbo.Mytable WHERE Condition1 = 'VbCondition1' AND Condition2 = 'VbConditon2' GROUP BY EmployyeNum ORDER BY COUNT(Column1)

SELECT NAME, EmployeeNum, SUM(Column1), (Sum(Column1) *100 / @MaxVar1
From dbo.MyTable Where Condition1 = 'Vbcondtion1' AND Condition2 = 'VbCondition2'
Group By Name, EmployeeNum Order by @MaxVar1 Desc

-------------------

我得到的是该列的最高输入，而不是该列的员工的计算出的总数.当我将总和除以MaxVar时，我无法获得员工的总数，也无法显示员工完成的百分比.

SQL不允许我在获取值的查询中执行子查询.

还有其他方法吗?

-------------------

What i''m getting is the highest input for the column and not a computed total for the employee for a column. When I divide the Sum by the MaxVar I can not get the total for the employee and show the percent that the employee completed.

SQL is not letting me do a subquery inside of a query that gets a value.

Is there some other way to do this?

Any help or advice will be more than appreciated.

推荐答案

那行不通吗?

So isn''t this working?

SELECT Name, 
       EmployeeNum, 
       SUM(Column1), 
       SUM(Column1) *100 / COUNT(*)
FROM dbo.MyTable 
WHERE Condition1 = 'Vbcondtion1&' 
AND Condition2 = 'VbCondition2'
GROUP BY Name, EmployeeNum 

那应该得到第1列的总和，而在第二列上，总和除以雇员的行数.

That should get the sum of Column 1 and on the second column the sum is divided by the number of rows for the employee.

答案是使用临时表.


代码如下:
---------------

The answer is to use a temp table.


code is below:
---------------

Declare @MaxSales int

SELECT EmployeeNum, SUM(Sales) as SalesTmp
  into #tempSales
  FROM [dbo].[MyTable]
  where Condition = 'Condition1' 
  group by EmployeeNum
  order by EmployeeNum

SELECT @MaxSales = MAX(SalesTmp) from #TempSales

SELECT NAME, EmployeeNum, SUM(Sales), (Sum(Sales) *100 / @MaxSales
From dbo.MyTable Where Condition1 = 'Vbcondtion1' AND Condition2 = 'VbCondition2'
Group By Name, EmployeeNum Order by @MaxSales Desc

DROP TABLE #TempSales

----------------------

这是解决此问题的一种方法.

如果有人知道更好的方法，请发布您的答案.
我希望这也会对其他人有所帮助.这真是一个巨大的痛苦.

----------------------

this is one solution to do this.

If anyone knows a better way to do this please post your answer.
I hope this will help someone else too. This was a huge pain to figure out.

这篇关于SQL vb.net查询选择字段总计以及按字段分组的总计百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！