row group + auto number

USE test;

DROP TABLE IF EXISTS makanan;
CREATE TABLE IF NOT EXISTS makanan(
id_mkn INT,
nama VARCHAR(50),
PRIMARY KEY(id_mkn)
);

INSERT INTO makanan
VALUES
(1,'Ayam'),
(2,'Bebek'),
(3,'Kambing'),
(4,'Kelinci'),
(5,'Kuda');

SELECT * FROM makanan;

+--------+---------+
| id_mkn | nama    |
+--------+---------+
|      1 | Ayam    |
|      2 | Bebek   |
|      3 | Kambing |
|      4 | Kelinci |
|      5 | Kuda    |
+--------+---------+
5 rows in set (0.02 sec)

DROP TABLE IF EXISTS jenis;
CREATE TABLE IF NOT EXISTS  jenis(
id_jenis INT,
id_mkn INT,
jenis VARCHAR(50),
PRIMARY KEY(id_jenis)
);

INSERT INTO jenis
VALUES
(1,1,'Goreng'),
(2,2,'Goreng'),
(3,3,'Bakar sate'),
(4,3,'Cincang'),
(5,4,'Sate'),
(6,5,'Sate'),
(7,1,'Bakar'),
(8,1,'Rebus');

SELECT * FROM jenis;
+----------+--------+------------+
| id_jenis | id_mkn | jenis      |
+----------+--------+------------+
|        1 |      1 | Goreng     |
|        2 |      2 | Goreng     |
|        3 |      3 | Bakar sate |
|        4 |      3 | Cincang    |
|        5 |      4 | Sate       |
|        6 |      5 | Sate       |
|        7 |      1 | Bakar      |
|        8 |      1 | Rebus      |
+----------+--------+------------+
8 rows in set (0.00 sec)

-- don-t do it at home, it-s very..very dangerous :)

SET @iprev=0, @jprev=0;
SELECT makanan,no,jenis FROM
(
SELECT
IF(c.id_jenis IS NULL,'',b.nama) AS makanan,
a.jenis,
@jprev:=if(@iprev=a.id_mkn,@jprev+1,1) AS no,
@iprev:=a.id_mkn AS id_mkn,a.id_jenis
FROM jenis a
JOIN makanan b USING(id_mkn)
LEFT JOIN
(
SELECT MIN(id_jenis) AS id_jenis
FROM jenis
GROUP BY id_mkn
) c USING(id_jenis)
ORDER BY b.id_mkn,a.id_jenis
) AS tmp;

+---------+------+------------+
| makanan | no   | jenis      |
+---------+------+------------+
| Ayam    |    1 | Goreng     |
|         |    2 | Bakar      |
|         |    3 | Rebus      |
| Bebek   |    1 | Goreng     |
| Kambing |    1 | Bakar sate |
|         |    2 | Cincang    |
| Kelinci |    1 | Sate       |
| Kuda    |    1 | Sate       |
+---------+------+------------+
8 rows in set (0.00 sec)