主页 / dba / 问题 / 124573
Accepted
Battle_Slug
Asked: 2015-12-26 10:51:21 +0800 CST

获取具有最小值的行

  • 772

我有表公司：

    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;

如何将它减少到我需要的？谢谢你。

mysql join

2 个回答

  1. 试试这个查询：

    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 (select 
employee_id, 
company_id, 
min(score) score from score 
group by employee_id, 
company_id) 
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;
    • 1
  2. Best Answer
    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 获取最小分数。如果它不是您想要的，请根据需要进行调整。

    • 0

相关问题