mysql -在某些字段中使用空值折叠或覆盖多行

Question

我有一张桌子

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(100) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `val1` varchar(100) DEFAULT NULL,
  `val2` varchar(100) DEFAULT NULL,
  `val3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `t` VALUES (1,'tj',1,'Set-1',NULL,NULL),(2,'tj',5,'Z-invalid','Set-2',NULL),(3,'tj',10,'a-invalid','Z-invalid','Set-3');

下面是数据的样子：

mysql> select * from t;
+----+------+----------+-----------+-----------+-------+
| id | user | priority | val1      | val2      | val3  |
+----+------+----------+-----------+-----------+-------+
|  1 | tj   |        1 | Set-1     | NULL      | NULL  |
|  2 | tj   |        5 | Z-invalid | Set-2     | NULL  |
|  3 | tj   |       10 | a-invalid | Z-invalid | Set-3 |
+----+------+----------+-----------+-----------+-------+

我想要做的是按优先级排序，并折叠或覆盖数据。如果列中的值为NULL，则从下一个最高优先级的列中获取该值。如果一个字段不为空，那么就到此为止。

它应该看起来像这样：

+------+-----------+-----------+-------+
| user | val1      | val2      | val3  |
+------+-----------+-----------+-------+
| tj   | Set-1     | Set-2     | Set-3 |
+------+-----------+-----------+-------+

如果我只想得到一个字段，那就很简单了--我只需要

SELECT val1 FROM t WHERE user='tj' AND val1 IS NOT NULL ORDER BY priority ASC LIMIT 1;

但是我实际上希望实现这个想法的表有大约30列，我不想做30种不同的选择。我不能使用MAX，因为它是决定为每列显示哪个值的优先级，而不是列本身的值的最大值。

这里有没有我遗漏的某种聚合选项？

Answer 1

set @val1 = NULL;
set @val2 = NULL;
set @val3 = NULL;

select @val1 AS val1, @val2 AS val2, @val3 AS val3
from
(
  select @val1 := COALESCE(@val1, val1) AS val1, 
         @val2 := COALESCE(@val2, val2) AS val2, 
         @val3 := COALESCE(@val3, val3) AS val3
  from t 
  order by priority asc
) x
limit 1

SQL Fiddle demo