oracle - Oracle 动态连接(或移动行)
问题描述
我正在运行Oracle 11g,需要将某些行分成列,但列数未确定,因此我需要动态执行。
最初我想分成多个查询并在以后加入它们,但这似乎不是最合适的,而且我在使其动态化时遇到了麻烦。
这是我的表的简化示例:
CREATE TABLE foo (
id NUMBER,
cod NUMBER,
val NUMBER,
dat DATE
);
INSERT INTO foo VALUES(1, 35, 58.10, TO_DATE('01-07-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(2, 45, 38.50, TO_DATE('01-07-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(3, 45, 3.89, TO_DATE('20-07-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(4, 35, 102.0, TO_DATE('01-07-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(5, 75, 69.32, TO_DATE('01-07-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(6, 75, 74.65, TO_DATE('01-07-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(7, 45, 32.8, TO_DATE('01-07-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(8, 75, 12.76, TO_DATE('01-07-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(1, 35, 38.50, TO_DATE('01-08-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(2, 45, 3.89, TO_DATE('01-08-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(3, 45, 102.0, TO_DATE('01-08-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(4, 35, 69.32, TO_DATE('01-08-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(5, 75, 74.65, TO_DATE('01-08-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(6, 75, 32.8, TO_DATE('01-08-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(7, 45, 38.50, TO_DATE('30-08-2019', 'DD-MM-YYYY'));
INSERT INTO foo VALUES(8, 75, 3.89, TO_DATE('01-08-2019', 'DD-MM-YYYY'));
SELECT a.cod, a.val, b.cod, b.val
FROM foo a, (
SELECT id, cod, val
FROM foo
WHERE dat = TO_DATE('01-07-2019', 'DD-MM-YYYY')
) b
WHERE a.id = b.id AND
a.dat = TO_DATE('01-08-2019', 'DD-MM-YYYY');
输出(按 COD 排序):
COD|VAL |COD|VAL |
---|-----|---|-----|
35| 38.5| 35| 58.1|
35|69.32| 35| 102|
45| 3.89| 45| 38.5|
75|74.65| 75|69.32|
75| 32.8| 75|74.65|
75| 3.89| 75|12.76|
我需要这些行只有一个不同的 COD,其他行将排列在新列中。
预期的:
COD 35 | COD 45 | COD 75 | <- illustrative
-------------------|-------------------|-------------------|
COD|VAL |COD|VAL |COD|VAL |COD|VAL |COD|VAL |COD|VAL |
---|-----|---|-----|---|-----|---|-----|---|-----|---|-----|
35| 38.5| 35| 58.1| 45| 3.89| 45| 38.5| 75|74.65| 75|69.32|
35|69.32| 35| 102| | | | | 75| 32.8| 75|74.65|
| | | | | | | | 75| 3.89| 75|12.76|
谢谢
解决方案
我解决了以下问题:
DECLARE
p_cod_str VARCHAR2(100) := '35, 75, 45';
i_prv VARCHAR2(10);
q_loop VARCHAR2(1000);
q_select VARCHAR2(1000);
q_where VARCHAR2(1000);
q_from VARCHAR2(5000);
q_query VARCHAR2(5000);
BEGIN
-- split cods string by comma
FOR i IN (
SELECT trim(regexp_substr(p_cod_str, '[^,]+', 1, LEVEL)) cod
FROM dual
CONNECT BY LEVEL <= regexp_count(p_cod_str, ',') + 1
) LOOP
q_loop := 'SELECT rownum as rn, a.id as id,
a.cod AS cod_a_' || i.cod || ', a.val AS val_a_' || i.cod || ',
b.cod AS cod_b_' || i.cod || ', b.val AS val_b_' || i.cod || '
FROM foo a, (
SELECT id, cod, val
FROM foo
WHERE dat = TO_DATE(''01-07-2019'', ''DD-MM-YYYY'')
) b
WHERE a.id = b.id AND a.cod = ' || i.cod || ' AND
a.dat = TO_DATE(''01-08-2019'', ''DD-MM-YYYY'')
';
q_select := q_select || ',
t_' || i.cod || '.cod_a_' || i.cod || ', t_' || i.cod || '.val_a_' || i.cod || ',
t_' || i.cod || '.cod_b_' || i.cod || ', t_' || i.cod || '.val_b_' || i.cod;
IF q_from IS NULL THEN
q_from := ' (
' || q_loop || '
) t_' || i.cod;
ELSE
q_from := q_from || '
FULL OUTER JOIN (
' || q_loop || '
) t_' || i.cod || '
ON t_' || i_prv || '.rn = t_' || i.cod || '.rn';
END IF;
i_prv := i.cod;
END LOOP;
-- mount query
q_query := 'SELECT ' || ltrim(q_select, ', ')
|| ' FROM ' || q_from;
dbms_output.put_line('query: ' || q_query);
END;
它将生成 SQL,EXECUTE IMMEDIATE
我可以运行它。
结果:
COD_A_35|VAL_A_35|COD_B_35|VAL_B_35|COD_A_75|VAL_A_75|COD_B_75|VAL_B_75|COD_A_45|VAL_A_45|COD_B_45|VAL_B_45|
--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|
35| 38.5| 35| 58.1| 75| 74.65| 75| 69.32| 45| 3.89| 45| 38.5|
35| 69.32| 35| 102| 75| 32.8| 75| 74.65| | | | |
| | | | 75| 3.89| 75| 12.76| | | | |
谢谢大家的提示
推荐阅读
- c - WideCharToMultiByte 随机失败,出现错误 122/ERROR_INSUFFICIENT_BUFFER
- google-apps-script - Google Script 有两个用于编辑时的触发器。我可以删除“简单”的吗?
- oracle - 为时间戳字段插入 SYSTIMESTAMP
- flutter - 如何显示从时间戳经过的时间?
- selenium - 使用 Robot Framework 处理随机按钮
- java - 创建基于位置的应用程序而不保存坐标
- javascript - 每个模块只有一个默认导出允许使用 Navbar 在 React Native 上出错
- angular - 添加确认对话框作为按钮的添加属性指令
- ruby-on-rails - rake db:migrate:不兼容的库版本 postgres
- laravel - 如何使用 Vue 和 Laravel 避免警告“直接改变道具”