代码之家 › 专栏 › 技术社区 › Tim Lytle

使用SQL MIN()和GROUP BY的额外字段

aggregate-functions mysql sql

Tim Lytle · 技术社区 · 15 年前

例如,给定一个包含部门名称、员工名称和薪资的表:

SELECT MIN(e.salary), e.* FROM employee e GROUP BY department

显然,我会得到两个好专栏,最低工资和部门。员工姓名(以及任何其他员工字段)是否来自同一行?即与MIN(薪水)的行?

我知道很可能有两名员工的薪水相同(而且是最低的),但我(现在)所关心的是获得(或)的所有信息 )最便宜的雇员。

这会选择最便宜的推销员吗?

SELECT min(salary), e.* FROM employee e WHERE department = 'sales'

从本质上讲,我可以确保随MIN()函数返回的数据与(或)匹配吗 单人间 )用那个最小值记录?

如果数据库很重要,我将使用MySql。

4 回复 | 直到 15 年前

Tom H 15 年前

如果你想在每个部门找到“最便宜”的员工,我有两个选择:

SELECT
     E.*     -- Don't actually use *, list out all of your columns
FROM
     Employees E
INNER JOIN
     (
          SELECT
               department,
               MIN(salary) AS min_salary
          FROM
               Employees
          GROUP BY
               department
     ) AS SQ ON
     SQ.department = E.department AND
     SQ.min_salary = E.salary

或者您可以使用:

SELECT
     E.*
FROM
     Employees E1
LEFT OUTER JOIN Employees E2 ON
     E2.department = E1.department AND
     E2.salary < E1.salary
WHERE
     E2.employee_id IS NULL -- You can use any NOT NULL column here

第二种说法有效地说,让我看看你在同一部门找不到其他低工资员工的所有员工。

在这两种情况下,如果两名或两名以上员工的最低工资相等,那么他们两人(全部)都可以得到。

Quassnoi 15 年前

SELECT  e.*
FROM    employee e
WHERE   e.id =
        (
        SELECT  id
        FROM    employee ei
        WHERE   ei.department = 'sales'
        ORDER BY
                e.salary
        LIMIT 1
        )

要获取每个部门的值,请使用:

SELECT  e.*
FROM    department d
LEFT JOIN
        employee e
ON   e.id =
        (
        SELECT  id
        FROM    employee ei
        WHERE   ei.department = d.id
        ORDER BY
                e.salary
        LIMIT 1
        )

要仅获取有员工的部门的值,请使用:

SELECT  e.*
FROM    (
        SELECT  DISTINCT eo.department
        FROM    employee eo
        ) d
JOIN
        employee e
ON   e.id =
        (
        SELECT  id
        FROM    employee ei
        WHERE   ei.department = d.department
        ORDER BY
                e.salary
        LIMIT 1
        )

当然,在上有一个索引 (department, salary)

dima.rus 12 年前

最快的解决方案:

SET @dep := '';
SELECT * FROM (
  SELECT * FROM `employee` ORDER BY `department`, `salary`
) AS t WHERE IF ( @dep = t.`department`, FALSE, ( @dep := t.`department` ) OR TRUE );

user1668898 8 年前

选择first_name,salary from(选择first_name,salary,Row_NUMBER()(按部门划分,按薪资ASC排序)作为员工的Row_count,其中Row_count=1;