mysql - 从两个表连接中获取前两条唯一记录 - Mysql

Question

表 1:employee_detail:

id              name
1               ABC
2               CCC 
3               FFF 
4               ggg
5               jjj

表 2:绩效评估

id          date_of_join        isAppraisalcomplete         emp_id
1           1-07-2010           Yes                         1
2           09-6-2010           Yes                         2
3           10-7-2012           Yes                         3   
4           23-8-2015           No                          4
5           07-11-2018          No                          5

表 3:财务详细信息

id          salary          hike        emp_id          p_a_id          year
1           11000           12          1               1               2016
2           11000           9           1               1               2017
3           11000           11          1               1               2016
4           11000           10          1               1               2017
2           33000           15          2               2               2016
3           36000           10          2               2               2017
4           31000           15          2               2               2016    
5           44001           10          2               2               2017
..........
..........
..........

预期输出:

Emp_id      Date_of_join        isAppraisalcomplete         Salary      Hike    year        
1           1-07-2010           Yes                         11000       12      2016
1           1-07-2010           Yes                         11000       9       2017
2           09-06-2010          Yes                         33000       15      2016
2           09-06-2010          Yes                         36000       10      2017
..........
..............

我用于的查询:

select * from financial_details  bsd inner join performance_appraisal fi on fi.emp_id = bsd.emp_id limit 2;

我的结果:

Emp_id      Date_of_join        isAppraisalcomplete         Salary      Hike        year    
1           1-07-2010           Yes                         11000       12          2016
1           1-07-2010           Yes                         11000       9           2017

在增加限制时，它显示 emp_id 中的所有记录，例如 emp_id 1，不仅显示前两条记录，还显示所有记录。

如何使用 MySql 连接另一个表来从表中获取前两条记录。

在使用 where 条件时，按 desc 排序，限制为 2，我得到一条记录(一名员工)的准确结果。但实际上我试图获取所有记录(所有员工)以及来自 Financial_details 表和 Performance_appraisal 表连接的前两个唯一数据。请帮忙。

编辑:

查询:

CREATE TABLE employee_detail
(
id int,
name varchar(255)
);
CREATE TABLE performance_appraisal
(
id int,
date_of_join varchar(255),
isAppraisalcomplete varchar(255),
emp_id int
);
CREATE TABLE financial_details
(
id int,
salary varchar(255),
hike varchar(255),
emp_id int,
p_a_id int,
t_year varchar(255)
);

insert into employee_detail (id, name) values (1,"abc");
insert into employee_detail (id, name) values (2,"def");
insert into employee_detail (id, name) values (3,"ghi");
insert into performance_appraisal (id, date_of_join, isAppraisalcomplete, emp_id) values (1, "1-07-2010", "Yes", 1);
insert into performance_appraisal (id, date_of_join, isAppraisalcomplete, emp_id) values (2, "09-6-2010", "Yes", 2);
insert into performance_appraisal (id, date_of_join, isAppraisalcomplete, emp_id) values (3, "10-7-2012", "Yes", 3);
insert into performance_appraisal (id, date_of_join, isAppraisalcomplete, emp_id) values (4, "23-8-2015", "No", 4);
insert into performance_appraisal (id, date_of_join, isAppraisalcomplete, emp_id) values (5, "07-11-2018", "No", 5);

insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (1, "11000", "12", 1,1,"2016");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (2, "12000", "9", 1,1,"2017");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (3, "10500", "11", 1,1,"2016");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (4, "11400", "10", 1,1,"2017");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (5, "36000", "15", 2,2,"2016");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (6, "36000", "15", 2,2,"2017");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (7, "31000", "15", 2,2,"2016");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (8, "44000", "15", 2,2,"2017");

Answer 1

这里有一些需要考虑的事情，尽管按照书面说明，此解决方案专门用于 8.0 之前的版本...

SELECT emp_id
     , id 
  FROM 
     ( SELECT emp_id
            , id
            , CASE WHEN @prev = emp_id THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev:=emp_id prev 
         FROM financial_details
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY emp_id
            , id
     ) x 
 WHERE i <= 2;
+--------+------+
| emp_id | id   |
+--------+------+
|      1 |    1 |
|      1 |    2 |
|      2 |    5 |
|      2 |    6 |
+--------+------+