1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| WITH MyTable AS
(
select 1 as uid, 'titi' as nom, 0 as parrain union all
select 2, 'toto', 1 union all
select 5, 'xxx', 2 union all
select 6, 'tttt', 2 union all
select 7, 'eee', 1 union all
select 8, 'zzz', 1 union all
select 9, 'yyy', 8
),
MyParrain AS
(
SELECT parrain, count(parrain) as total
FROM MyTable
GROUP BY parrain
)
select
MyTable.uid,
coalesce(MyParrain.total, 0) as nbfilleuils
from
MyTable
left outer join MyParrain
ON MyParrain.parrain = MyTable.uid
order by uid asc |
Partager