时间戳

首页 > 解决方案 > SQL做数学找到最小值然后插入列

mysql - SQL做数学找到最小值然后插入列

问题描述

我有 2 张桌子这是我的第一张桌子

+-----------+---------+------+------+---------+---------+-------------+
|   name    |  squad  | val1 | val2 | result1 | result2 | finalresult |
+-----------+---------+------+------+---------+---------+-------------+
| Andrew    | alpha   |    1 |    2 |         |         |             |
| Brian     | bravo   |    3 |    4 |         |         |             |
| Chris     | charlie |    5 |    6 |         |         |             |
| Danny     | alpha   |    7 |    8 |         |         |             |
| Elise     | bravo   |    9 |   10 |         |         |             |
| Fransisca | charlie |   11 |   12 |         |         |             |
+-----------+---------+------+------+---------+---------+-------------+

这是我的第二张桌子

+---------+----------+------+------+
|  squad  | subsquad | val3 | val4 |
+---------+----------+------+------+
| alpha   | air      |    1 |    2 |
| alpha   | land     |    3 |    4 |
| alpha   | sea      |    5 |    6 |
| bravo   | air      |    1 |   -2 |
| bravo   | land     |    3 |   -4 |
| bravo   | sea      |    5 |   -6 |
| charlie | air      |   -1 |    2 |
| charlie | land     |   -3 |    4 |
| charlie | sea      |   -5 |    6 |
+---------+----------+------+------+

我想将数据插入到 result1、result2 和 finalresult

finalresult = MIN ((val1+val3) * (val2+val4)for every subsquad) 
result1 = val 3 that have smallest value in finalresult
result2 = val 4 that have smallest value in finalresult

这是我想实现的示例表

+-----------+---------+------+------+---------+---------+-------------+
|   name    |  squad  | val1 | val2 | result1 | result2 | finalresult |
+-----------+---------+------+------+---------+---------+-------------+
| Andrew    | alpha   |    1 |    2 |       1 |       2 |           8 |
| Brian     | bravo   |    3 |    4 |       8 |      -2 |         -16 |
| Chris     | charlie |    5 |    6 |       0 |      12 |           0 |
| Danny     | alpha   |    7 |    8 |       8 |      10 |          80 |
| Elise     | bravo   |    9 |   10 |      14 |       4 |          56 |
| Fransisca | charlie |   11 |   12 |       6 |      18 |         108 |
+-----------+---------+------+------+---------+---------+-------------+

这就是我所做的

SELECT name, a.squad, val1, val2, result1, result2, ((val1+val3)*(val2+val4)) as finalresult, b.squad, subsquad, val3, val4
FROM a
LEFT JOIN b ON a.squad=b.squad

标签： mysqlsql

解决方案

查看此 http://sqlfiddle.com/#!9/e995b/1

select ad.* from (
SELECT name, a.squad, val1, val2, (val1+val3) as result1, (val2+val4) as result2, ((val1+val3)*(val2+val4)) as finalresult
FROM a
CROSS JOIN b ON a.squad=b.squad
) as ad
inner join ( 
SELECT name, min((val1+val3)*(val2+val4)) as finalresult
FROM a 
LEFT JOIN b ON a.squad=b.squad
group by name,a.squad
 ) as f
 on ad.name = f.name and ad.finalresult = f.finalresult

推荐阅读