SQL Server中where子句中的累积条件 [英] agrregate condition in where clause in sql server

问题描述

大家好，
我写了一个查询，并得到如下所示的Output，但我希望所有记录的WorkingHours< 48.


请帮助我.我只想要大胆的结果.


查询和输出如下.

Hi All,
I wrote a query and I got an Output like below but I want all records whose WorkingHours <48.


Please help me.I want only result which is bold.


Query and Output is below.

SELECT OffEmail,EmployeeName,(Sum(DHours*60+DMinutes)-sum(MHours*60+MMinutes))/60  as WorkingHours,(Sum(DHours*60+DMinutes)-sum(MHours*60+MMinutes))%60  as WorkingMins from Attendance_Master  where LoginTime between '2011-04-01 00:00:00.000' and '2011-05-01 00:00:00.000'
group by OffEmail,EmployeeName

输出

OffEmail员工姓名WorkingHours WorkingMins

[e-mail] Abhishek Kumar Gupta 204 23
[e-mail] Abhishek Mishra 191 47
[e-mail]管理员24 0
[e-mail] Ajay Pratap Singh 219 41
[e-mail]阿米特·库玛·辛格202 42
[e-mail]阿米特·特瓦里0 0
[e-mail] Amresh Sahu 202 24
Anjum Jeelani 173 15

Output

OffEmail EmployeeName WorkingHours WorkingMins

[e-mail] Abhishek Kumar Gupta 204 23
[e-mail] Abhishek Mishra 191 47
[e-mail] Admin 24 0
[e-mail] Ajay Pratap Singh 219 41
[e-mail] Amit Kumar Singh 202 42
[e-mail] Amit Tewari 0 0
[e-mail] Amresh Sahu 202 24
[e-mail] Anjum Jeelani 173 15

推荐答案

SELECT OffEmail,EmployeeName,(Sum(DHours*60+DMinutes)-sum(MHours*60+MMinutes))/60  as WorkingHours,(Sum(DHours*60+DMinutes)-sum(MHours*60+MMinutes))%60  as WorkingMins from Attendance_Master  where LoginTime between '2011-04-01 00:00:00.000' and '2011-05-01 00:00:00.000'
group by OffEmail,EmployeeName
having (Sum(DHours*60+DMinutes)-sum(MHours*60+MMinutes))/60 < 48 

这篇关于SQL Server中where子句中的累积条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！