在组上选择MAX()不会返回相应的兄弟列
在组上选择MAX()不会返回相应的兄弟列
提问于 2019-10-31 09:58:11
我使用的是MySQL Tutorial's sample database。
我需要找到每个semestre和city的销售最差(最低的venta_por_empleado)和最好的销售(最高的venta_por_empleado)的销售人员。使用temporary表，我获得了以下结果集(tbl_ventas_ciudad_semestre，一个临时表)：
| salesRepEmployeeNumber | Nombre_Empleado | city | venta_por_empleado | officeCode | orden_year | semestre | periodo |
|------------------------|------------------|---------------|--------------------|------------|------------|----------|---------|
| 1504 | Barry Jones | London | 6719 | 7 | 2019 | 1 | 2019-1 |
| 1286 | Foon Yue Tseng | NYC | 5016 | 3 | 2019 | 1 | 2019-1 |
| 1323 | George Vanauf | NYC | 6372 | 3 | 2019 | 1 | 2019-1 |
| 1702 | Martin Gerard | Paris | 4180 | 4 | 2019 | 1 | 2019-1 |
| 1401 | Pamela Castillo | Paris | 8464 | 4 | 2019 | 1 | 2019-1 |
| 1370 | Gerard Hernandez | Paris | 12021 | 4 | 2019 | 1 | 2019-1 |
| 1166 | Leslie Thompson | San Francisco | 3587 | 1 | 2019 | 1 | 2019-1 |
| 1165 | Leslie Jennings | San Francisco | 11208 | 1 | 2019 | 1 | 2019-1 |
| 1611 | Andy Fixter | Sydney | 5550 | 6 | 2019 | 1 | 2019-1 |
| 1621 | Mami Nishi | Tokyo | 4923 | 5 | 2019 | 1 | 2019-1 |
| 1501 | Larry Bott | London | 7776 | 7 | 2019 | 2 | 2019-2 |
| 1337 | Loui Bondur | Paris | 6186 | 4 | 2019 | 2 | 2019-2 |
| 1188 | Julie Firrelli | Boston | 4227 | 2 | 2020 | 1 | 2020-1 |
| 1612 | Peter Marsh | Sydney | 6036 | 6 | 2020 | 1 | 2020-1 |
| 1216 | Steve Patterson | Boston | 4876 | 2 | 2020 | 2 | 2020-2 |为了找到销售额最差的销售人员，我将在表中应用MIN(venta_por_empleado)：
SELECT
Nombre_Empleado,
city,
MIN(venta_por_empleado) as venta_por_empleado,
orden_year,
semestre,
periodo
FROM tbl_ventas_ciudad_semestre
GROUP BY
city, periodo
ORDER BY
periodo ASC, venta_por_empleado DESC;结果：
| Nombre_Empleado | city | venta_por_empleado | orden_year | semestre | periodo |
|-----------------|---------------|--------------------|------------|----------|---------|
| Barry Jones | London | 6719 | 2019 | 1 | 2019-1 |
| Foon Yue Tseng | NYC | 5016 | 2019 | 1 | 2019-1 |
| Martin Gerard | Paris | 4180 | 2019 | 1 | 2019-1 |
| Leslie Thompson | San Francisco | 3587 | 2019 | 1 | 2019-1 |
| Andy Fixter | Sydney | 5550 | 2019 | 1 | 2019-1 |
| Mami Nishi | Tokyo | 4923 | 2019 | 1 | 2019-1 |
| Larry Bott | London | 7776 | 2019 | 2 | 2019-2 |
| Loui Bondur | Paris | 6186 | 2019 | 2 | 2019-2 |
| Julie Firrelli | Boston | 4227 | 2020 | 1 | 2020-1 |
| Peter Marsh | Sydney | 6036 | 2020 | 1 | 2020-1 |
| Steve Patterson | Boston | 4876 | 2020 | 2 | 2020-2 |为了找到销售情况最好的销售人员，我将在表中应用MAX(venta_por_empleado)：
SELECT
Nombre_Empleado,
city,
MAX(venta_por_empleado) as venta_por_empleado,
orden_year,
semestre,
periodo
FROM tbl_ventas_ciudad_semestre
GROUP BY
city, periodo
ORDER BY
periodo ASC, venta_por_empleado DESC;| Nombre_Empleado | city | venta_por_empleado | orden_year | semestre | periodo |
|-----------------|---------------|--------------------|------------|----------|---------|
| Barry Jones | London | 6719 | 2019 | 1 | 2019-1 |
| Foon Yue Tseng | NYC | 6372 | 2019 | 1 | 2019-1 |
| Martin Gerard | Paris | 12021 | 2019 | 1 | 2019-1 |
| Leslie Thompson | San Francisco | 11208 | 2019 | 1 | 2019-1 |
| Andy Fixter | Sydney | 5550 | 2019 | 1 | 2019-1 |
| Mami Nishi | Tokyo | 4923 | 2019 | 1 | 2019-1 |
| Larry Bott | London | 7776 | 2019 | 2 | 2019-2 |
| Loui Bondur | Paris | 6186 | 2019 | 2 | 2019-2 |
| Julie Firrelli | Boston | 4227 | 2020 | 1 | 2020-1 |
| Peter Marsh | Sydney | 6036 | 2020 | 1 | 2020-1 |
| Steve Patterson | Boston | 4876 | 2020 | 2 | 2020-2 |使用MIN()和MAX()，它返回正确的sales数字值，但是与这些sales数字相关联的销售人员的姓名不匹配。
我如何才能获得正确的销售编号的正确名称？
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-11-01 03:34:19
考虑到每个表都是临时的约束，我需要创建一个新的临时表，按照Tim的建议从tbl_ventas_ciudad_semestre复制内容。
DROP TEMPORARY TABLE IF EXISTS tbl_ventas_ciudad_semestre_2;
CREATE TEMPORARY TABLE tbl_ventas_ciudad_semestre_2
SELECT * FROM tbl_ventas_ciudad_semestre;然后，可以使用子查询获得第一个表的正确MIN和MAX
/* MIN or worst sales*/
SELECT * FROM tbl_ventas_ciudad_semestre a WHERE
a.venta_por_empleado IN
(
SELECT MIN(b.venta_por_empleado) FROM tbl_ventas_ciudad_semestre_2 b
GROUP BY periodo, city
);
/* MAX or best sales*/
SELECT * FROM tbl_ventas_ciudad_semestre a WHERE
a.venta_por_empleado IN
(
SELECT MAX(b.venta_por_empleado) FROM tbl_ventas_ciudad_semestre_2 b
GROUP BY periodo, city
)Stack Overflow用户
发布于 2019-10-31 10:14:35
考虑到tbl_ventas_ciudad_semestre是一个临时表，这是一个既能满足您的需求(或多或少)又能正常工作的版本。我们可以将您的表连接到子查询，该子查询将查找每个学期和城市的最低和最高销售额：
SELECT
t1.salesRepEmployeeNumber,
t1.Nombre_Empleado,
t1.city,
t1.venta_por_empleado,
t1.officeCode,
t1.orden_year,
t1.semestre,
t1.periodo
FROM tbl_ventas_ciudad_semestre t1
INNER JOIN
(
SELECT
city,
semestre,
MIN(venta_por_empleado) AS min_venta_por_empleado,
MAX(venta_por_empleado) AS max_venta_por_empleado
FROM tbl_ventas_ciudad_semestre
GROUP BY
city,
semestre
) t2
ON t1.city = t2.city AND
t1.semestre = t2.semestre AND
t1.venta_por_empleado IN (t2.min_venta_por_empleado, t2.max_venta_por_empleado)
ORDER BY
t1.semestre,
t1.periodo,
t1.venta_por_empleado;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58635968
复制相关文章
相似问题
腾讯云开发者
