我正在尝试进行分配,但我得到的是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/