我有一个表,products,维度是这样的:
+------------+--------+-------+--------+
| product_id | length | width | height |
+------------+--------+-------+--------+
| 1 | 23.00 | 25.00 | 22.00 |
| 2 | 14.25 | 14.25 | 1.75 |
| 3 | 19.50 | 3.00 | 2.50 |
| 4 | 3.50 | 11.00 | 19.00 |
+------------+--------+-------+--------+我需要做的是将它们按顺序排列,这样length总是最大的维度,width是第二大维度,height是最小维度。很容易做到这一点:
SELECT
GREATEST(length,width,height) AS length,
LEAST(length,width,height) AS height
FROM products但是我怎么才能确定最终的尺寸呢?然后我试了一下:
SELECT product_id,
SUBSTRING_INDEX(GROUP_CONCAT(dim ORDER BY dim DESC), ',', 1) AS length,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(dim ORDER BY dim DESC), ',', 2), ',', -1) AS width,
SUBSTRING_INDEX(GROUP_CONCAT(dim ORDER BY dim DESC), ',', -1) AS height
FROM
(
SELECT product_id, length AS dim
FROM products
UNION
SELECT product_id, width AS dim
FROM products
UNION
SELECT product_id, height AS dim
FROM products
) v
GROUP BY product_id;这实际上是可行的,但似乎复杂得离谱。有没有人能提出一个比我上面概述的更简单的解决方案呢?
发布于 2018-06-08 01:22:10
假设只有3列,一些简单的数学运算就可以做到:
SELECT length + width + height
- GREATEST(length, width, height)
- LEAST(length, width, height) AS width
FROM products对于超过一组固定的列,基本上是先取消透视然后再透视。一些数据库有一个非标准的扩展,比如T-SQL中的UNPIVOT/PIVOT,但我不认为在MySQL中有一个看起来不像您的查询的简单扩展,或者使用listagg在道德上等效的扩展。
https://stackoverflow.com/questions/50746865
复制相似问题