随笔分类 - 数据库操作
摘要：CAST(extnumfield0 AS VARCHAR(50))
阅读全文
摘要：select stuff((select ','+objname from contract where charindex(id,pactid) > 0 for xml path('')),1,1,'') from project where id='2c928b7992f766440192fc2
阅读全文
摘要：select GROUP_CONCAT(CONCAT(objname,'-',id)) from humres
阅读全文
摘要：SELECT * FROM `sign_rule` WHERE ( `start_time` > $startTime AND `start_time` < $endTime ) OR ( `start_time` <= $startTime AND `end_time` >= $endTime )
阅读全文
摘要：SELECT * INTO NewTable FROM OriginalTable;
阅读全文
摘要：SELECT STUFF( (SELECT ',' + id FROM assets where id in('4028e481467e27a4014683c552db1794','4028e481467e27a4014683c6ff7717d4') FOR XML PATH('')), 1, 1,
阅读全文
摘要：1.这种是后面有逗号的select xmlagg(xmlparse(content files || ','wellformed) order by files).getclobval() as files from (select field008 as files from ufp2h3p816
阅读全文
摘要：1.原本图 2.解决方法 select regexp_substr(a.numbers,'[^,]+',1,level) as numbers from (select distinct id,numbers from project where isdelete=0 and isactive=1
阅读全文
摘要：--（1）2021年至今天为止签订的合同（不含付款合同）里面，一共有多少个客户（相同的客户要合并算作一个）；1171--（2）2021年至今天为止签订的合同（不含付款合同）里面，帮我统计一个清单，就是累计合同签订额最高的15个客户。 --1,合同金额不为0select (select objname
阅读全文
摘要：select exttextfield6 from assets where charindex(id,'8a070cd88ab2a995018ad0c69def060a,8a070cd88ab2a995018ad0c69ffb0625') > 0 实例：select stuff((select '
阅读全文
摘要：1.select stuff((select ','+a.exttextfield1 from project a where ','+ b.field027 +',' like '%,'+cast(a.id as varchar(32))+',%' for xml path('') ),1,1,'
阅读全文
摘要：select * from docbase where isdelete=0update docbase set subject=STUFF(subject,23,4,'****') where isdelete=0 update docbase set subject=STUFF(subject,
阅读全文
摘要：update formlayoutfield set formula='''$currentuser$''=field001 or ((select orgid from humres where id=''$currentuser$'')=''402880ec5c99d3bd015c9a17bba
阅读全文
摘要：select RAWTOHEX(sys_guid()) from dual
阅读全文
摘要：一千条之前需要下面的语句 select top 1000 id from project order by id 一千条之后需要下面的语句 select top 1000 id from project where isdelete=0 and id not in(select top 10000i
阅读全文
摘要：--DELIMITER $$ CREATE FUNCTION `func_get_split_string_total`( f_string varchar(1000),f_delimiter varchar(5) ) RETURNS int(11) BEGIN -- Get the total n
阅读全文
摘要：CREATE DEFINER=`root`@`localhost` FUNCTION `deleteManyChar`(in_str varchar(2000)) RETURNS varchar(2000) CHARSET utf8 COLLATE utf8_unicode_ciBEGIN DECL
阅读全文
摘要：1.把查询结果用逗号拼接 select group_concat(objname) from humres 2. 把两列的数据拼接在一起 select objname,group_concat(concat_ws('-',id,objname)order by id) as v from humre
阅读全文
摘要：CREATE DEFINER=`root`@`localhost` FUNCTION `deleteManyChar`(in_str varchar(2000)) RETURNS varchar(2000) CHARSET utf8 COLLATE utf8_unicode_ciBEGIN DECL
阅读全文
摘要：适合场景:多字段拼接，字段间使用同样的拼接符(会自动跳过NULL)
阅读全文
