SQL查询:请建议我下面的查询输出 [英] SQL Query: Please suggest me the output of below Query
本文介绍了SQL查询:请建议我下面的查询输出的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
员工表:
ID名称日期有效工资
1 A 1/04 1 2000
2 B 1/04 0 2000
3 C 3/05 1 4000
4 A 5/05 1 4000
5 B 16/05 1 9000
6 A 2/05 1 3000
7 D 3/08 0 12000
期望的输出:
姓名工资
A 3 9000
B 1 11000
C 1 4000
D 0 12000
解决方案
你最后想要的输出不清楚,
但根据我的理解,下面是你正在尝试的查询。
选择 emp.Name,Count(emp.salary) as Count,Sum(emp.Salary) as Count_Salary
来自员工 as \\ temp ( nolock )
组 按 emp.Name
订单 by emp.Name
如果您有任何进一步的疑问,请告诉我。
Torakami解决方案中的一点点补充
选择 E.Name,COUNT( NULLIF (E.ACTIVE, 0 )) as COUNT,Sum(E.Salary) as 薪水
来自员工E
组 按 E.Name
订单 按 E.Name
最后我得到了询问从下面的输出:
从
中选择A.Name,A.sal,B.present(选择姓名,总和(工资)作为来自EmpWork集团的sal的名称)A,
(按名称选择名称,来自EmpWork集团的Sum(状态))B
其中A.name = b。名称
Employee Table:
ID Name Date Active Salary
1 A 1/04 1 2000
2 B 1/04 0 2000
3 C 3/05 1 4000
4 A 5/05 1 4000
5 B 16/05 1 9000
6 A 2/05 1 3000
7 D 3/08 0 12000
Desired Output:
Name Count Salary
A 3 9000
B 1 11000
C 1 4000
D 0 12000
解决方案
Your last desired output is not clear ,
but as per my understanding below is the query you are trying.
Select emp.Name , Count(emp.salary) as Count , Sum(emp.Salary) as Count_Salary From Employee as emp with (nolock) Group by emp.Name Order by emp.Name
Just let me know if you have any further queries.
A slight addition in Torakami solution
Select E.Name , COUNT(NULLIF(E.ACTIVE,0)) as COUNT, Sum(E.Salary) as Salary From Employee E Group by E.Name Order by E.Name
Finally I got the query from the below output:
select A.Name, A.sal, B.present from
(select Name, sum(salary) as sal from EmpWork group by name) A,
(select Name, Sum(status) as present from EmpWork group by name) B
where A.name= b.name
这篇关于SQL查询:请建议我下面的查询输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文