sql - 如何使 max() 函数只输出一行

Question

Select MAX(end_contract-start_contract), job_description, employer_name
FROM contracts join jobs on contracts_job = jobs_id
              join employers on contracts_client = employer_id
WHERE contracts_end is not NULL
GROUP BY jobs_id,jobs_desc,employer_name

此查询当前输出：

MAX(end_contract-start_contract) jobs_desc        employer_name                      
-------------------------------- --------------- ------------------------------
                             153 Janitor         Microsoft              
                              80 Soldier         Microsoft              
                             119 UNDEFINED       USPS       
                             290 UNDEFINED       Microsoft              
                              89 Pilot           USNC                     
                             119 Cook            USNC                    
                             232 driver          USNC                     
                             340 Soldier         USMC 

我希望输出为：

MAX(end_contract-start_contract) jobs_desc        employer_name                      
-------------------------------- --------------- ------------------------------
                             340 Soldier         USMC 

这就是我使用 max() 的原因，但我不明白为什么它不只是显示最大值。我对 SQL 很陌生

标签： sqloracledatetimesql-order-byinner-join

Answer 1

我怀疑聚合在您的代码中没有做有用的事情。如果您想要最长期限的合同，您可以order by和fetch：

select c.contract_end - c.contract_start as contract_days, j.desc, e.emp_name
from contract c
inner join job j on c.contract_job = j.job_id
inner join employer e on c.contract_client = e.emp_id
where c.contract_end is not null
order by contract_days desc
fetch first 1 row with ties