mysql - 从 MySQL 中的表的一部分中选择最小最大值和最大最小值

Question

我想从另一个表 2 中为表 1 中的每个值选择最小最大值和最大最小值。示例输入

table2
name, value,y
f1, .01,.04
f1,.02,.05
f1,.05,.06
f1,.45,.07
f2,.03,.09
f2,.05,.02

table1
name, value
f1, .04
f2,.04

expected output
table3
name, value,min_max-value,max_min-value,y(min_max-value),y(max_min-value)
f1, .04,.02,.05,.05,.06
f2,.04,.03,.05,.09,.02

Answer 1

您可以使用以下查询从 t2 获取 MAX 和 MIN 值:

SELECT
    t1.name,
    t1.value,
    (SELECT min(value) FROM table2 t2 WHERE t2.name = t1.name),
    (SELECT MAX(value) FROM table2 t2 WHERE t2.name = t1.name)
FROM table1 t1

编辑:要使值更接近 t1.value 和相应的 y 值，您可以这样做:

SELECT 
    t1.NAME, 
    t1.value, 
    (SELECT MAX(t2.value)
      FROM table2 t2
      WHERE t2.NAME = t1.NAME AND t2.value < t1.value) AS maxmin, 
    (SELECT MIN(t2.value)
      FROM table2 t2
      WHERE t2.NAME = t1.NAME AND t2.value > t1.value) AS minmax, 
    (SELECT t2.y
      FROM table2 t2
      WHERE t2.NAME = t1.NAME AND t2.value = (
            SELECT MAX(t3.value)
            FROM table2 t3
            WHERE t3.NAME = t1.NAME AND t3.value < t1.value
            )) AS ymaxmin, 
    (SELECT t2.y
      FROM table2 t2
      WHERE t2.NAME = t1.NAME AND t2.value = (
            SELECT MIN(t3.value)
            FROM table2 t3
            WHERE t3.NAME = t1.NAME AND t3.value > t1.value
            )) AS yminmax
FROM table1 t1

在 this fiddle 中查看