Batchnumber应该是相同数字的prind [英] Batchnumber should be prind on same number

问题描述

  CREATE   TABLE  practice_mysql.sample（id  int （ 11 ）默认  NULL ，名字 varchar （ 45 ） default   NULL ）ENGINE = InnoDB  DEFAULT  CHARSET = utf8; 
 
  insert   into  sample（id，Firstname） values （'  1'，'  Anish'）; 
 插入 进入示例（id，名字）值（'  2'，'  Anish'）; 
 插入 进入示例（id，名字）值（'  3'，'  Uma'）; 
 插入 进入示例（id，名字）值（'  4'，'  Anish'）; 
 插入 进入示例（id，名字）值（'  5'，'  Uma'）; 
 插入 进入示例（id，名字）值（'  6'，'  temp'）; 

**



>我尝试这个查询获取名字前3个字母和我hav

>用于生成自动增量



**

  SET   @ ndx  =  0 ; 
  SET   @ cur_hash  = ' '; 
  SELECT  id，名字，CONCAT（ left （名字， 3 ）， IF （ndx> = 0，CONCAT（'   -  00'，ndx），' ' ））BatchNumber 
  FROM （ SELECT  id，
  @ new_hash ：= MD5（名字），
（ @ ndx ：=  IF （@ new_hash = @ cur_hash，@ ndx + 1， 1 ））ndx，
  @cur_hash ：= MD5（名字），
名字
  FROM （ SELECT  id，名字 FROM 示例 ORDER   BY 名字）AA）订单 按 id; 

i尝试了这段代码，但输出错误。这里有什么错误？

名字| Batchnumber 
 --------------------- 
 
 Anish | Ani001 
 Anish | Ani001 
 Uma | Uma001 
 Anish | Ani001 
 Uma | Uma001 
 temp | tem001 
 

但我需要这种类型的输出：

名字| Batchnumber 
 --------------------- 
 
 Anish | Ani001 
 Anish | Ani002 
 Uma | Uma001 
 Anish | Ani003 
 Uma | Uma002 
 temp | tem001 
 

当多次插入名称时，我增加了Batchnumber 。但它不起作用。

我会试着自己找出它，但它对我来说是凝灰岩。任何人都可以解决它吗？



我尝试过：



< pre lang =SQL> SET @ ndx = 0 ;
SET @ cur_hash = ' ';
SELECT id，名字，CONCAT（ left （名字， 3 ）， IF （ndx> = 0，CONCAT（' - 00'，ndx），' ' ））BatchNumber
FROM （ SELECT id，
@ new_hash ：= MD5（名字），
（ @ ndx ：= IF （@ new_hash = @ cur_hash，@ ndx + 1， 1 ））ndx，
@cur_hash ：= MD5（名字），
名字
FROM （ SELECT id，名字 FROM 示例 ORDER BY 名字）AA）订单 按 id;

这里的值不会增加它的打印数量相同我应该错误我做这个

解决方案

错误报告：

您的代码不处理2个不同名称具有相同前3个字母的情况。

  insert   into  sample（id，Firstname） values （'  7'，' < span class =code-string> Anish A.'）; 
 插入 进入示例（id，名字）值（'  8'，'  Anish B。'）; 

  SELECT  id，
  @ new_hash ：= MD5（名字），
（ @ ndx ：=  IF （@ new_hash = @ cur_hash，@ ndx + 1， 1 ））ndx，
  @ cur_hash ：= MD5（名字），
名字
  FROM （ SELECT  id，名字 FROM  sample  ORDER   BY 名字）AA）A  order   by  id; 

你需要设置 @new_hash 基于左（名字，3）而不是名字

CREATE TABLE  practice_mysql.sample (  id int(11) default NULL,  Firstname varchar(45) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into sample (id,Firstname) values ('1','Anish');
insert into sample (id,Firstname) values ('2','Anish');
insert into sample (id,Firstname) values ('3','Uma');
insert into sample (id,Firstname) values ('4','Anish');
insert into sample (id,Firstname) values ('5','Uma');
insert into sample (id,Firstname) values ('6','temp');

**

> and i Try this query for firstname taken first 3 letter and the i hav
> using to generate autoincrement

**

SET @ndx = 0;
SET @cur_hash = '';
SELECT id,Firstname ,CONCAT(left(Firstname ,3),IF(ndx>=0,CONCAT('--00',ndx),'')) BatchNumber
FROM (SELECT id,
   @new_hash := MD5(Firstname ),
   (@ndx := IF(@new_hash=@cur_hash,@ndx+1,1)) ndx,
   @cur_hash := MD5(Firstname ),
   Firstname
FROM (SELECT id,Firstname FROM sample ORDER BY Firstname ) AA) A order by id;

i tried this code but the output is wrong. What is the mistake here?

Firstname | Batchnumber
---------------------

Anish     |  Ani001
Anish     |  Ani001
Uma       |  Uma001
Anish     |  Ani001
Uma       |  Uma001
temp      |  tem001

But i need this type of output:

Firstname | Batchnumber
   ---------------------

   Anish     |  Ani001
   Anish     |  Ani002
   Uma       |  Uma001
   Anish     |  Ani003
   Uma       |  Uma002
   temp      |  tem001

When the Name is inserted more than once, i have incremented the Batchnumber. But its not working.
i will try to find it out myself but its tuff for me. can anybody solve it?

What I have tried:

SET @ndx = 0;
SET @cur_hash = '';
SELECT id,Firstname ,CONCAT(left(Firstname ,3),IF(ndx>=0,CONCAT('--00',ndx),'')) BatchNumber
FROM (SELECT id,
   @new_hash := MD5(Firstname ),
   (@ndx := IF(@new_hash=@cur_hash,@ndx+1,1)) ndx,
   @cur_hash := MD5(Firstname ),
   Firstname
FROM (SELECT id,Firstname FROM sample ORDER BY Firstname ) AA) A order by id;

here the value is not increment its print on same number what i should error i do this

解决方案

Bug report:
Your code do not handle the case where 2 different name have the same first 3 letters.

insert into sample (id,Firstname) values ('7','Anish A.');
insert into sample (id,Firstname) values ('8','Anish B.');

SELECT id,
   @new_hash := MD5(Firstname ),
   (@ndx := IF(@new_hash=@cur_hash,@ndx+1,1)) ndx,
   @cur_hash := MD5(Firstname ),
   Firstname
FROM (SELECT id,Firstname FROM sample ORDER BY Firstname ) AA) A order by id;

You need to set @new_hash based on left(Firstname ,3) instead of Firstname

这篇关于Batchnumber应该是相同数字的prind的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！