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

创建了一张表，如下
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
也就是第三列是第二列对每个字母数量的统计，格式不限，只需要统计出每个字母的数量即可

可能是版本问题，前面的几位写的不错，但我的版本显示不了一些函数，我学习后也写了，希望对题主有帮助
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

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

img

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

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