mysql 查询行列转换

最新推荐文章于 2024-06-14 14:02:57 发布

gslsa

最新推荐文章于 2024-06-14 14:02:57 发布

阅读量188

点赞数

分类专栏： mysql 文章标签： MySQL SQL

本文链接：https://blog.csdn.net/c170331773/article/details/83773135

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

题目：
查询出如下的结果
[img]http://dl.iteye.com/upload/attachment/358894/b51cb433-3904-3e73-99f3-e41f82527f23.jpg[/img]

A 表
[img]http://dl.iteye.com/upload/attachment/358890/57b4bffd-184b-3a7e-b723-2aaeda97f4ef.jpg[/img]

B 表
[img]http://dl.iteye.com/upload/attachment/358892/1ffd8433-af80-328f-abaf-7fde6e5a690c.jpg[/img]

查询sql:
[color=blue][size=medium][b]
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[/b][/size][/color]