我正在尝试进行分配,但我得到的是double值。我选择4月作为标准来计算剩余天数和花费天数。对于过滤假期,BenefitTypeID等于1。请帮帮我。这是我写的问题和查询:

数据库的ERD图位于SQL Server equivalent to GROUP_CONCAT()

1.Number of benefits days allotted to each employee
2.Number of benefit days taken year-to-date
3.Number of benefit days remaining in the calendar year
4.Number of holidays allotted to each employee
5.Number of holidays taken year-to-date
6.Number of holidays remaining in the calendar year


对于此分配,编写查询以产生上述结果。假设您正在运行2013日历年的报告。与上一报告一样,在现实世界中,您可能会使用getdate()函数确定当前日期,并从当年年初到当前时间运行该报告。但是,对于此分配,您可以在查询中对2013年进行硬编码并检索该年的所有数据。

SELECT
  E.LastName+ ' '+E.FirstName As [Employee Name],
  count (B.BenefitTypeID)as [No of Benefit Days],
  SUM(CASE WHEN BT.BenefitTypeID='1' THEN 1 ELSE 0 END) As [Number of Holidays],
  SUM(CASE WHEN B.BT_Month='4' AND B.BT_Year='2013' THEN 1 ELSE 0 END) As [Number of Days Taken],
  SUM(CASE WHEN B.BT_Month!='4' and B.BT_Year!='2013' THEN 1 ELSE 0 END) As [Number of Days Remaining],
  SUM(CASE WHEN BT.BenefitTypeID='1' and B.BT_Month='4' and B.BT_Year='2013' THEN 1 ELSE 0 END) As [Number of Holidays Taken],
  SUM(CASE WHEN BT.BenefitTypeID='1' and B.BT_Month!='4' and B.BT_Year!='2013' THEN 1 ELSE 0 END) As [Number of Holidays Remaining]

FROM BenefitsTaken B
JOIN Employees E
ON( B.EmpID = E.EmpID )

LEFT Join  BenefitTypes BT
ON(B.BenefitTypeID=BT.BenefitTypeID)

Group By E.LastName,E.FirstName,BT.BenefitTypeID,B.BT_Month,B.BT_Year


这是查询结果。员工姓名应该出现一次,谢谢

Employee Name                                                 No of Benefit Days Number of Holidays Number of Days Taken Number of Days Remaining Number of Holidays Taken Number of Holidays Remaining
------------------------------------------------------------- ------------------ ------------------ -------------------- ------------------------ ------------------------ ----------------------------
Doe James                                                     1                  0                  1                    0                        0                        0
Doe James                                                     1                  0                  0                    0                        0                        0
Jones Mark                                                    2                  2                  0                    0                        0                        0
Jones Mark                                                    1                  0                  1                    0                        0                        0
Jones Mark                                                    1                  0                  0                    0                        0                        0
Rice Luke                                                     1                  0                  1                    0                        0                        0
Rice Luke                                                     1                  0                  0                    0                        0                        0
Smith Matthew                                                 1                  1                  1                    0                        1                        0
Smith Matthew                                                 1                  0                  1                    0                        0                        0

(9 row(s) affected)

最佳答案

不是100%肯定,但可能是由两个组LastName,FirstName引起的,因此请使用group By [Employee Name]

SELECT
  E.LastName+ ' '+E.FirstName As [Employee Name],
  count (B.BenefitTypeID)as [No of Benefit Days],
  SUM(CASE WHEN BT.BenefitTypeID='1' THEN 1 ELSE 0 END) As [Number of Holidays],
  SUM(CASE WHEN B.BT_Month='4' AND B.BT_Year='2013' THEN 1 ELSE 0 END) As [Number of Days Taken],
  SUM(CASE WHEN B.BT_Month!='4' and B.BT_Year!='2013' THEN 1 ELSE 0 END) As [Number of Days Remaining],
  SUM(CASE WHEN BT.BenefitTypeID='1' and B.BT_Month='4' and B.BT_Year='2013' THEN 1 ELSE 0 END) As [Number of Holidays Taken],
  SUM(CASE WHEN BT.BenefitTypeID='1' and B.BT_Month!='4' and B.BT_Year!='2013' THEN 1 ELSE 0 END) As [Number of Holidays Remaining]

FROM BenefitsTaken B
JOIN Employees E
ON( B.EmpID = E.EmpID )

LEFT Join  BenefitTypes BT
ON(B.BenefitTypeID=BT.BenefitTypeID)

Group By `Employee Name`

关于mysql - SQL SERVER COUNT查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28996087/

10-12 01:10
查看更多