Comparing MySQL and PostgreSQL Counter Queries

1. Comparison

select (case 
        when `type` = 1 and @counter1 = 50001 then 50001 
        when `type` = 1 then @counter1 := @counter1 + 1 
        when `type` = 2 and @counter2 = 50001 then 50001 
        when `type` = 2 then @counter2 := @counter2 + 1 
        when `type` = 4 and @counter3 = 50001 then 50001 
        when `type` = 4 then @counter3 := @counter4 + 1 
        when `type` = 8 and @counter8 = 50001 then 50001 
        when `type` = 8 then @counter8 := @counter8 + 1 
    end) as counter,log_action.`type`
from 
    (select @counter1 := 0 ) initCounter1,
    (select @counter2 := 0 ) initCounter2,
    (select @counter4 := 0 ) initCounter4,
    (select @counter8 := 0 ) initCounter8,
    (select `type` from pla) log_action

and

select 
    row_number() over(partition as type) as counter,"type" from 
    (select "type" from table_1 )

2. Explanation

The first, longer query is the MySQL version, while the second, shorter query is the PostgreSQL version.

Author: He Weiping, Qunar Travel Business Unit, search technology researcher and database researcher, translator of the first Chinese PostgreSQL manual and Programming Perl 3rd edition, with nearly 18 years of IT experience.

This article is reproduced from the company wiki “老何的1001夜”. Qunar colleagues can log in to the wiki to view the full article.