mysql使用小技巧

MySQL总结
.
mysql使用小技巧
pg使用小技巧
ck使用小技巧

四舍五入

-- 四舍五入取整
ROUND(x)

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

一行转多行

-- 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

字符串合并

-- 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

索引创建

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

日期函数

MySQL常用的日期函数

-- 2023-03-07 14:24:28
    to_char(date_created,'yyyy-mm-dd') as cover_day -- PG
    date_format(date_created,'%Y-%m-%d') as cover_day -- MySQL

    to_char(r.last_modified, 'YYYY-MM-DD HH24:MI:SS') last_modified, -- PG
    date_format(r.last_modified, '%Y-%m-%d %H:%i:%s') last_modified, -- MySQL

count多条件

-- 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解析

-- json对象解析
    -- PG
    (c.json_config ->> 'maxScore')::numeric max_score
    (rd2.rel_json ->> 'content')::text summary

    -- MySQL
    JSON_EXTRACT(c.json_config,'$.maxScore') max_score -- 函数
    (rd2.rel_json ->> '$.content') summary -- 字符串
    (fsc.feature_json_data ->> '$.code') code, -- 字符串
    convert((fsc.feature_json_data ->> '$.actualValue'), decimal(65, 4)) actualValue -- 解析并转为 decimal



-- 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'

视图定义

2023-09-27 00:27:59 Navicat 图形化，直接写查询SQL即可。