获取最大bps的sql查询问题 [英] sql query issue for getting max bps

问题描述

亲爱的大家:
我需要一个sql查询的解决方案，iam使用此查询:

"

hi Dear alls:
i have required solution of a an sql query, iam using this query :

"

select e.EMP_CODE,e.NAME,MAX(s.bps) as bps,s.doj from employee e, service_status s
where e.EMP_CODE=s.EMP_SERV_STATUS
group by e.Emp_code,e.name,s.doj
order by EMP_CODE

"

并返回以下结果:

1 USMAN UMER 1 2009-09-11 00:00:00.000
2 SAIMA 1 2008-06-11 00:00:00.000
2 SAIMA 2 2009-06-10 00:00:00.000
3 AIMA 1 2004-06-11 00:00:00.000
3 AIMA 2 2005-06-11 00:00:00.000
3 AIMA 3 2006-06-11 00:00:00.000
3 AIMA 4 2007-02-04 00:00:00.000
4 AHMED ALI 1 2012-07-07 00:00:00.000
5 IMRAN UL ASLAM 10 2007-06-12 00:00:00.000
5 IMRAN UL ASLAM 11 2010-08-12 00:00:00.000
6 ALI 1 2001-07-24 00:00:00.000
6 ALI 1 2002-07-24 00:00:00.000
7苹果0 1900-01-01 00:00:00.000
7苹果1 2003-07-24 00:00:00.000
8 MANGO 1 2006-07-24 00:00:00.000
9 ENTRY 1 2008-07-24 00:00:00.000
9 ENTRY 1 2009-07-24 00:00:00.000
9 ENTRY 1 2010-07-24 00:00:00.000

但我要求 所有员工的记录都只有最大bps ，例如EMP_CODE = 3
3 AIMA 4 2007-02-04 00:00:00.000


请帮我得到结果
regrds
usman umer

"

and it returns the result this:

1 USMAN UMER 1 2009-09-11 00:00:00.000
2 SAIMA 1 2008-06-11 00:00:00.000
2 SAIMA 2 2009-06-10 00:00:00.000
3 AIMA 1 2004-06-11 00:00:00.000
3 AIMA 2 2005-06-11 00:00:00.000
3 AIMA 3 2006-06-11 00:00:00.000
3 AIMA 4 2007-02-04 00:00:00.000
4 AHMED ALI 1 2012-07-07 00:00:00.000
5 IMRAN UL ASLAM 10 2007-06-12 00:00:00.000
5 IMRAN UL ASLAM 11 2010-08-12 00:00:00.000
6 ALI 1 2001-07-24 00:00:00.000
6 ALI 1 2002-07-24 00:00:00.000
7 APPLE 0 1900-01-01 00:00:00.000
7 APPLE 1 2003-07-24 00:00:00.000
8 MANGO 1 2006-07-24 00:00:00.000
9 ENTRY 1 2008-07-24 00:00:00.000
9 ENTRY 1 2009-07-24 00:00:00.000
9 ENTRY 1 2010-07-24 00:00:00.000

but i have required all employees record with only there maximum bps like for EMP_CODE=3
3 AIMA 4 2007-02-04 00:00:00.000


please help me to get the result
regrds
usman umer

推荐答案

假设您只需要员工记录的最大bps，则可以尝试以下查询

Assuming you only need employee records with their maximum bps, you can try the below query

SELECT e.EMP_CODE, e.Name, X.bps, s.doj
FROM employee e
INNER JOIN service_status s ON  e.EMP_CODE=s.EMP_SERV_STATUS
INNER JOIN
(
    SELECT e.EMP_CODE, MAX(s.bps) as bps FROM employee e
    INNER JOIN service_status s ON  e.EMP_CODE=s.EMP_SERV_STATUS
    GROUP BY e.EMP_CODE
) X ON e.EMP_CODE = X.EMP_CODE

这篇关于获取最大bps的sql查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！