你是不是在尝试这样的东西：

create table people(
id varchar(9) not null ,
name varchar(50) not null ,
doj date ,
PREV_COMPANY_ID varchar(50),
CURR_COMPANY_ID varchar(50),
Primary key id(`id`)
);

insert into people values ('1795f','ALEX BROWN','1973-03-02','4e5b','123a'),('8772','Chris Mitchell','2016-06-15','4746','4e5b'),('5e03','Patt Hobbs','1976-09-14','4e5b','123b');

create table company(
id varchar(9) not null ,
name varchar(50) not null ,
Primary key id(`id`)
);

insert into company values ('4746','Mora-Sullivan'),('49de6','Harmon-Miller'),('4e5b','Fakesmith');

select p.name,p.CURR_COMPANY_ID,company.name as company_name 
from people p join (select PREV_COMPANY_ID ,count(*) as comp_high_nr from people group by PREV_COMPANY_ID  order by comp_high_nr desc limit 1) as t on p.CURR_COMPANY_ID =t.PREV_COMPANY_ID join company on p.PREV_COMPANY_ID=company.id  ;

试试这个会对你有帮助

CREATE TABLE tblcompany (
  id varchar(9) NOT NULL,
  name varchar(50) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO tblcompany (id,name) VALUES 
 ('1','Mora-Sullivan'),
 ('2','Harmon-Miller'),
 ('3','Fakesmith');

CREATE TABLE tblpeople (
  id varchar(9) NOT NULL,
  name varchar(50) NOT NULL,
  doj date DEFAULT NULL,
  PREV_COMPANY_ID varchar(50) DEFAULT NULL,
  CURR_COMPANY_ID varchar(50) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO tblpeople (id,name,doj,PREV_COMPANY_ID,CURR_COMPANY_ID) VALUES 
 ('1','ALEX BROWN','1973-03-02','1','2'),
 ('2','Chris Mitchell','2016-06-15','1','3'),
 ('3','Patt Hobbs','1976-09-14','2','3');

首先，我们需要找到员工工资最高的公司

SELECT t1.CURR_COMPANY_ID, COUNT(t1.id) as `NoOfEmployee` FROM tblpeople as t1 GROUP BY t1.CURR_COMPANY_ID ORDER BY NoOfEmployee DESC LIMIT 1;

然后我们加入people表以获取该公司的所有员工

SELECT t1.*, t2.name as `CURR_COMPANY`, t3.name as `PREV_COMPANY` FROM tblpeople as t1 LEFT JOIN tblcompany as t2 ON t1.CURR_COMPANY_ID=t2.id LEFT JOIN tblcompany as t3 ON t1.PREV_COMPANY_ID=t3.id INNER JOIN (SELECT CURR_COMPANY_ID, COUNT(id) as EmployeeCount FROM tblpeople GROUP BY CURR_COMPANY_ID ORDER BY EmployeeCount DESC LIMIT 1) as t4 ON t1.CURR_COMPANY_ID=t4.CURR_COMPANY_ID;

请检查以下查询是否符合您的要求

SELECT *
  FROM PEOPLE
 WHERE CURR_COMPANY_ID IN (  SELECT PREV_COMPANY_ID
                               FROM (  SELECT PREV_COMPANY_ID, COUNT (1) AS C
                                         FROM PEOPLE
                                     GROUP BY CURR_COMPANY_ID
                                     ORDER BY C DESC)
                           ORDER BY c DESC)

假设是mysql：

SELECT people.Name, companies.Name
FROM people
JOIN companies ON people.PREV_COMPANY_ID = companies.ID
WHERE people.CURR_COMPANY_ID = (
SELECT companies.ID
FROM people
JOIN companies ON people.PREV_COMPANY_ID = companies.ID
GROUP BY companies.ID
ORDER BY COUNT(*) DESC
LIMIT 1
)
ORDER BY people.Name ASC;