MySQL:用组中任何非空值填充空字符串

Question

这是一个表tests(简化(：

-------------------------
| id | test_name | unit |
-------------------------
| 1  | Voltage   | V    |
| 2  | Current   |      |
| 3  | Frequency | Hz   |
| 4  | Voltage   | v    |
| 5  | Voltage   |      |
| 6  | Voltage   | V    |
| 7  | Current   | A    |
| 8  | Voltage   | V    |
-------------------------

我需要用其组中的任何非空值(通过test_name(填充空的unit字段，即Voltage的单位可以是V或v，而Current的单位应该是A。有没有办法用update ... join ... group by或类似的东西做到这一点？

Answer 1

使用此查询(对于MySql 5.7+(：

SELECT test_name, ANY_VALUE(unit) unit
FROM tests
WHERE unit IS NOT NULL
GROUP BY test_name

您为每个test_name获得一个非空unit(前提是该test_name至少有一个非零unit(

将其加入UPDATE语句中的表：

UPDATE tests t1
INNER JOIN (
SELECT test_name, ANY_VALUE(unit) unit
FROM tests
WHERE unit IS NOT NULL
GROUP BY test_name
) t2 ON t2.test_name = t1.test_name
SET t1.unit = t2.unit
WHERE t1.unit IS NULL;

请参阅演示

您可以使用MIN()或MAX()来代替ANY_VALUE()

Answer 2

这应该有效：

UPDATE tests AS t1 JOIN tests AS t2 USING (test_name)
SET t1.unit = t2.unit
WHERE t1.unit IS NULL AND t2.unit IS NOT NULL;