Oracle SQL MAX和GROUP BY占用了太多的代码行

anhgbhbe 于 2022-12-11 发布在 Oracle

关注(0)|答案(1)|浏览(116)

I have this long query, where I want to fetch some data about employees:

SELECT e.id,
e.emp_number,
TO_CHAR(SYSDATE,'DD/MONTH/YYYY') "GREGORIAN",
to_char(sysdate,'DD-MM-YYYYY','nls_calendar=''arabic hijrah''') HIJRI,
ba.acc_number "Account Number"
to_char(c.id) "National ID",
en.name

FROM
  relationships r,
  rel_actions ra,
  actions a,
  payrolls p,
  emp_names en,
  citizenships c,
  pay_methods pm,
  bank_accounts ba,
  assignments as,
  emp e

WHERE r.id = ra.id
AND r.id=pm.id
AND as.id = e.id
AND r.id = e.id
AND en.id = e.id
AND en.NAME_TYPE ='GLOBAL'
AND a.action_type  = 'T'
AND a.id = ra.id
AND a.id = p.id
and c.id = e.id
and ba.id=pm.id
AND a.effective_date BETWEEN ba.start_date AND ba.end_date 
AND a.effective_date BETWEEN p.effective_start_date AND p.effective_end_date
AND a.effective_date BETWEEN r.start_date AND r.end_date
AND a.effective_date BETWEEN en.effective_start_date AND en.effective_end_date
AND a.effective_date BETWEEN e.effective_start_date AND e.effective_end_date
AND a.effective_date BETWEEN pm.effective_start_date AND pm.effective_end_date
AND a.effective_date BETWEEN as.effective_start_date AND as.effective_end_date
AND  as.assignment_type = 'E'
AND SYSDATE BETWEEN as.effective_start_date AND  as.effective_end_date

ORDER BY e.emp_number

the result of this query will be something like this :

emp_number      account_number      name      national_id       gregorian         hijri           

1               6456                john      ^*&$^**$^**       6/12/2022         12/5/1444
1               6456                john      ^*&$^**$^**       6/12/2022         12/5/1444
2               4121                Mathew    %&#%^%&%&%^       6/12/2022         12/5/1444
2               4121                Mathew    %&#%^%&%&%^       6/12/2022         12/5/1444

taking the first 2 rows for example, they have different effective_date , so I want to fetch the row that have the newest effective_date :

and a.effective_date in (
select effective_Date from pay_payroll_actions 
where  a.effective_date BETWEEN ba.start_date AND ba.end_date 
AND a.effective_date BETWEEN p.effective_start_date AND p.effective_end_date
AND a.effective_date BETWEEN r.start_date AND r.end_date
AND a.effective_date BETWEEN en.effective_start_date AND en.effective_end_date
AND a.effective_date BETWEEN e.effective_start_date AND e.effective_end_date
AND a.effective_date BETWEEN pm.effective_start_date AND pm.effective_end_date
AND a.action_type  = 'T'
AND a.id = ra.id
AND a.id = p.id
    
)

GROUP BY e.id, e.emp_number,
TO_CHAR(SYSDATE,'DD/MONTH/YYYY'),
to_char(sysdate,'DD-MM-YYYYY','nls_calendar=''arabic hijrah'''),
ba.acc_number ,
to_char(c.id),
en.name

my question is, do I really need to apply all related conditions in the sub-query in order to get the same effective dates resulted from the main query? and if yes, its too long, is there a way to shorten this? thanks in advance

oracle

来源：https://stackoverflow.com/questions/74705913/oracle-sql-max-and-group-by-costs-too-much-lines-of-code

1条答案

按热度按时间

7lrncoxx1#

You can filter the duplicate rows using row_number ()

WITH cte 
 AS
 (SELECT e.id,
  e.emp_number,
  TO_CHAR(SYSDATE,'DD/MONTH/YYYY') "GREGORIAN",
  to_char(sysdate,'DD-MM-YYYYY','nls_calendar=''arabic hijrah''') HIJRI,
   ba.acc_number "Account Number"
   to_char(c.id) "National ID",
   en.name,

   row_number() over (partition BY e.id,
  e.emp_number,ba.acc_number,c.id,en.name ORDER BY a.effective_date desc) AS dup_count
     FROM your_query)
     SELECT * FROM cte WHERE dup_count = 1;

OR Since you are already checking the dates between condition in your main query ,you can refine your sub query to

and a.effective_date in (
 select effective_Date from pay_payroll_actions py
 where a.id=py.id
 )

赞(0）回复(0）举报 2022-12-11

我来回答

Oracle SQL MAX和GROUP BY占用了太多的代码行

1条答案

相关问题

热门标签

最新问答