[{"state": "USED", "orderNo": 196564465684, "deductibleAmount": 1000}, {"state": "USED", "orderNo": 196564465500, "deductibleAmount": 30}, {"state": "USED", "orderNo": 196564464361, "deductibleAmount": 500}]怎么用sql提出2个orderNo
时间: 2023-08-22 17:09:20 浏览: 42
要提取这个JSON字符串中的所有orderNo,您可以使用MySQL中的JSON_EXTRACT函数。例如,以下是提取orderNo的SQL查询:
```
SELECT JSON_EXTRACT('{"data":[{"state": "USED", "orderNo": 196564465684, "deductibleAmount": 1000}, {"state": "USED", "orderNo": 196564465500, "deductibleAmount": 30}, {"state": "USED", "orderNo": 196564464361, "deductibleAmount": 500}]}', '$.data[*].orderNo') AS orderNos;
```
这将返回一个包含所有orderNo的JSON数组。如果您只需要前两个orderNo,可以在查询中添加LIMIT 2。例如:
```
SELECT JSON_EXTRACT('{"data":[{"state": "USED", "orderNo": 196564465684, "deductibleAmount": 1000}, {"state": "USED", "orderNo": 196564465500, "deductibleAmount": 30}, {"state": "USED", "orderNo": 196564464361, "deductibleAmount": 500}]}', '$.data[*].orderNo') AS orderNos LIMIT 2;
```
这将返回一个包含前两个orderNo的JSON数组。