（Oracle）如何在具有水平和垂直坐标的表中选择数据？

2024-05-21 • 问答

我的目标是使函数能够在Tableau中选择具有垂直和水平坐标的数据。如何使我的身体机能？该函数应根据函数参数I_HORIZENT动态选择列，我不知道该如何处理。

例如，如果垂直坐标为“ E1”而水平坐标为“ RP” 然后该函数应返回“ 12”。垂直坐标是一列K1。水平坐标是一行，列NUM = 0。

  NUM  K1    K2  K3  K4 
  --- ---  ---- ---- ----
  0          RK  RP   RN
  1   E1     10  12   11 
  2   E2     21  23   19

表和我的功能如下

CREATE TABLE DATAA ( 
NUM VARCHAR2(15),K1 VARCHAR2(15),K2 VARCHAR2(15),K3 VARCHAR2(15),K4 VARCHAR2(15)
);

INSERT INTO DATAA VALUES('0',NULL,'RK','RP','RN');
INSERT INTO DATAA VALUES('1','E1','10','12','11');
INSERT INTO DATAA VALUES('2','E2','21','23','19');

SELECT * FROM DATAA;



CREATE OR REPLACE FUNCTION MYFUN
(
I_VERTICAL IN VARCHAR2.
I_HORIZENT IN VARCHAR2
)RETURN VARCHAR2 AS V_VALUE VARCHAR2;
BEGIN
---



?  --How to make my function body?



---
RETURN V_VALUE;
END;

您不需要为此使用动态SQL。您可以使用UNPIVOT：

WITH unpivoted ( num,col_name,col_index,value ) AS (
  SELECT *
  FROM   DATAA
  UNPIVOT ( value FOR col_index IN ( K2 AS 2,K3 AS 3,K4 AS 4 ) )
)
SELECT r.value
FROM   ( SELECT * FROM unpivoted WHERE value = 'RP' AND num = '0' ) c
       INNER JOIN
       ( SELECT * FROM unpivoted WHERE col_name = 'E1' ) r
       ON ( r.col_index = c.col_index )

输出：

| VALUE |
| :---- |
| 12    |

db 提琴here

如果要将其包装到函数中，则：

CREATE OR REPLACE FUNCTION MYFUN
(
  I_VERTICAL   IN VARCHAR2,I_HORIZONTAL IN VARCHAR2
) RETURN VARCHAR2
AS
  V_VALUE VARCHAR2(15);
BEGIN
  WITH unpivoted ( num,value ) AS (
    SELECT *
    FROM   DATAA
    UNPIVOT ( value FOR col_index IN ( K2 AS 2,K4 AS 4 ) )
  )
  SELECT r.value
  INTO V_VALUE
  FROM   ( SELECT * FROM unpivoted WHERE value = I_HORIZONTAL AND num = '0' ) c
         INNER JOIN
         ( SELECT * FROM unpivoted WHERE col_name = I_VERTICAL ) r
         ON ( r.col_index = c.col_index );
  RETURN V_VALUE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

和

SELECT MYFUN( 'E1','RP' ) FROM DUAL;

输出：

| MYFUN('E1','RP') |
| :--------------- |
| 12               |

db 提琴here

（Oracle）如何在具有水平和垂直坐标的表中选择数据？

huilovenai 回答：（Oracle）如何在具有水平和垂直坐标的表中选择数据？

大家都在问