SELECT test1.id, test1.classname, count( t1.id )
FROM `test1` AS t1
LEFT JOIN test1 ON test1.id = t1.parent
WHERE test1.classname IS NOT NULL
GROUP BY t1.parent没有输出0的这种情况.
FROM `test1` AS t1
LEFT JOIN test1 ON test1.id = t1.parent
WHERE test1.classname IS NOT NULL
GROUP BY t1.parent没有输出0的这种情况.
LEFT JOIN
(
SELECT parent,classname,count(*) AS count FROM 表一
GROUP BY parent WHERE parent>0
) AS B ON B.parent=A.id
WHERE A.parent=0
SELECT A.id AS classid, A.classname, IFNULL( B.count, 0 ) AS count
FROM test1 AS A
LEFT JOIN (
SELECT parent, count( * ) AS count
FROM test1
WHERE parent >0
GROUP BY parent
) AS B ON B.parent = A.id
WHERE A.parent =0受楼上的启发,呵呵.