环境准备
需要安装 node.js
环境
具体步骤
- 按照sql-formatter 插件页面上的
Usage
步骤,执行npm install -g sql-formatter
安装模块 - 在DBeaver的首选项中进行设置
- 找到编辑器中的SQL格式化选项,勾选“使用临时文件”,然后在命令行中输入以下命令。其中,
node_moudles
的路径可能不一样,根据自己的安装路径填写;斜杠全部用双斜杠,因为单斜杠可能被转译报错;-c config.json
是配置文件,这个文件放到DBeaver
的安装目录里就行,在插件的Usage
中可以找到具体的参数,可以不填;最后一个参数是软件传入的临时SQL文件的文件路径,用来给脚本获取SQL文件。
因为采用的是npm install -g xxx安装,所以安装路径可以通过npm config get prefix获取,包就在配置的这个路径下面!!
node C:\\Users\\Administrator\\node_moudules\\sql-formatter\\bin\\sql-formatter-cli.js -c config.json ${file}
如果上面命令不行,就直接用cmd命令,如下
# 这个路径就是通过npm config get prefix得来的路径
C:\\Users\\xwup\\AppData\\Roaming\\npm\\sql-formatter.cmd -c config.json ${file}
效果展示
这是我使用的配置文件,简单调整了几个参数,可以参考:
{
"language": "sql",
"tabWidth": 2,
"keywordCase": "upper",
"linesBetweenQueries": 2,
"tabulateAlias": true,
"indentStyle": "standard"
}
出来的效果如下所示:
SELECT
tmp.zh_lable AS sitename,
SUM(tmp.red) AS alarmcnt_urgent,
SUM(tmp.orange) AS alarmcnt_important,
SUM(tmp.yellow) AS alarmcnt_commonly,
SUM(tmp.switch_count) AS switch_count,
SUM(tmp.transformer_count) AS transformer_count,
SUM(tmp.ups_count) AS ups_count
FROM
(
SELECT
switch.zh_label AS zh_lable,
COUNT(IF(switch.capacity > 0.9, 1, NULL)) AS red,
COUNT(
IF(
switch.capacity > 0.8
AND switch.capacity <= 0.9,
1,
NULL
)
) AS orange,
COUNT(
IF(
switch.capacity > 0.7
AND switch.capacity <= 0.8,
1,
NULL
)
) AS yellow
FROM
(
SELECT
ccs.zh_label,
dev_obj.object_id,
SUM(ifnull(cptl.last_value, 0)) AS used,
ifnull(ccs2.aabbcc * (ccs2.model_num - 1), 0) AS total,
SUM(ifnull(cptl.last_value, 0)) / (ccs2.aabbcc * (ccs2.model_num - 1)) AS capacity
FROM
ccccccdddd ccs -- 设备obj 表
INNER JOIN aabbccdd dev_obj ON dev_obj.bcd = ccs.id
AND dev_obj.abc = 1 -- 这儿关联后 ddd 就只有设备信息了
INNER JOIN fffffff ccs2 ON dev_obj.id = ccs2.id
LEFT JOIN aabbccdd sig_obj ON dev_obj.id = sig_obj.id
AND sig_obj.dddeeee IN ('1234565') -- 通过 abc 限制 只取了 信号相关的数据
LEFT JOIN aabbddeerr cptl ON sig_obj.id = cptl.id
WHERE
1 = 1
AND dev_obj.delete_at IS NULL
AND ccs.city_id IN (% s)
AND ccs.power_site_type IN (% s)
GROUP BY
ccs.zh_label,
dev_obj.object_id
) AS switch
GROUP BY
switch.zh_label
) AS tmp