日期:2014-05-16  浏览次数:20787 次

mysql 查询行列转换
题目:
查询出如下的结果


A 表 


B 表



查询sql:

SELECT DISTINCT A.A_ID,A.A_NAME,T.NAME ,T1.TRUE,T2.FALSE
FROM
A,
B,
(SELECT B.A_ID AID,COUNT(B.A_ID) AS NAME FROM A ,B WHERE A.A_ID = B.A_ID GROUP BY  B.A_ID) T,
(SELECT B.A_ID T1ID, COUNT(B.B_TYPE) AS "TRUE" FROM A,B WHERE A.A_ID = B.A_ID AND B.B_TYPE = 'True'GROUP BY B.A_ID) T1,
(SELECT B.A_ID T2ID, COUNT(B.B_TYPE) AS "FALSE" FROM A,B WHERE A.A_ID = B.A_ID AND B.B_TYPE = 'False'GROUP BY B.A_ID) T2
WHERE A.A_ID = T.AID
AND A.A_ID = T1.T1ID
AND A.A_ID = T2.T2ID