我有下表
| EMPID | MESASURE | | MEASURE_VAL1 |MEASURE_VAL2 |MEASURE_VAL3 |
| -------- | -------------- |---------- |-------------- |--------------
| A | MEASURE1 | 10 | NULL | NULL
| A | MEASURE2 | NULL | 20 | NULL
| A | MEASURE3 | NULL | NULL | 30
| A | MEASURE4 | NULL | NULL | NULL
我想得到結果
| EMPID | MEASURE 1 | MEASURE 2 | MEASURE 3
-------------------------------------------
| A | 10 | 20 | 30
我試過 where not null 并且只得到一個值。請建議。謝謝
uj5u.com熱心網友回復：
對我來說，它看起來像
SQL> with test (empid, measure, measure_val1, measure_val2, measure_val3) as
2 (select 'A', 'MEASURE1', 10 , null, null from dual union all
3 select 'A', 'MEASURE2', null, 20 , null from dual union all
4 select 'A', 'MEASURE3', null, null, 30 from dual union all
5 select 'A', 'MEASURE4', null, null, null from dual
6 )
7 select empid,
8 max(case when measure = 'MEASURE1' then nvl(measure_val1, 0) nvl(measure_val2, 0) nvl(measure_val3, 0) end) measure_1,
9 max(case when measure = 'MEASURE2' then nvl(measure_val1, 0) nvl(measure_val2, 0) nvl(measure_val3, 0) end) measure_2,
10 max(case when measure = 'MEASURE3' then nvl(measure_val1, 0) nvl(measure_val2, 0) nvl(measure_val3, 0) end) measure_3,
11 max(case when measure = 'MEASURE4' then nvl(measure_val1, 0) nvl(measure_val2, 0) nvl(measure_val3, 0) end) measure_4
12 from test
13 group by empid;
EMPID MEASURE_1 MEASURE_2 MEASURE_3 MEASURE_4
----- ---------- ---------- ---------- ----------
A 10 20 30 0
SQL>
uj5u.com熱心網友回復：
使用SUM（或MAX）聚合函式：
SELECT empid,
SUM(measure_val1) AS measure1,
SUM(measure_val2) AS measure2,
SUM(measure_val3) AS measure3
FROM table_name
GROUP BY empid;
其中，對于樣本資料：
CREATE TABLE table_name (EMPID, MESASURE, MEASURE_VAL1, MEASURE_VAL2, MEASURE_VAL3) AS
SELECT 'A', 'MEASURE1', 10, NULL, NULL FROM DUAL UNION ALL
SELECT 'A', 'MEASURE2', NULL, 20, NULL FROM DUAL UNION ALL
SELECT 'A', 'MEASURE3', NULL, NULL, 30 FROM DUAL UNION ALL
SELECT 'A', 'MEASURE4', NULL, NULL, NULL FROM DUAL;
輸出：
EMPID 措施1 措施2 措施3 一個 10 20 30
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/395801.html
上一篇：我如何授予執行陳述句
下一篇：Oracle中如何多次更新表？