mysql sql select_mysql – SQL SELECT查询

假设我有一个带有三列的SQL表“Company”：“department_id”,“employee”,“job”.像这样的东西：

DEPARTAMENT_ID | EMPLOYEE | JOB

--------------------------------------

1 | Mark | President

1 | Robert | Marketing Manager

1 | Rose | Administration Assitant

2 | Anna | Programmer

2 | Michael | Programmer

2 | Celia | Sales Manager

3 | Jhon | Sales Manager

3 | Donna | Programmer

3 | David | Marketing Manager

我想编写一个返回部门ID的查询,其中至少有50％的工作是相同的.

我的例子中我需要的结果就是：

DEPARTAMENT_ID |

--------------------------------------

2 |

我该如何编写这个SQL查询？我想我尝试了各种各样的东西,但我不明白:(.

解决方法:

这有点棘手.您需要将部门中某个职位的总人数与总人数进行比较.因此,一种方法使用两个聚合：

select department_id

from (select department_id, count(*) as numemp

from t

group by department_id

) d join

(select department_id, max(numemp) as numemp

from (select department_id, job, count(*) as numemp

from t

group by department_id, job

) d

group by department_id

) dj

on d.numemp <= 2 * dj.numemp;

如果您有一个部门在两个作业之间完全分开,则可能会出现重复项.在这种情况下,请使用select distinct.

标签：mysql,sql,select

来源： https://codeday.me/bug/20190523/1156647.html