使用SQL MIN()&的额外字段通过...分组 [英] Extra Fields with SQL MIN() & GROUP BY

问题描述

在使用SQL MIN()函数以及GROUP BY时，是否有任何其他列(不是MIN列或GROUP BY列之一)与匹配的MIN行中的数据相匹配?

When using the SQL MIN() function, along with GROUP BY, will any additional columns (not the MIN column, or one of the GROUP BY columns) match the data in the matching MIN row?

例如，给定一个包含部门名称，员工名称和薪水的表:

For example, given a table with department names, employee names, and salary:

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

很显然，我会得到两个不错的栏目:最低工资和部门.员工名称(和其他任何员工字段)是否来自同一行?就是带有MIN(salary)的行?

Obviously I'll get two good columns, the minimum salary and the department. Will the employee name (and any other employee fields) be from the same row? Namely the row with the MIN(salary)?

我知道很可能会有两个雇员的薪水相同(且最低)，但是(现在)我所关心的只是获取所有信息(或一个)最便宜的员工.

I know there could very possibly be two employees with the same (and lowest) salary, but all I'm concerned with (now) is getting all the information on the (or a single) cheapest employee.

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

Would this select the cheapest salesman?

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

从本质上讲，我可以确定与MIN()函数一起返回的数据是否与具有该最小值的(或单个)记录匹配?

Essentially, can I be sure that the data returned along with the MIN() function will matches the (or a single) record with that minimum value?

如果数据库很重要，那么我正在使用MySql.

If the database matters, I'm working with MySql.

推荐答案

如果您想让每个部门的最便宜"员工，您将有两种选择:

If you wanted to get the "cheapest" employee in each department you would have two choices off the top of my head:

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

第二个声明的工作原理是有效地告诉我，向所有员工展示您在同一部门找不到其他更低薪水的员工.

The second statement works by effectively saying, show me all employees where you can't find another employee in the same department with a lower salary.

在两种情况下，如果两个或多个员工的薪水相等，并且最低，您将同时获得这两个薪金.

In both cases, if two or more employees have equal salaries that are the minimum you will get them both (all).

这篇关于使用SQL MIN()&的额外字段通过...分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！