MySQL 记录一些祖传SQL脚本

记录一些祖传SQL脚本

查找数组对象中

1
2
3
4
5
6
7
8
9
10
11
12
数据库中数据格式

items => [{"field": "AAA", "from": "A123", "to": "431"}, {"field": "BBBB", "from": "B123", "to": "B0"}]


当我要获取 field 的值为 AAA 的form字段

SELECT JSON_UNQUOTE(JSON_EXTRACT(items,replace (JSON_UNQUOTE(JSON_SEARCH(items, 'one','AAA')),'field','from') ))
FROM test 
WHERE items->>'$[*].field' = 'AAA' ;

return A123
分段说明
说明 返回
JSON_SEARCH(items, ‘one’,’AAA’) 获取items 中 第一个值为’AAA’的路径 “$[0].field”
JSON_UNQUOTE(JSON_SEARCH(items, ‘one’,’AAA’)) 去除双引号 $[0].field
replace (JSON_UNQUOTE(JSON_SEARCH(items, ‘one’,’AAA’)),’field’,’from’) 字段字段为 from $[0].from
JSON_UNQUOTE(JSON_EXTRACT(items,replace (JSON_UNQUOTE(JSON_SEARCH(items, ‘one’,’AdminOrg’)),’field’,’from’) )) 根据 $[0].from 查找数据 A123

按类别sum

1
2
3
4
5
SELECT 
sum(if( type=1,money,0)) as  "收入",
sum(if( type=0,money,0)) as  "支出"

from test

根据经纬度计算距离

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SET @lat = 123.123;
SET @lng = 456.123;

SELECT id,`name`,lat,lng,
    ROUND(
        6378.138*2*ASIN(
            SQRT(
                POW(
                    SIN(
                        (@lat*PI()/180-lat*PI()/180)/2
                    ),2
                )+COS(@lat*PI()/180)*COS(lat*PI()/180)*POW(
                    SIN(
                        (@lng*PI()/180-lng*PI()/180)/2
                    ),2
                )
            )
        )*1000
    ) AS lang   
FROM test where 1 HAVING lang > 1

多类型组object-json

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

case
	WHEN test.type = 1 || test.type = 3
	then
		JSON_OBJECT(
			"id",testB.id,
			"test_id",testB.test_id, 
			"AAAA",testB.AAAA,
		)
		)
	WHEN test.type = 2
	then
		JSON_OBJECT(
			"id",testB.id,
			"test_id",testB.test_id, 
			"BBBB",testB.BBB,
			"CCCC",testB.CCCC
		)	

	else JSON_OBJECT("id",null)
END  as jdoc