How can I write MySQL query that filters even and odd number from a a single column and return the r-CodePudding

Input Table

output table

------ ------ -
| even | odd |
 ------ ----- 
|    2 |  1  | 
|    4 |  3  |
|    6 |  5  |  
|    8 |  7  | 
|   10 |  9  |  
|   12 | 11  |  
 ------ -----

CodePudding user response：

On MySQL 8 , we can use ROW_NUMBER() here along with the modulus:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Numb % 2 ORDER BY Numb) rn
    FROM yourTable
)

SELECT
    MAX(CASE WHEN Numb % 2 = 0 THEN Numb END) AS even,
    MAX(CASE WHEN Numb % 2 = 1 THEN Numb END) AS odd
FROM cte
GROUP BY rn
ORDER BY 1;

CodePudding user response：

mysql 8 before:
with v_value as (select even,@rownum:=@rownum 1 as rownum from (select @rownum:=0) a,test b order by even)
select even,(select even from v_value where rownum=c.rownum-1)
  from v_value c
 where mod(even,2)=0;
mysql 8:
select * from (select even,lag(even) over(order by even asc) as odd from test order by even) a where mod(even,2)=0;