With Temp as (
Select population, ' Nation ', ' Guangzhou ' City and dual UNION all
Select 1500 population, ' nation ', ' Shanghai ' City and dual UNION all
Select population, ' Nation ', ' Beijing ' City and dual UNION all
Select 1000 population, ' USA ' Nation, ' New York ' city and dual UNION all
Select population, ' USA ' Nation, ' Bostom ' City and dual UNION all
Select population, ' Japan ' Nation, ' Tokyo ' city from dual
)
Select
Rank (+, ' Bf ') within GROUP (ORDER by population,city Desc) rank
from temp;
Description: According to 500 query, according to the Bf fuzzy query
With Temp as (
Select ' Nation ', ' Guangzhou ' City from dual union all
Select ' Nation ', ' Shanghai ' City from dual union all
Select ' Nation ', ' Beijing ' City from dual union all
Select ' USA ' Nation, ' New York ' city-dual UNION ALL
Select ' USA ' Nation, ' Bostom ' city-dual UNION ALL
Select ' Japan ' Nation, ' Tokyo ' city from dual
)
Select Nation,listagg (City, ', ') within GROUP (order by city DESC)
From temp
Group by Nation;
Description: Similar to the WM_CONCAT function, the data for row-level statistics.
With Temp as (
Select population, ' Nation ', ' Guangzhou ' City and dual UNION all
Select 1500 population, ' nation ', ' Shanghai ' City and dual UNION all
Select population, ' Nation ', ' Beijing ' City and dual UNION all
Select 1000 population, ' USA ' Nation, ' New York ' city and dual UNION all
Select population, ' USA ' Nation, ' Bostom ' City and dual UNION all
Select population, ' Japan ' Nation, ' Tokyo ' city from dual
)
Select population,
Nation
Listagg (city, ', ') within GROUP (order by city) over (partition by nation) rank
from temp;
Note: Use the over function to data row-level statistics, display the data of each row