我有一张桌子,
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;
这实际上是可行的,但似乎复杂得离谱。有谁能提出一个比我在上面概述的更简单的解决方案吗?