獲取具有最小值的行

我有桌子公司：

   company_id |  
- - - - - - - - - - - - - -  
     1        |               
     2        |               

員工：

employee_id | 
- - - - - - - - - - - -
     1     |        
     2     |        

它們通過表employee_company 連接：

employee_id | company_id
- - - - - - - - - - - -
     1     |     1   
     2     |     1
     1     |     2
     2     |     2

還有一些因素，通過表連接到公司：

factor_id | company_id
- - - - - - - - - - - -
   1     |     1   
   1     |     2
   2     |     3

還有分數表，每個分數都有唯一的公司和員工對：

score_id | employee_id | company_id | score | 
- - - - - - - - - - - - - - - - - - - - - - - -
  1     |     1       |      1     | 21
  2     |     1       |      2     | 30
  3     |     2       |      1     | 50
  4     |     2       |      2     | 11

目標是讓表格具有一組顯示每個員工的最低分數的行，按因素過濾，如下所示：

 | employee_id | score | 
- - - - - - - - - - - - -
       1       |  21
       2       |  11

現在我建立了連接所有表並顯示所有分數的查詢：

select * from employee 
join employee_company on employee.employee_id=employee_company.employee_id 
join company on company.company_id=employee_company.company_id 
join score on score.employee_id=employee.employee_id and score.company_id=company.company_id 
join factor_company on factor_company.company_id=company.company_id
join factor on factor_company.factor_id=factor.factor_id
where factor.factor_id =:factor_id;

如何減少到我需要的？謝謝你。

select employee_id, min(score) from 
employee 
join employee_company on employee.employee_id=employee_company.employee_id 
join company on company.company_id=employee_company.company_id 
join score on score.employee_id=employee.employee_id and score.company_id=company.company_id 
join factor_company on factor_company.company_id=company.company_id
join factor on factor_company.factor_id=factor.factor_id
where factor.factor_id = factor_id

group by employee_id

在上面的語句中，您從您提供的查詢中獲取所有員工 ID 及其分數，並獲取每個員工 ID 的最低分數。如果不完全符合您的要求，請根據需要進行調整。

引用自：https://dba.stackexchange.com/questions/124573