I want to add an ID column to increment to the result of UNION ALL in MySQL

Question: Question:

■ Current SQL

SELECT Y.id, Y.hoge, Y.piyo
FROM (
SELECT A.id, A.hoge, A.piyo
FROM A
UNION ALL 
SELECT B.a_id, "", B.piyo
FROM B
)Y

■ Current status acquisition results

id　hoge　piyo

■ want results

increment_id　id　hoge　piyo

■ What you want to do

• I want to add a new increment_id column to the result when UNION ALL is done.
• increment_id column… +1 in record order

■ Background

• I want to use it when I do GROUP_CONCAT later

Answer: Answer:

There was a similar answer on stackoverflow.com.

SELECT
  @id:=@id+1 increment_id,
  Y.id,
  Y.hoge,
  Y.piyo
FROM 
  (select @id:=0) as id,
  (
    SELECT A.id, A.hoge, A.piyo FROM A
    UNION ALL
    SELECT B.a_id, "", B.piyo FROM B
  ) Y;

( Execution result )