SQL
mySql,oracle
流浪者的短匕
这个作者很懒,什么都没留下…
展开
-
oracle group concat
LISTAGG(ID, ',') WITHIN GROUP(ORDER BY ID) AS ids-- 示例 select LISTAGG(ID, ',') WITHIN GROUP(ORDER BY ID) AS idsfrom EDU_APP_CHANNEL_POSITION group by CHANNEL_ID原创 2020-12-01 13:17:42 · 27954 阅读 · 1 评论 -
jdbc插入返回主键
public Long updateAppLogs(final EduAppSortSendLogs sendLogs, final long userId) { KeyHolder id = new GeneratedKeyHolder(); this.getJdbcTemplate().update(con -> { String sql = ""; int index = 0; Pr...原创 2020-11-27 16:32:27 · 171 阅读 · 0 评论 -
oracle更新数据时如果有就更新没有就插入
merge intomerge into EDU_DOWN_APK ausing (select count(*) num from EDU_DOWN_APK where IOS = 2) b on (b.num > 0) when matched thenupdate set a.UPLOAD_TIME = sysdate where a.IOS = 2 when not matched theninsert (upload_time, down_url, province..原创 2020-10-14 14:55:54 · 708 阅读 · 0 评论 -
先排序,再进行 UNION ALL
该地方是 oracle 数据库示例SELECT * FROM (WITH TMP1 AS ( select ID from ACT_URL_INFO where rownum < 6 order by ID asc), TMP2 AS ( select ID from ACT_URL_INFO where rownum < 6 order by ID desc ) SELECT * FROM TMP1 UNION ALL SELECT * FROM ..原创 2020-07-30 17:23:44 · 1222 阅读 · 0 评论 -
用逗号分隔字符串,如何用like正确匹配
WHERE CONCAT(CONCAT(',', ID), ',') like CONCAT(CONCAT('%,', '1'), ',%')解题思路,将字符串两边加上“,” 然后用like “%,1,%” 这种来匹配。原创 2020-07-23 10:25:07 · 1379 阅读 · 0 评论 -
case when 用法
select ID, (case when ID < 5 then '优秀' when ID < 8 and ID > 6 then '良好' else '不优秀' end) as asdfrom PDCC_BAS_EVENTwhere ID < 9;原创 2020-07-17 16:43:09 · 162 阅读 · 0 评论 -
MySQL格式化时间
MySQL格式化时间DATE_FORMAT(NOW(),'%Y-%m-%d %T');结果:2020-01-08 16:55:48可以使用的格式有:格式 描述 %a 缩写星期名 %b 缩写月名 %c 月,数值 %D 带有英文前缀的月中的天 %d 月的天,数值(00-31) %e 月的天,数值(0-31) %f...原创 2020-01-08 17:36:14 · 162 阅读 · 0 评论 -
oracle 设置序列,设置主键自增
oracle 设置主键自增create table Test_Increase( userid number(10) primary key, /*主键,自动增加*/ username varchar2(20));-- 2创建自动增长序列 CREATE SEQUENCE TestIncrease_Sequence INCREMENT BY 1 -- 每次加几个 ...原创 2020-01-08 17:19:54 · 242 阅读 · 0 评论 -
mysql排序后生成排名
SELECT *,@rownum:=@rownum + 1 AS rank FROM(SELECT * FROM 表格 ORDER BY 字段 ASC) AS a,(SELECT @rownum:=0) AS b;CREATE TABLE `rankTest` ( `id` int(8) NOT NULL auto_increment, `name` varchar(2...原创 2019-07-06 09:42:04 · 1590 阅读 · 1 评论 -
mysql分组后取每组排名第一名的那一整条数据
SELECT * FROM ranktest WHERE id IN(SELECT substring_index(GROUP_CONCAT(id ORDER BY score DESC,id ASC),',',1) idFROM ranktestGROUP BY `name`)SET FOREIGN_KEY_CHECKS=0;-- --------------------...原创 2019-07-10 16:59:34 · 7772 阅读 · 0 评论