数据库
drop table
这个作者很懒,什么都没留下…
展开
-
pgsql数据库报错:because it does not have a replica identity and publishes updates
报错信息:SQL 错误 [55000]: ERROR: cannot delete from table "xxxxx" because it does not have a replica identity and publishes updatesHint: To enable updating from the table, set REPLICA IDENTITY using ALTER TABLE. SQL 错误 [55000]: ERROR: cannot delete from .原创 2022-03-16 09:51:21 · 6730 阅读 · 0 评论 -
pgsql 实现oracle的start with与层级树level
oracle SELECT LEVEL AS LVL, TPL.PORT_ID, TPL.PARENT_PORTID FROM TN_PONLINK TPL START WITH TPL.DEV_ID = 'F62A1470D64111E6990CAC577F000001' CONNECT BY PRIOR原创 2021-12-16 11:24:50 · 3603 阅读 · 1 评论 -
pgsql 列转行
1单表查询结果出来只有一行结果select * from table;2现在将查询结果由列转为行展示select unnest(string_to_array(concat_ws(',',a.t_dev_names,a.t_dev_ids,a.T_PORT_NAMES,a.T_PORT_IDS),',')) as result from table as a步骤分析:concat_ws:将几个字段合并为一个字段并用逗号拼接成字符串select concat_ws(',',a.t_dev_原创 2021-12-10 14:22:25 · 3356 阅读 · 1 评论 -
mysql实现oracle的connect By一行转多行功能
在项目中将oracle数据库变更为mysql数据库的时候,在储存过程中遇见了connect by与regexp_substr这个关键字,作用是将一行参数转为多行参数 select regexp_substr('1,2,3,4,5', '[^,]+', 1, rownum) lzh from dual connect by rownum <= length(regexp_replace('1,2,3,4,5', '[^,]+'));现在要将之改为mysql数据库,原创 2020-09-30 17:39:37 · 854 阅读 · 0 评论 -
mysql使用mysql.help_topic表实现一行转多行功能
使用到数据库自带的mysql.help_topic表来属性拆分,help_topic表就是实现行转列功能SELECT SUBSTRING_INDEX( SUBSTRING_INDEX('1,2,3,4,5',',',help_topic_id + 1),',' ,- 1) lzhFROM mysql.help_topicWHERE help_topic_id < (LENGTH('1,2,3,4,5')) - LENGTH(REPLACE ('1,2,3,4,5', ',', ''))原创 2020-09-30 17:34:15 · 8318 阅读 · 1 评论