Est ce que vous connaissez sous MySQL une manière d'obtenir se résultat, avec une fonction propre ou avec une magouille!

When the data has multiple partitions

If the data has multiple partitions, the solution is more complex. Here is one possible solution:

create table fruits (
type varchar(10) not null,
variety varchar(20) not null,
primary key(type, variety));

insert into fruits values
('apple', 'gala'),
('apple', 'fuji'),
('apple', 'limbertwig'),
('orange', 'valencia'),
('orange', 'navel'),
('pear', 'bradford'),
('pear', 'bartlett'),
('cherry', 'bing'),
('cherry', 'chelan');

select l.type, l.variety, count(*) as num
from fruits as l
left outer join fruits as r
on l.type = r.type
and l.variety >= r.variety
group by l.type, l.variety;

+--------+------------+-----+
| type | variety | num |
+--------+------------+-----+
| apple | fuji | 1 |
| apple | gala | 2 |
| apple | limbertwig | 3 |
| cherry | bing | 1 |
| cherry | chelan | 2 |
| orange | navel | 1 |
| orange | valencia | 2 |
| pear | bartlett | 1 |
| pear | bradford | 2 |
+--------+------------+-----+

Here I’ve partitioned the data by the type column, so there are four partitions (apple, cherry, orange, pear). Notice the num column starts at 1 for each partition and counts upward.

The drawback to this solution is the LEFT OUTER JOIN with the >= in the join condition. This effectively makes the join a CROSS JOIN, which is inefficient (O(n2)). It may be a good idea to avoid this except on small data sets.