FROM `chapter` JOIN `manga` ON `chapter`.`manga_id` = `manga`.`id`with recursive cte (main_username, id, id_referer, lvl) as (
select username,
id,
id_referer,
0 lvl
from tb_users
where id = 1
union all
select main_username,
p.id,
p.id_referer,
lvl + 1
from tb_users p
inner join cte
on p.id_referer = cte.id
where lvl <= 3
)
select main_username,
count(case lvl when 1 then 1 end) cnt_lvl1,
count(case lvl when 2 then 1 end) cnt_lvl2,
count(case lvl when 3 then 1 end) cnt_lvl3
from cte
group by main_usernameselect *
from (SELECT @row_num := CASE WHEN @row_num_val = a.id THEN @row_num+1
WHEN (@row_num_val := a.id) IS NOT NULL THEN 1
END pair_flag, a.*, b.*
from (SELECT @row_num := null, @row_num_val := null) AS x,
table1 a
left join table2 b on (a.id = b.tab1_id)) t1
where pair_flag <= 2with table1 as(
select 1 id, 'table1_1' text1 union all
select 2,'table1_2' union all
select 3,'table1_3' union all
select 4,'table1_4' union all
select 5,'table1_5'
)
,table2 as (
select 1 id2, 1 tab1_id,'table2_1' text2 union all
select 2, 1 tab1_id,'table2_2' union all
select 3, 1 tab1_id,'table2_3' union all
select 4, 2 tab1_id,'table2_4' union all
select 5, 2 tab1_id,'table2_5' union all
select 6, 2 tab1_id,'table2_6' union all
select 7, 3 tab1_id,'table2_7' union all
select 8, 3 tab1_id,'table2_8' union all
select 9, 3 tab1_id,'table2_9' )
SELECT *
from table1 a
left join table2 b on (a.id = b.tab1_id)SELECT *
from table1 a
left join table2 b on (a.id = b.tab1_id)
where b.tab1_id is not nullSELECT *
from table1 a
inner join table2 b on (a.id = b.tab1_id)