V2EX = way to explore
V2EX 是一个关于分享和探索的地方
Sign Up Now
For Existing Member Sign In
• 请不要在回答技术问题时复制粘贴 AI 生成的内容
This topic created in 1924 days ago, the information mentioned may be changed or developed.
DROP TABLE IF EXISTS `abc`;
CREATE TABLE `abc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`score` json DEFAULT NULL,
`type` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `abc` (`id`, `score`, `type`) VALUES
(1, '{\"v\": 100, \"type\": \"number\"}', 'a'),
(2, '{\"v\": 95, \"type\": \"number\"}', 'a'),
(3, '{\"v\": 60, \"type\": \"number\"}', 'a'),
(4, '{\"v\": 100, \"type\": \"number\"}', 'b'),
(5, '{\"v\": 95, \"type\": \"number\"}', 'b'),
(6, '{\"v\": 60, \"type\": \"number\"}', 'b');
执行 sql 语句
select type, max(score->'$.v') as v1, min(score->'$.v') as v2 from abc group by type;
得出结果
+------+------+------+
| type | v1 | v2 |
+------+------+------+
| a | 95 | 100 |
| b | 95 | 100 |
+------+------+------+
不是预期
type=a, v1=100, v2=60
type=b, v1=100, v2=60
查了下，可能 mysql 的 json 字段索引方式是按照 ascii 码顺序索引的，那么该怎么做才能得到预期结果呢？
 |
1
lovelive1024 Jan 27, 2021  1
转成数字
select type, max(CONVERT(score->'$.v', DECIMAL)) as v1, min(CONVERT(score->'$.v', DECIMAL)) as v2 from abc group by type; |
 |
2
Rache1 Jan 27, 2021 1
```sql
select type, max(CAST(score->'$.v' AS unsigned)) as v1, min(CAST(score->'$.v' AS unsigned)) as v2 from abc group by type; ``` |
|
5
lovelive1024 Jan 28, 2021
@xuyl #4 设置 DECIMAL 的精度，DECIMAL(18, 2)
|
Select Language