你该好好学习
2021-10-23 19:49
采纳率: 100%
浏览 1.1k

存储过程,计算一个字符串中每一个字母的数量,今晚有效

创建了一张表,如下
CREATE TABLE label_table (
company varchar(200) PRIMARY KEY ,
label varchar(255),
label_num varchar(255)
);
第一列插入内容举例为A,B,C
第二列插入内容为纯字母无规律字符串,例如'hjdk','dfdkldg'
插入时,两个参数需要一起提供,例如('A','gdhjk')
如果A不存在,则插入以上内容,如果表中已经存在主键A,则第二个字段追加在已有字段之后,
例如表中已经存在(A,dhk)
再插入(A,jd),则最终字段为(A,dhkjd)
所以存储过程需要提供两个输入参数
以下这句似乎能达到类似的效果,仅作参考
INSERT INTO label_table VALUES('B', 'dfg')
ON DUPLICATE KEY UPDATE label=concat(label,'dfg');
例如现在表中有了这么两行数据
A,tyutyt
B,werwe
那么我希望表中第三列这样显示
A,tyutyt,t3y2u1
B,werwe, w2e2r1
也就是第三列是第二列对每个字母数量的统计,格式不限,只需要统计出每个字母的数量即可

  • 好问题 提建议
  • 收藏

4条回答 默认 最新

  • 广大菜鸟 2021-10-24 15:14
    已采纳

    可能是版本问题,前面的几位写的不错,但我的版本显示不了一些函数,我学习后也写了,希望对题主有帮助
    1、创建表格

    CREATE TABLE label_table (
        company varchar(200)PRIMARY KEY ,
        label varchar(255),
        label_num varchar(255)
    );
    

    2、定义存储过程

    delimiter $$
    drop procedure if exists  insert_update_table;
    create procedure insert_update_table(In in_company varchar(200), In in_label varchar(255)) 
    BEGIN
        declare selectedCOUNT int default 0;
        declare newLabel varchar(200) default '';
        declare oldLabel varchar(200);
        declare newLabelNum varchar(400);
        declare tmpStr varchar(200);
        declare newLabelCopy varchar(200);
        declare t int;    
        # 声明一个自定义的变量
        select count(*) into selectedCOUNT
        from label_table 
        where label_table.company=in_company;
        if (selectedCOUNT>0) then 
            select label into oldLabel
            from label_table 
            where label_table.company=in_company;
            set oldLabel =  IFNULL(oldLabel, '');
            set newLabel =  CONCAT(oldLabel , in_label);
        else 
            set newLabel =  in_label;
        end if;
        set newLabelNum = '';
        set newLabelCopy = newLabel;
        while length(newLabelCopy)>0 DO
            set tmpStr = left(newLabelCopy,1);
            set t = length(newLabelCopy)-length(replace(newLabelCopy,tmpStr,''));
            set newLabelNum =  CONCAT(newLabelNum , tmpStr , concat(t,''));
            set newLabelCopy = replace(newLabelCopy,tmpStr,'');     
        end while;
        if (selectedCOUNT>0) then 
            update label_table SET label=newLabel, label_num=newLabelNum  where company = in_company;
        else
            BEGIN
                insert into label_table(company,label,label_num) values(in_company,newLabel,newLabelNum);
            END;
        end if;
        COMMIT;
    END$$
    delimiter ;
    

    3. 测试,插入空字符串,相对应更新

    INSERT INTO label_table(company,label) VALUES('A', 'tyutyt');
    INSERT INTO label_table(company,label) VALUES('B', 'werwe');
    select * from label_table;
    call insert_update_table('a','');
    select * from label_table;
    call insert_update_table('b','');
    select * from label_table;
    

    img

    已采纳该答案
    评论
    解决 1 无用
    打赏 举报
  • 孙叫兽 2021-10-23 20:38

    这个不可以把第二列的数据拿到放到一个判断里然后放到第三列?

    img

    评论
    解决 无用
    打赏 举报
  • simtoba 2021-10-23 22:49

    create proc p1 @n1 varchar(200),@n2 varchar(255) AS
    DECLARE @t int,@i int,@str varchar(255),@s char(1),@str1 varchar(255),@s1 char(1)
    select @t=count(company) from test1 where company=@n1
    if @t>0
    update test1 set label=label+@n2 where company=@n1
    else
    insert into test1 (company,label) values(@n1,@n2)
    select @str=label from test1 where company=@n1
    set @i=0
    set @s=substring(@str,1,1)
    set @str1=''
    while @i<len(@str)+1
    begin
    set @i=@i+1
    if @i=1
    begin
    set @t=len(@str)-len(replace(@str,@s,''))
    set @str1=@str1 + @s + trim(str(@t))
    end
    if substring(@str,@i,1)<>@s and @i>1
    begin
    set @s1=substring(@str,@i,1)
    set @t=len(@str)-len(replace(@str,@s1,''))
    set @str=replace(@str,@s1,@s)
    set @str1=@str1 + @s1 + trim(str(@t))
    end
    end

      update test1 set label_num = @str1 where company=@n1   
    
    评论
    解决 无用
    打赏 举报
  • 大榕树51 2021-10-24 01:08

    DELIMITER //
    CREATE PROCEDURE count_lable (
    IN c_company VARCHAR ( 200 ),
    IN c_label VARCHAR ( 255 ),
    IN c_label_num VARCHAR ( 255 ),
    )
    BEGIN

    DECLARE idx int, jdex int, c_c VARCHAR (1), c_temp VARCHAR (255), temp_label VARCHAR (255);
    DECLARE select_label VARCHAR (255);
    DECLARE row_count INT;
    set select_label = ELECT label from label_table where company = c_company;
    set idx = 1
    set temp_label = CONCAT(select_label, c_label)
    while idx <= len(temp_label)
    begin
    set c_c = substring(temp_label, idx, 1)-- 取得idx所在的这个字符
    set jdex = length(temp_label)-length(replace(temp_label, c_c ,''))
    set c_temp =CONCAT(c_c, convert(varchar(20), jdex));
    set c_label_num = CONCAT(c_label_num, c_temp);
    set idx = idx + 1
    end

    SELECT count(*) INTO row_count from label_table where company = c_company;
    IF (row_count=0) THEN
    INSERT INTO label_table( company, label, label_num) VALUES(company, temp_label, c_label_num);
    ELSE
    UPDATE label_table SET SET label = temp_label, label_num = c_label_num WHERE company = c_company;
    END IF;

    COMMIT;
    END

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题