MySQLWorkbench-How to concatenate numbering of lists that are re-enumerated when changing the value of a field with values of a field of another table?

Navigation
3

Good morning, my question is somewhat complex. I have the following code (Very grateful McNets for the help with the formula). : 

select 
       @cid := case when @poblacion = poblacion then @cid + 1 else @cid := 1 end as cid,
       @poblacion := poblacion as p,
       nombre
from
    (select @cid := 1) i,
    (select @poblacion := poblacion as poblacion, nombre from Clientes order by poblacion) t

Which gives me back the following: 

+-----+-------------+--------+
| cid |  poblacion  | nombre |
+-----+-------------+--------+
|  1  | Los Angeles |  John  |
+-----+-------------+--------+
|  2  | Los Angeles |  Pepe  |
+-----+-------------+--------+
|  3  | Los Angeles | Serena |
+-----+-------------+--------+
|  1  |  San Diego  |  Lola  |
+-----+-------------+--------+
|  2  |  San Diego  |  Juan  |
+-----+-------------+--------+
|  3  |  San Diego  |  Maria |
+-----+-------------+--------+

And in another table called Location I have the following values: 

+-----+-------------+---------+
|  ID |   Ciudad    |Iniciales|
+-----+-------------+---------+
|  1  | Los Angeles |    LA   |
+-----+-------------+---------+
|  2  |  San Diego  |    SD   |
+-----+-------------+---------+
|  3  |  New York   |    NY   |
+-----+-------------+---------+

I seek to concatenate the initials of the Localization table with those of the Customers table.

Staying more or less like this: 

+------------+-------------+---------+
|    Cod     |   Ciudad    |  Nombre |
+------------+-------------+---------+
|  LA000001  | Los Angeles |  John   |
+-----+------+-------------+---------+
|  LA000002  | Los Angeles |  Pepe   |
+------------+-------------+---------+
|  LA000003  | Los Angeles | Serena  |
+------------+-------------+---------+
|  NY000001  |  New York   |  Lola   |
+------------+-------------+---------+
|  NY000002  |  New York   |  Juan   |
+------------+-------------+---------+
|  SD000001  |  San Diego  |  Maria  |
+------------+-------------+---------+

When making inner joins the formula does not work, with case when, if, and a million parameters I still can not merge the formula indicated above with the concatenation of the initials of the Table Location, the zeros that will disappear according to @ cid go increasing, thanks in advance!

    
asked by neojosh2 13.01.2017 в 19:24
source

2 answers

2

We add the table Locations with LEFT JOIN in case the names do not match, with LPAD we put 0 in front of cid, and we use CONCAT to join the initials with the value. 

select concat(iniciales, lpad(cid, 6, 0)) as Cod, p as Ciudad, nombre
from
    (select 
           @cid := case when @poblacion = poblacion then @cid + 1 else @cid := 1 end as cid,
           @poblacion := poblacion as p,
           t.nombre,
           t.iniciales
    from
        (select @cid := 1) i,
        (select @poblacion := tt.poblacion as poblacion, tt.nombre, loc.iniciales 
         from tt
             left join loc on tt.poblacion = loc.poblacion 
         order by tt.poblacion) t
     ) t1

+----------+-------------+--------+
|    Cod   | Ciudad      | nombre |
+----------+-------------+--------+
| LA000001 | Los Angeles | John   |
+----------+-------------+--------+
| LA000002 | Los Angeles | Pepe   |
+----------+-------------+--------+
| LA000003 | Los Angeles | Serena |
+----------+-------------+--------+
| SD000001 | San Diego   | Lola   |
+----------+-------------+--------+
| SD000002 | San Diego   | Juan   |
+----------+-------------+--------+
| SD000003 | San Diego   | Maria  |
+----------+-------------+--------+

Here you can verify it: link

    
answered by 13.01.2017 / 21:21
source
2

We must be very careful with queries that use the technique of variables, especially when they begin to be more complex.

In fact, MySQL does not offer many guarantees as to the order in which it processes the variables. And in the case of @McNets' response, although it is very interesting, it can cause problems without warning. Particularly because the consultation assumes many things:

  • Assume that the join will process the records in the order specified within the sub-query ( ORDER BY poblacion )
  • Assume that the result of the join will come out in the same order specified within the sub-query ( ORDER BY poblacion )
  • Assume that the variables in SELECT are processed in the written order.

But in reality, those things are not guaranteed, so the query is very fragile. Note what it says the MySQL documentation about it:

  

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement.

     

For other statements, such as SELECT, you might get the results, but this is not guaranteed.

     

However, the order of evaluation for expressions involving user variables is undefined.

Translation of mine:

  

As a general rule, unless it is within a SET statement, you should never assign a value to a variable and read it within the same statement.

     

For other statements, such as SELECT , you may get the expected results, but this is not guaranteed.

     

However, the order of evaluation of expressions containing variables is not defined.

Obviously, the ideal would have been to be able to use window functions as ROW_NUMBER() , but MySQL does not have them: (

I propose the following query, that although it does not offer the best performance, at least it guarantees stable results. Note that in order for it to work correctly, the clientes table needs to include some numeric column that serves as the primary key (in this case, I put one that is called id ). If you do not have one, it is worth adding one, since it is not good practice that your table does not have a primary key anyway: 

select concat(l.iniciales, 
              lpad((select count(*) + 1
                     from clientes c2
                    where c2.poblacion = c.poblacion
                      and c2.id > c.id),
                   6, '0')) as cid,
       l.ciudad,
       c.nombre
  from clientes c
  join localizacion l
    on l.ciudad = c.poblacion
 order by cid;

Result: 

|    Cod     |   Ciudad    |  Nombre |
---------------------------------------
|  LA000001  | Los Angeles |  Serena |
|  LA000002  | Los Angeles |  Pepe   |
|  LA000003  | Los Angeles |  John   |
|  NY000001  |  New York   |  Juan   |
|  NY000002  |  New York   |  Lola   |
|  SD000001  |  San Diego  |  Maria  |

Demo

Separate note: it would be advisable not to repeat the value ciudad in both tables. Rather, it would be better if the clientes table includes more a column localizacion_id or something like that, defined as foreign key to localizacion.id .

    
answered by 13.01.2017 в 21:44
Include HTML content without adding it in REACT classes Go through adjacent positions of a matrix