mysql 由价格1,价格2,价格3到价格10字段,先找到不为0值的字段并保存最大值,最大值前面为0的用第二个不为0值填充,最大值后面用最大值填充
时间: 2024-03-22 21:42:16 浏览: 19
可以使用如下的 SQL 语句来实现:
```
SELECT
GREATEST(价格1, 价格2, 价格3, 价格4, 价格5, 价格6, 价格7, 价格8, 价格9, 价格10) AS 最大值,
CONCAT_WS('', IF(价格1 = 0, '', 价格1), IF(价格2 = 0 AND 价格1 != 0, '', 价格2), IF(价格3 = 0 AND 价格2 = 0 AND 价格1 != 0, '', 价格3), IF(价格4 = 0 AND 价格3 = 0 AND 价格2 = 0 AND 价格1 != 0, '', 价格4), IF(价格5 = 0 AND 价格4 = 0 AND 价格3 = 0 AND 价格2 = 0 AND 价格1 != 0, '', 价格5), IF(价格6 = 0 AND 价格5 = 0 AND 价格4 = 0 AND 价格3 = 0 AND 价格2 = 0 AND 价格1 != 0, '', 价格6), IF(价格7 = 0 AND 价格6 = 0 AND 价格5 = 0 AND 价格4 = 0 AND 价格3 = 0 AND 价格2 = 0 AND 价格1 != 0, '', 价格7), IF(价格8 = 0 AND 价格7 = 0 AND 价格6 = 0 AND 价格5 = 0 AND 价格4 = 0 AND 价格3 = 0 AND 价格2 = 0 AND 价格1 != 0, '', 价格8), IF(价格9 = 0 AND 价格8 = 0 AND 价格7 = 0 AND 价格6 = 0 AND 价格5 = 0 AND 价格4 = 0 AND 价格3 = 0 AND 价格2 = 0 AND 价格1 != 0, '', 价格9), IF(价格10 = 0 AND 价格9 = 0 AND 价格8 = 0 AND 价格7 = 0 AND 价格6 = 0 AND 价格5 = 0 AND 价格4 = 0 AND 价格3 = 0 AND 价格2 = 0 AND 价格1 != 0, '', 价格10)) AS 填充后的字段
FROM
表名;
```
这个 SQL 语句会先找到不为 0 的字段,然后取其中的最大值。接着,会将最大值前面为 0 的字段用第二个不为 0 的字段填充,最大值后面的字段用最大值填充。最后,会将填充后的字段和最大值一起作为查询结果返回。