mysql - 在sql中统计年龄并查看sql表中的数据

Question

 |---------------------|
 |DUN|NAME   |BIRTHDAY |
  --------------------
 |A  |MELAYU |21/2/2014|
 |A  |CINA   |21/2/2002|
 |B  |CINA   |21/2/2011|
 |-------------------- |

我的表名称是 maklumat。我想使用sql查看基于年龄的数据。下面是要查看的示例表。

 |---------------------|
 |DUN|AGE<3  |AGE 3-6  |
  --------------------
 |A  |1      |1        |
 |B  |0      |1        |
 |-------------------- |

这就是我尝试过的

SELECT jantina,dun, count(tarikh_lahir > 1954-04-07) as 60KEBAWAH, count(1950-04-07>=tarikh_lahir <= 1954-04-07) as 60HINGGA64, count(1945-04-07>=tarikh_lahir <= 1949-04-07) as 65HINGGA69, count(1940-04-07>=tarikh_lahir <= 1944-04-07) as 70HINGGA74,count(1935-04-07>=tarikh_lahir <= 1939-04-07) as 75HINGGA79,count(1930-04-07>=tarikh_lahir <= 1934-04-07) as 80HINGGA85, count(1925-04-07>=tarikh_lahir <= 1929-04-07) as 86HINGGA89, count(1920-04-07>=tarikh_lahir <= 1924-04-07) as 90HINGGA94, count(1915-04-07>=tarikh_lahir <= 1919-04-07) as 95HINGGA99, count(1910-04-07>=tarikh_lahir <= 1914-04-07) as 100HINGGA104,count(tarikh_lahir <1910-04-07) as 60KEBAWAH
FROM maklumat_ahli
WHERE jantina = 'lelaki' AND (
kematian_tarikh IS NULL
AND bayaran_pertama IS NULL
AND bayaran_kedua IS NULL
)
GROUP BY dun

Answer 1

SELECT M.DUN, COUNT(M.AGE) AS 'AGE<3', COUNT(M1.AGE) AS 'AGE 3-6' FROM MAKLUMAT M
LEFT JOIN MAKLUMAT M1 ON M.DUN = M1.DUN AND M1.AGE BETWEEN 3 AND 6
WHERE M.AGE < 3
GROUP BY M.DUN