在实际工作中,开发可能会碰到数据需要进行 行转列 的查询,第一个想到的就是用Oracle的内置函数PIVOT,但PL可能会说,这种查询的性能可能会不太好,项目上要求不使用这个函数,那么有什么方法实现这种查询呢?
方案:使用同一张表的row_id来进行关联查询,因为Oracle中表数据的row_id是唯一的。
直接举例,供参考查阅:
使用PIVOT函数的SQL:
SELECT * FROM (
SELECT CODE, VOICE, POST
FROM (SELECT B.PARAM_CODE, C.VALUE, C.ROW_ID
FROM SYS_A A
JOIN SYS_B B ON A.ID = B.A_ID
JOIN SYS_C C ON C.B_ID = B.ID
WHERE A.GROUP_CODE = 'TEST_CODE'
AND A.STATE = 'A'
AND B.STATE = 'A'
AND C.STATE = 'A'
AND A.ORG_ID = 123
AND B.ORG_ID = 123
AND C.ORG_ID = 123) T
PIVOT(MAX(T.VALUE)
FOR PARAM_CODE IN('CODE' AS CODE, 'VOICE' AS VOICE, 'POST' AS POST))
) T WHERE CODE='AAAAAA'
使用row_id关联查询来替换PIVOT函数的SQL:
SELECT B.VALUE,A.B_ID,B.B_ID
FROM SYS_C A
INNER JOIN SYS_C B ON B.ROW_ID = A.ROW_ID
WHERE A.STATE='A' AND B.STATE='A'
AND A.ORG_ID=123
AND A.B_ID IN (SELECT PCD.ID
FROM SYS_A PC
INNER JOIN SYS_B PCD ON PC.ID=PCD.A_ID
WHERE PC.STATE='A' AND PCD.STATE='A'
AND PC.ORG_ID=123
AND PC.GROUP_CODE = 'TEST_CODE'
AND PCD.PARAM_CODE = 'CODE')
AND B.B_ID IN (SELECT PCD.ID
FROM SYS_A PC
INNER JOIN SYS_B PCD ON PC.ID=PCD.A_ID
WHERE PC.STATE='A' AND PCD.STATE='A'
AND PC.ORG_ID=123
AND PC.GROUP_CODE = 'TEST_CODE'
AND PCD.PARAM_CODE = 'VOICE')
AND A.VALUE = 'AAAAAA'