精华内容
下载资源
问答
  • 于是用了 GROUP_CONCAT 函数,将多个数据按默认字符 “,” 进行字符串拼接。然而,想要经行将数据相加的时候,发现MySQL中没有Java中常用的split函数。于是,本人写了一个拆分求和的函数,以满足业务需求。 DROP ...

    因业务需求,需要将一对多的数据合并到一个字段中,用位运算的方式(2的n次方,再求和)来经行数据存储。于是用了 GROUP_CONCAT 函数,将多个数据按默认字符 “,” 进行字符串拼接。然而,想要经行将数据相加的时候,发现MySQL中没有Java中常用的split函数。于是,本人写了一个拆分求和的函数,以满足业务需求。

    DROP FUNCTION IF EXISTS split_sum;
    CREATE FUNCTION split_sum (str VARCHAR(200)) RETURNS INT(20)
    DETERMINISTIC
    BEGIN
    	DECLARE max_size INT(11);
    	DECLARE i INT(11);
    	DECLARE sum INT(20);
    	SET max_size = LENGTH(str) - LENGTH(REPLACE(str, ',', '')) + 1;
    	SET i = 0;
    	SET sum = 0;
    	WHILE i < max_size DO
    		SET i = i + 1;
    		SET sum = sum + SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', i),',',-1);
    	END	WHILE;
    	RETURN sum;
    END;
    

    简单测试

    SELECT split_sum('1,2,5,7,15');
    

    顺便附上GROUP_CONCAT运用的一个小demo

    SELECT
    	a.id,
    	split_sum(GROUP_CONCAT(CASE	WHEN a.type = 1 THEN POWER(2, a.label_id) ELSE 0 END)) market_labels,
    	split_sum(GROUP_CONCAT(CASE WHEN a.type = 2 THEN POWER(2, a.label_id) ELSE 0 END)) tort_labels
    FROM
    	(
    		SELECT
    			1 AS id,
    			1 AS label_id,
    			1 AS type
    		UNION
    			SELECT
    				1 AS id,
    				1 AS label_id,
    				2 AS type
    			UNION
    				SELECT
    					1 AS id,
    					3 AS label_id,
    					1 AS type
    				UNION
    					SELECT
    						1 AS id,
    						7 AS label_id,
    						2 AS type
    	) a
    GROUP BY
    	a.id;
    

    如果有什么疑问或者更好的方法,欢迎留言交流。^ _ ^

    展开全文
  • 利用mysql存储过程拆分查询出来的字符串的操作 每条数据字符串的结构是:(天天)[100];(新闻)[90];(关键)[100]…这样的结构 期待输出的样子如下: 关键字 数值 天天 100 新闻 90 ...

    利用mysql存储过程拆分查询出来的字符串的操作
    每条数据字符串的结构是:(天天)[100];(新闻)[90];(关键)[100]…这样的结构
    期待输出的样子如下:

    关键字 数值
    天天 100
    新闻 90
    关键 100

    首先拆分一条数据的字符串
    把:(天天)[100];(新闻)[90];(关键)[100]
    循环拆分成:(天天)[100],在操作拆分成:天天 100
    这里我们用到函数
    SUBSTRING_INDEX(需要拆分的字符串,以什么符号分割,第几个符号前的字符);
    第三个参数为负数时就是取从分割字符起往右侧的字符截取

    先定义存储过程变量
     DECLARE i INT(11);#循环变量
     DECLARE max_size INT(20);#获取有多少分隔符
     DECLARE stt VARCHAR(200);#查询每条数据的值
     DECLARE num VARCHAR(20);#循环截取用
     DECLARE k_var VARCHAR(20);#需要插入的名称
     DECLARE k_int VARCHAR(20);#需要插入的值
    set stt = select kv from test;
    #创建一个临时表,把数据存入,每次执行删除
    DROP TABLE IF EXISTS temp_list;#如果存在就删除
    CREATE TEMPORARY TABLE temp_list ( #创建临时表 
    kv VARCHAR(200) NOT NULL,
    cou INT NOT NULL
    );
    TRUNCATE TABLE temp_list;#清空
    
    SET max_size = LENGTH(stt) - LENGTH(REPLACE(stt, ';', '')) + 1;
    #这里用字符串长度-去掉分割字符的长度+1 声明循环的次数
    SET i = 1;
    WHILE i <= max_size DO循环开始
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(stt, ';', i),';',-1) INTO num;
    #字符分割得到(天天)[100]
    SELECT SUBSTRING_INDEX(REPLACE(num,'(',''),')',1) into k_var;
    #字符分割得到天天
    SELECT SUBSTRING_INDEX(REPLACE(num,']',''),'[',-1) into k_int;
    #字符分割得到100
    INSERT into temp_list VALUES(k_var,k_int);
    #把得到的
    SET i= i+1;
    END WHILE;
    select * from temp_list;
    
    END
    

    就能得到想要的了;
    但是这只能针对查询出来一条数据的操作
    查询出来是多条数据如:

    kv
    (天天)[100];(新闻)[90];(关键)[100]
    (天天)[100];(新闻)[90];(关键)[100]
    (天天)[100];(新闻)[90];(关键)[100]

    这样的数据就需要用到游标,循环输出游标,在执行上边的操作。
    游标需要

    DECLARE done INT DEFAULT 0; # 自定义控制游标循环变量,默认false
    #声明游标
    #把查询结果赋值给游标tb
     DECLARE tb CURSOR FOR SELECT kv  FROM test ;
     #绑定控制变量到游标,游标循环结束自动转true 
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =1;
    
      #游标循环
      OPEN tb;
      #循环
       REPEAT
      	 FETCH tb into stt; #把游标tb的字段赋值给标量stt,多个结果值,需注意顺序
     	  IF NOT DONE THEN
    	。。。。。这里执行你想要的操作
    	END IF;
       UNTIL done END REPEAT;
      CLOSE tb;#关闭游标
    

    我这里还有一个需求那就是需要查询多个表,表中字段一样。
    存储过程传入表名,表名为变量
    格式为:
    table,table1
    这时就需要拼接sql
    之后用拼接的sql查询把得到的数据赋给游标
    但是游标格式
    DECLARE tb CURSOR FOR SELECT
    无法执行拼接sql
    这时需要用到视图功能
    把拼接sql执行并放入到视图中
    游标在直接查询视图
    全部代码如下:

    DROP** PROCEDURE IF EXISTS `delete_matches`;
    DELIMITER ;;
    CREATE DEFINER=`root`@`%` PROCEDURE `test`(IN `table_type` varchar(32))
    BEGIN#创建存储过程 传入的表名table_type 格式table,table1
    
     DECLARE i INT(11);#循环变量
     DECLARE max_size INT(20);#获取有多少分隔符
     DECLARE stt VARCHAR(200);#查询每条数据的值
     DECLARE num VARCHAR(20);#循环截取用
     DECLARE k_var VARCHAR(20);#需要插入的名称
     DECLARE k_int VARCHAR(20);#需要插入的值
     DECLARE done INT DEFAULT 0; # 自定义控制游标循环变量,默认false
     DECLARE max_size_tab INT;
     DECLARE i_tab INT;
     DECLARE tab_name VARCHAR(20);#表名称
     DECLARE temp_sql VARCHAR(400);#拼接sql
     DECLARE head_sql VARCHAR(400);#拼接sql
     DECLARE un_sql VARCHAR(50);#拼接sql
     DECLARE cr_view VARCHAR(50);#拼接sql
     
     #声明游标
     DECLARE tb CURSOR FOR SELECT kv  FROM v_view ;
     #绑定控制变量到游标,游标循环结束自动转true 
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =1;
     
     SET temp_sql='';#需拼接的语句
     SET head_sql='SELECT kv  FROM ';#需拼接的语句
     SET un_sql = ' UNION ALL ';#需拼接的语句
     SET cr_view = 'create view v_view as ';#创建视图语句
    
     #循环输出传入的表名
     SET max_size_tab = LENGTH(table_type) - LENGTH(REPLACE(table_type, ',', '')) + 1;
     IF  max_size_tab>1 THEN
     SET i_tab = 1;
     WHILE i_tab<=max_size_tab DO
      #拆分传入的表名
     SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(table_type, ',', i_tab),',',-1) INTO tab_name;
     IF i_tab=max_size_tab THEN#拼接语句时,最后不需要加union all
     SET temp_sql=CONCAT(temp_sql,head_sql,tab_name);#拼接sql语句
     ELSE#多张表拼接语句
     SET temp_sql=CONCAT(temp_sql,head_sql,tab_name,un_sql);#拼接sql语句
     END IF;
     SET i_tab = i_tab+1;
     END WHILE;
     ELSE 
     #当传入的表名为一个时,拼接的语句
     SET temp_sql=CONCAT(head_sql,table_type);
     END IF;
     SET temp_sql =CONCAT(cr_view,temp_sql);#拼接创建视图的语句
     SET @sql =temp_sql;
    #执行拼接sql
     PREPARE stmt1 FROM  @sql;
                  EXECUTE stmt1 ;
                  DEALLOCATE PREPARE stmt1;
     #创建临时表
     DROP TABLE IF EXISTS temp_list;#如果存在就删除
         
         CREATE TEMPORARY TABLE temp_list ( #创建临时表 
          kv VARCHAR(200) NOT NULL,
       cou INT NOT NULL
          );
          TRUNCATE TABLE temp_list;#清空
          #游标循环
      OPEN tb;
      #循环
       REPEAT
       FETCH tb into stt;
       IF NOT DONE THEN
    #每条数据的操作
       #获取最大的循环次数
       SET max_size = LENGTH(stt) - LENGTH(REPLACE(stt, ';', '')) + 1;
       SET i = 1;
       WHILE i <= max_size DO
       SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(stt, ';', i),';',-1) INTO num;
        SELECT SUBSTRING_INDEX(REPLACE(num,'(',''),')',1) into k_var;
        SELECT SUBSTRING_INDEX(REPLACE(num,']',''),'[',-1) into k_int;
        INSERT into temp_list VALUES(k_var,k_int);
        SET i= i+1;
        END WHILE;
       END IF;
       UNTIL done END REPEAT;
       CLOSE tb;
       SELECT kv,sum(cou)  FROM temp_list GROUP BY kv;#分组求和临时表
       DROP VIEW IF EXISTS v_view;#删除视图
    END
    ;;
    DELIMITER ;
    

    总结一下:

    1、这个存储过程用到了游标,视图,创建临时表还有一些函数。
    2、声明游标那两句要放在定义变量下边,要不是真报错啊。
    3、这个过程执行效率怎么样,具体没有试过,只是参考。
    4、最后对临时表的操作,就像正常的表一下,可以排序,去多少条啊。

    展开全文
  • mysql直接使用sum(varchar)会导致出现结果出现小数位异常,需要使用cast()函数或者CONVERT()对数据进行格式化,具体如下 Cast(字段名 as 转换的类型 ),可以转换的类型是有限制的,其中类型可以为: CHAR[(N)] ...

    mysql直接使用sum(varchar)会导致出现结果出现小数位异常,需要使用cast()函数或者CONVERT()对数据进行格式化,具体如下

    Cast(字段名 as 转换的类型 ),可以转换的类型是有限制的,其中类型可以为:

    CHAR[(N)] 字符型 
    DATE  日期型
    DATETIME  日期和时间型
    DECIMAL  float型  (DECIMAL (10,2)//设置精度)
    SIGNED  int
    TIME  时间型

    1. -- 错误

    2. SELECT

    3. SUM(price)

    4. FROM

    5. m_user

    6.  
    7. -- 正确

    8. SELECT TRUNCATE

    9. ( SUM( u.price ), 2 )

    10. FROM

    11. m_user u;

    
     
    1. -- 正确

    2. SELECT

    3. SUM(TRUNCATE( u.price , 2 ) )

    4. FROM

    5. m_user u;

    
     
    1. -- 正确 但是和以上误差0.01

    2. SELECT

    3. sum(cast(price as decimal(18,2)))

    4. FROM

    5. m_user

    标注:

    1、TRUNCATE函数将expr按照int_expr长度在小数点后按照位数直接进行截取。

    实例:SELECT TRUNCATE(200.12567,4);      输出结果:200.1256

    2、ROUND函数将expr按照int_expr长度在小数点后按照位数进行四舍五入截取。

    实例:SELECT ROUND(200.12567,4);     输出结果:200.1567

    3、MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值,并产生另一个类型的值。

    两者具体的语法如下: 
    CAST(value as type); 
    CONVERT(value, type); 
    就是CAST(xxx AS 类型), CONVERT(xxx,类型)。

     

    展开全文
  • MySQL字符串常用函数使用方式

    千次阅读 2019-12-26 18:01:29
    五:多个查询列结果合并:CONCAT(str1,str2,…) 1、参数解说 mysql CONCAT() 函数用于将多个字符串连接成一个字符串 语法: CONCAT(str1,str2,…) 返回结果: 返回结果为连接参数产生的字符串。 如有任何一个参数为...

    一:日期加减天数,Date比较

    1:日期加减操作

    select date_add(now(), interval 1 day); - 加1天
    select date_add(now(), interval 1 hour); -加1小时
    select date_add(now(), interval 1 minute); - 加1分钟
    select date_add(now(), interval 1 second); -加1秒
    select date_add(now(), interval 1 microsecond);-加1毫秒
    select date_add(now(), interval 1 week);-加1周
    select date_add(now(), interval 1 month);-加1月
    select date_add(now(), interval 1 quarter);-加1季
    select date_add(now(), interval 1 year);-加1年
    
    #实际使用  5年前,5年后
    UPDATE supplier SET create_time = DATE_ADD( create_time, INTERVAL -5 YEAR ) ; 
    UPDATE supplier SET create_time = DATE_ADD( create_time, INTERVAL 5 YEAR ) ;
    
    #日期比较    一天后的时间相等操作
    select s.* from supplier s,bank b
    where s.create_time = DATE_ADD(b.RecordDate, INTERVAL 1 DAY)

    二:字符串截取操作

    1、字符串拆分: SUBSTRING_INDEX(str, delim, count)

    1. 参数解说
    参数名 解释
    str 需要拆分的字符串
    delim 分隔符,通过某字符进行拆分
    count 当 count 为正数,取第 n 个分隔符之前的所有字符; 当 count 为负数,取倒数第 n 个分隔符之后的所有字符。

    2. 举例 以“:”逗号为分隔符之前的所有字符。

    select SUBSTRING_INDEX('刘晓宇:13187211622',':',1)        提取姓名
    SELECT SUBSTRING_INDEX('刘晓宇:13187211622',':',-1)        提取手机号
    
    #把名字抽出来放到联系人字段中
    UPDATE supplier SET contact_name = SUBSTRING_INDEX( '刘晓宇:13187211622',':',1 ) ;
    #把手机号码抽出来放到手机号字段中
    UPDATE supplier SET mobile_phone = SUBSTRING_INDEX( '刘晓宇:13187211622',':',-1 ) ;

    三:替换 REPLACE

    参数名 解释
    field_name 需要替换的字符串
    string_to_find 目标字符
    string_to_replace 替换字符串,当 string_to_find 找到后就用 string_to_replace 替换成新的 field_name 字符串。

    1、实例 

    #常用的数据库替换 标点符号
    UPDATE market_info SET specifi=REPLACE( specifi, ' ; ', ';' ); 
    UPDATE market_info SET specifi=REPLACE( specifi, ' ;', ';' ); 
    #常用的数据库替换 文字
    UPDATE market_info SET remark=REPLACE( remark, '简介:', '备注:' ); 

    四:字符串长度:LENGTH( str )

    1、参数解说

    参数名 解释
    str 需要计算长度的字符串

    2、 举例 

    (1)获取 ‘中华人民共和国万岁’ 字符串的长度

    #中文和英文会发生很奇妙的结果,试试
    
    SELECT LENGTH('中华人民共和国万岁') ;
    
    SELECT LENGTH('we live in china') ;

    五:多个查询列结果合并:CONCAT(str1,str2,…)

    1、参数解说

    mysql CONCAT() 函数用于将多个字符串连接成一个字符串
     

    语法
                 CONCAT(str1,str2,…)

    返回结果

    • 返回结果为连接参数产生的字符串。

    • 如有任何一个参数为NULL ,则返回值为 NULL。

    2、 举例

    # 查询一个固定值
    SELECT CONCAT('a','b','c');
    
    #具体查询表结构
    SELECT CONCAT(IFNULL(m.name,''),' ',IFNULL(m.price,'')) FROM  market_price m LIMIT 100 ;

    六:查询固定的数据类型,字典类型转换处理 CASE WHEN THEN ELSE END

    1、参数解说

    case...when...then...else...end,是在from前面,可以改变记录中某字段的值,不能决定是否显示该记录

    case...when...then...else...end,可用于对同一记录的多个字段求和,带分支判断。

    2、实际应用

    SELECT cp.name, cp.unit, cp.price
    	, CASE 
    		WHEN cp.source_type = 1 THEN '主材'
    		ELSE '材料'
    	END AS source_type
    FROM collect_price cp
    LIMIT 100

     

    展开全文
  • -- 错误 SELECT SUM(price) FROM m_user -- 正确 SELECT TRUNCATE ( SUM( u.price ), 2 ) FROM m_user u; -- 正确 SELECT SUM(TRUNCATE( u.price , 2 ) ) ...
  • 1,mysql字符串比较:STRCMP(str1, str2):比较两个字符串,如果这两个字符串相等返回0,如果第一个参数是根据当前的排序小于第二个参数顺序返回-1,否则返回1。注意:1,"." 小于 0或"0"2,不...
  • 最后发现是mysql这边数字相加在java后台sql语句里需要转义一下。 如下图所示: String sql = "SELECT NAME,serviceCount\\+fileCount\\+tableCount as resCount FROM (。。。这边是3个表)""; 否则就变...
  • mysql当中,字符串类型间进行加减乘除运算的时候,会截取字符串数字开头的那一部分数字进行运算,如果字符串前面没有数字,那么就只能截取的数值为0,那么进行加减的时候结果都是0,进行乘除的时候结果都是NULL,如下几...
  • mysql字符串和null

    2021-04-29 17:20:19
    如果是varchar类型,默认值用空字符串(’’)会更好一些。带有null的默认值还是可以走索引的,只是会影响效率。当然,如果确认该字段不会用到索引的话,也是可以设置为null的。 在设置字段的时候,可以给字
  • 字符串函数MySQL

    2020-11-23 08:44:00
    字符串函数 (1)length(xx):求字节数,和字符集有关 (2)char_length(xx):求字符数 (3)CONCAT(str1,str2,...) (4)CONCAT_WS(分隔符,str1,str2,...) (5)UPPER(xx);转大写 (6)LOWER(xx):转小写 (7)LEFT(s,n):返回最...
  • 在针对数字类型的数据时,我们有分组求和,求最大最小值等操作。 而在处理字符串类型的数据时,有时我们也需要将同分组的数据合并拼接到一起,实现效果如下图:   2. 解决方案编辑   利用报表设计器的功能...
  • 1.1 格式化字符串 1.1.1 字符串拼接 CONCAT CONCAT_WS 1.1.2 设置大小写即引号包围 LOWER / LCASE / UPPER / UCASE / QUOTE 1.1.3 修剪和补充字符串 LTRIM / RTRIM / TRIM ...
  • 文章目录函数1、单行函数1.1 字符串函数1.2 数值函数/数学函数1.3 日期函数1.4 流程函数1.5 其他函数2、分组函数 两种SQL函数 单行函数 多行函数 1、单行函数 只对一行进行变换,每行返回一个结果 可以嵌套 参数...
  • SQL语句1、按照关键字(符号)截取字符串,2、取集合参数某下标值,3、字符串拼接,4、循环查询语句,5、分组求和函数用法:&lt;select id="getSettleTableDetail" parameterType="map" ...
  • Oracle字符串提取数字

    2020-03-18 15:05:03
    场景:用户需要从memo字段中获取,实时扣款:后的扣款金额,并把同一案件的求和 1.先判断字段中是否含有实时扣款 instr(memo,‘实时扣款’)>0 2.截取实时扣款以及后面一小 case when instr(memo,‘实时扣款’)>0...
  • 2.判断NULL用IS NULL或者IS NOT NULL,SQL语句函数中可以使用ifnull()函数来进行处理,判断空字符用=''或者<>''来进行处理 3.对于MySQL特殊的注意事项,对于timestamp数据类型,如果往这个数据类型插入...
  • 1.字符串截取 (1) substring(str,index)当index>0从左边开始截取直到结束 当index从右边开始截取直到结束 当index=0返回空 (2) substring(str,index,len) 截取str,从index开始,截取len长度 2.去重 我去重...
  • mysql通过可重复的关键id字符串获取关联字段数据总和 在日常开发中,碰到一种情景,传递参数为"1,2,3,4,2,3,4,5,1,2,3"这种,如果用in 或者 find in set ,通过sum查询出的数据是去重过的,也就是"1,2,3,4"只被求和...
  • 比如表1中的No字段是字符串类型,no字段中包含数字及符号,如20+1.5,8+5+4等 那么有办帮法提取出20,1.5,8,5,4这些数字来进行求和等于38.5吗?语句如何写?
  • mysql字段值(字符串)累加

    千次阅读 2018-12-24 12:21:10
    mysql在更新记录时,需要在原来的值上在累加新的值。 例如原来有条记录: id country a784829a-c0dc-4cb6-88a9-8c376fab83a6 USA 现在更新更新country字段:在原值的基础上添加“+++UK”,使其变为如下值: id ...
  • 3、在数据量少的时候可以使用sum()函数直接求和,因为MySQL中它可以自动识别是字符串类型还是数字类型。 4、以上适用于整数,或者是数据量小,要是数据量太大,并且精度较高不适用,那么就可以考虑使用MySQL 的CAST...
  • MySQL varchar字段求和

    2020-11-16 10:35:28
    SELECT SUM(CONVERT(字段, DECIMAL(字段长度,字段小数位))) FROM 表名 参考:mysql 字符串字段转浮点型字段 https://blog.csdn.net/q664243624/article/details/78550162

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 13,362
精华内容 5,344
关键字:

mysql字符串数字求和

mysql 订阅