SELECT sum(if(aid=1,1,0)) as a, sum(if(bid=1,1,0)) as b,sum(if(cid=1,1,0)) as c from tta union all SELECT sum(if(aid=2,1,0)) as a, sum(if(bid=2,1,0)) as b,sum(if(cid=2,1,0)) as c from tta union all SELECT sum(if(aid=3,1,0)) as a, sum(if(bid=3,1,0)) as b,sum(if(cid=3,1,0)) as c from tta
SELECT c.ID,IF(c.A=0,NULL,c.A) A,IF(c.B=0,NULL,c.B) B,IF(c.C=0,NULL,c.C) C FROM (SELECT a.ID, (SELECT COUNT(*) FROM t WHERE aid=ID) A, (SELECT COUNT(*) FROM t WHERE bid=ID) B, (SELECT COUNT(*) FROM t WHERE cid=ID) C FROM (SELECT aid ID FROM t UNION SELECT bid FROM t UNION SELECT cid FROM t) a WHERE NOT ID IS NULL) c
select v, sum(if(k='A',1,0)) as A, sum(if(k='B',1,0)) as B, sum(if(k='C',1,0)) as C from ( select 'A' as k, aid as v from 一个表 union all select 'B' as k, bid as v from 一个表 union all select 'C' as k, cid as v from 一个表 ) t group by v
union all
SELECT sum(if(aid=2,1,0)) as a, sum(if(bid=2,1,0)) as b,sum(if(cid=2,1,0)) as c from tta
union all
SELECT sum(if(aid=3,1,0)) as a, sum(if(bid=3,1,0)) as b,sum(if(cid=3,1,0)) as c from tta
(SELECT a.ID,
(SELECT COUNT(*) FROM t WHERE aid=ID) A,
(SELECT COUNT(*) FROM t WHERE bid=ID) B,
(SELECT COUNT(*) FROM t WHERE cid=ID) C
FROM
(SELECT aid ID FROM t
UNION
SELECT bid FROM t
UNION
SELECT cid FROM t) a WHERE NOT ID IS NULL) c
select v,
sum(if(k='A',1,0)) as A,
sum(if(k='B',1,0)) as B,
sum(if(k='C',1,0)) as C
from (
select 'A' as k, aid as v from 一个表
union all
select 'B' as k, bid as v from 一个表
union all
select 'C' as k, cid as v from 一个表
) t
group by v
http://blog.csdn.net/acmain_chm/article/details/4283943