SQL查询：请建议我下面的查询输出 [英] SQL Query: Please suggest me the output of below Query

问题描述

员工表：

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屋！