在实际工作中,开发可能会碰到数据需要进行 行转列 的查询,第一个想到的就是用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'