Oracle
去重复 distinct;
例子:
select distinct * from table;
字段下的值前面拼接;
例子:
select '拼接的内容' || 字段名 from table;
截取值substr();
例子:
//substr(TO_CHAR("字段名",第几位,到第几位));
select substr(To_CHAR(name,0,2)) from table;
判断 case when;
例子:
select sex case
when '0' then (赋值) 男
when '1' then (赋值)女 end xb(字段名取别名) from table;
创建临时表 with as;
例子:
with user as (select * from table)
//多个
with
user as (select * from table)
student as (select *from table)
...
结果集只取其中一行或几行 ROWNUM;
例子:
select * from table where ROWNUM = 1;
//ROWNUM = ? 后值是需要取的行数,可以是< ? 几行以内;
exists使用:
例子:
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE Exists (SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
类似于in但是比in快,也有not exists
转载 原文链接:https://blog.csdn.net/fu_pengfei_love822/article/details/70805245
MySQL
json字段转成列
JSON_EXTRACT(json_doc, path[, path] …)
JSON_EXTRACT("json字段名",'$.json中的key名')
例子:
select JSON_EXTRACT(`mail_attachment`, '$.gold') gold ,JSON_EXTRACT(`mail_attachment`, '$.point') point,give_type giveType,trigger_threshold from fbf_mail_config
json里多个{}
JSON_EXTRACT("json字段名",'$[0].json中的key名')
$[0].json第几个,$[*].json 全部,会自动转成行
详细连接:MySql之json_extract函数处理json字段 - 腾讯云开发者社区-腾讯云
判断json类型字段是否存在某个值
JSON_CONTAINS(json字段名,JSON_OBJECT('json中的key的名称', 查询的值))
例子:
AND JSON_CONTAINS(channel_list,JSON_OBJECT('id', #{channelId,jdbcType=INTEGER}))