mysql使用小技巧

发表于 | 分类于 |

mysql使用小技巧
pg使用小技巧
ck使用小技巧

四舍五入

1
2
3
4
5
-- 四舍五入取整
ROUND(x)

-- 四舍五入取整，保留2位小数
ROUND(x, 2)

一行转多行

1
2
3
4
5
6
7
8
9
-- 2023-02-14 16:46:09
WITH s AS (SELECT store_id, org_structure_id FROM t_sys_org_structure_store WHERE tenant_id=$tenant_id$)
SELECT
    s.store_id,
    -- o.path          -- 一行变多行
    substring_index(substring_index(o.path, ',', m.help_topic_id + 1) ,',', -1) org_id
FROM s left join v_sys_org_structure o on s.org_structure_id=o.id
join mysql.help_topic m on m.help_topic_id < LENGTH(o.path) - LENGTH(REPLACE(o.path,',','')) + 1
-- WHERE s.store_id=8

字符串合并

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 2023-04-17 09:49:09

-- ----- --
-- MySQL --
-- ----- --

-- concat    所有元素直接拼接（任何一个为NULL，结果为NULL）
SELECT concat('a', 'b', 'c') -- abc
SELECT concat('a', NULL, 'c') -- NULL

-- concat_ws 第一个为拼接字符，忽略NULL
SELECT concat_ws('.', 'a', 'b', NULL, 'c') -- a.b.c

-- 字段合并一行，支持排序
SELECT tenant_id, group_concat(id separator '.')  FROM hawkeye_tenant_config GROUP BY tenant_id
SELECT tenant_id, group_concat(id ORDER BY id DESC separator '.')  FROM hawkeye_tenant_config GROUP BY tenant_id

索引创建

1
2
-- 2023-03-16 11:42:51
CREATE INDEX hawkeye_job_log_date_created_job_id_idx ON hawkeye_job_log (date_created, job_id);

日期函数

1
2
3
4
5
6
7
-- 2023-03-07 14:24:28
<if test="dbType == 'PostgreSQL'">
    to_char(date_created,'yyyy-mm-dd') as cover_day
</if>
<if test="dbType == 'MySQL'">
    date_format(date_created,'%Y-%m-%d') as cover_day
</if>

count多条件

1
2
3
4
5
6
7
8
9
10
11
-- 2023-03-17 18:03:08
-- 兼容pg
WITH tmp AS (SELECT COUNT(1) FROM patrol_config WHERE tenant_id=$tenant_id$ AND config_type='CATEGORY')

SELECT
		COUNT(CASE WHEN rel_type='SIGNED' THEN 1 END) signed,
		COUNT(CASE WHEN rel_type='CATEGORY_TASK' THEN 1 END) category_task,
		(SELECT * FROM tmp) category_task_all,
		COUNT(CASE WHEN rel_type='DIAGNOSIS_TASK' THEN 1 END) diagnosis_task,
		COUNT(CASE WHEN rel_type='PATROL_SUMMARY' THEN 1 END) patrol_summary
FROM patrol_rel_detail WHERE record_id=$record_id$

json解析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- json对象解析
    -- PG
    <if test="$tenant_id$ != 'anta'">
        cast((fsc.feature_json_data -> 'code') as text) "code",
        cast((fsc.feature_json_data -> 'actualValue') as text) "actualValue"
    </if>

    -- MySQL
    <if test="$tenant_id$ == 'anta'">
        (fsc.feature_json_data ->> '$.code') code,
        convert((fsc.feature_json_data ->> '$.actualValue'), decimal(65, 4)) actualValue
    </if>






-- json数组解析         ===> [{"featureCode": "sales", "featureName": "销售额", "targetValue": "50000"},{"featureCode": "atv", "featureName": "客单价", "targetValue": "650"}]
SELECT
 SUM(tmp.target_value) sales
FROM
    $table$,
    JSON_TABLE($table$.fetch_target_json, '$[*]' COLUMNS(
     feature_code VARCHAR(255) PATH '$.featureCode',
     target_value DECIMAL(10,2) PATH '$.targetValue'
    )) tmp
WHERE
    tenant_id=$tenant_id$
    AND rel_type='STORE'
    AND rel_id IN ($storeIds$)
    AND stats_date BETWEEN  $startDate$ AND $endDate$
    -- AND stats_date BETWEEN '2023-01-01' AND '2023-01-09'
 AND tmp.feature_code='sales'