问题描述:

Hi this is the sample query i was trying to execute in MySQL. It give out an error when am using the concat function

 SELECT @grp :=San_Orange_CS_GRP as santoor_grp ,

@_11:=power(@grp,0.1)+0.1*@_11 AS concat "@grp" , "_11" ,

@_12:=power(@grp,0.2)+0.1*@_12 AS concat "@grp" , "_12"

FROM DUMMY JOIN (SELECT @_11:=0,@_12:=0) t;

When i alter the code and give the column name everytime in the query after AS statement, it works fine. But the problem is i have around 100 columns so i cannot keep on writting the names each and every time i run different market grp

 SELECT @grp :=Orange_CS_GRP as Orange_CS_grp ,

@_11:=power(@grp,0.1)+0.1*@_11 Orange_CS_GRP_11 ,

@_12:=power(@grp,0.2)+0.1*@_12 AS Orange_CS_GRP_12

from DUMMY JOIN (SELECT @_11:=0,@_12:=0) t;

Thanks in advance :-)

网友答案:

This is the exact query with concat:

SELECT @grp :=San_Orange_CS_GRP as santoor_grp , @_11:=power(@grp,0.1)+0.1*@_11 AS concat(@grp , "_11") , @_12:=power(@grp,0.2)+0.1*@_12 AS concat (@grp , "_12") FROM DUMMY JOIN (SELECT @_11:=0,@_12:=0) t;

use of concat is incorrect in your query..

Thanks

网友答案:

Alias names are identifiers and can't be assigned names from values of a column dynamically.

To do so you can use a stored procedure.

Example:

drop procedure if exists sp_so_q23692874;

delimiter //

create procedure sp_so_q23692874()
begin
  -- read my comment below this SP.
  SELECT San_Orange_CS_GRP INTO @grp FROM DUMMY LIMIT 1;
  set @grp_name := 'Orange_CS_GRP';

  set @sql := concat( 'SELECT San_Orange_CS_GRP as santoor_grp ' );
  set @sql := concat( @sql, ', @_11 := power( ', @grp, ', 0.1 ) + 0.1 * @_11 ' );
  set @sql := concat( @sql, 'AS `', @grp_name, '_11` ' );
  set @sql := concat( @sql, ', @_12 := power( ', @grp, ', 0.2 ) + 0.1 * @_12 ' );
  set @sql := concat( @sql, 'AS `', @grp_name, '_12` ' ); 
  set @sql := concat( @sql, ' FROM DUMMY JOIN ( SELECT @_11 := 0, @_12 := 0 ) t' );

  prepare stmt from @sql;
  execute stmt;
  drop prepare stmt;
end;
//

delimiter ;

call sp_so_q23692874();
-- select @sql;

I am not exactly sure how you are going to use this, because have a dynamic alias name on an expression name may be good if result set has only one row. You can't give a new name for an expression on each row and that makes no sense too. And that is the reason why I put a LIMIT clause on grp result set.

相关阅读:
Top