我有下表：
CREATE TABLE tablename ("ID" varchar2(1), "Type" varchar2(3), "Value" int);
INSERT ALL
INTO tablename ("ID", "Type", "Value")
VALUES ('A', 'MS',2)
INTO tablename ("ID","Type", "Value")
VALUES ('A', 'MS', 5)
INTO tablename ("ID", "Type", "Value")
VALUES ('A', 'MSH', 6)
INTO tablename ("ID", "Type", "Value")
VALUES ('A', 'MSH', 10)
INTO tablename ("ID", "Type", "Value")
VALUES ('A', 'MSO', -5)
INTO tablename ("ID", "Type", "Value")
VALUES ('A', 'MSO', 12)
INTO tablename ("ID", "Type", "Value")
VALUES ('B', 'MS',5)
INTO tablename ("ID","Type", "Value")
VALUES ('B', 'MS', -4)
INTO tablename ("ID", "Type", "Value")
VALUES ('B', 'MSH', 2)
INTO tablename ("ID", "Type", "Value")
VALUES ('B', 'MSH', 11)
INTO tablename ("ID", "Type", "Value")
VALUES ('B', 'MSO', -5)
INTO tablename ("ID", "Type", "Value")
VALUES ('B', 'MSO', 13)
SELECT * FROM dual
;
該表將按 ID 和型別分組，并使用值的總和。現在我想得到每個 ID 的 MS-MSH 和 MS-MSO 的區別。
所以結果應該是這樣的
ID | Type | sum(value) | Dif
A | MS | 7 | 0
A | MSH | 16 | -9
A | MSO | 7 | 0
B | MS | 1 | 0
B | MH | 13 | -12
B | MSO | 9 | -8
這是要使用的表
uj5u.com熱心網友回復：
（更多評論）我沒有測驗過，我會嘗試這些方面的東西。希望這個對你有幫助。抱歉，我沒有測驗過。
with cte as
(
select t.id,t.type,sum(t.value) as sumval
from
tablename as t
group by t.id,t.type
)
select c.*,
case c.type
when 'ms' then 0
else (c.sumval-(select c2.sumval from cte c2 where c2.id=c.id and c2.type='ms'))
end dif
from cte c
order by c.id,c.type
uj5u.com熱心網友回復：
您可以將聚合函式包裝在分析函式中，并使用條件聚合來查找相同的MS值（然后您只需讀取表一次，不需要任何相關的子查詢或 CTE）：typeid
SELECT id,
type,
sum(value) as sumval,
MAX(CASE type WHEN 'MS' THEN SUM(value) END) OVER (PARTITION BY id)
- SUM(value) AS diff
FROM tablename
GROUP BY
id,
type;
其中，對于樣本資料：
CREATE TABLE tablename (ID, Type, Value) AS
SELECT 'A', 'MS', 2 FROM DUAL UNION ALL
SELECT 'A', 'MS', 5 FROM DUAL UNION ALL
SELECT 'A', 'MSH', 6 FROM DUAL UNION ALL
SELECT 'A', 'MSH', 10 FROM DUAL UNION ALL
SELECT 'A', 'MSO', -5 FROM DUAL UNION ALL
SELECT 'A', 'MSO', 12 FROM DUAL UNION ALL
SELECT 'B', 'MS', 5 FROM DUAL UNION ALL
SELECT 'B', 'MS', -4 FROM DUAL UNION ALL
SELECT 'B', 'MSH', 2 FROM DUAL UNION ALL
SELECT 'B', 'MSH', 11 FROM DUAL UNION ALL
SELECT 'B', 'MSO', -5 FROM DUAL UNION ALL
SELECT 'B', 'MSO', 13 FROM DUAL;
輸出：
ID 型別 總和 差異 一種 多發性硬化癥 7 0 一種 多發性硬化癥 16 -9 一種 MSO 7 0 乙 多發性硬化癥 1 0 乙 多發性硬化癥 13 -12 乙 MSO 8 -7
db<>在這里擺弄
uj5u.com熱心網友回復：
如果您有"Types"like 'MS1', 'MSH1', 'MSO1', 'MS2', 'MSO2'...，則查詢有效，diff 被磁區"ID"并且字串末尾的數字"Type"，查詢僅 1 查看表，沒有子查詢和 cte，沒有 case 陳述句。
詢問：
select "ID","Type",sum("Value") Sum_value,
FIRST_VALUE (sum("Value")) over (partition by "ID",
nvl(regexp_substr("Type",'[0-9]{1,}$'),'0')
ORDER BY "ID")-sum("Value")
diff
from tablename
group by ("ID","Type")
樣本資料：
CREATE TABLE tablename ("ID" varchar2(1), "Type" varchar2(3), "Value" int);
INSERT ALL
INTO tablename ("ID", "Type", "Value")
VALUES ('A', 'MS',2)
INTO tablename ("ID","Type", "Value")
VALUES ('A', 'MS', 5)
INTO tablename ("ID", "Type", "Value")
VALUES ('A', 'MSH', 6)
INTO tablename ("ID", "Type", "Value")
VALUES ('A', 'MSH', 10)
INTO tablename ("ID", "Type", "Value")
VALUES ('A', 'MSO', -5)
INTO tablename ("ID", "Type", "Value")
VALUES ('A', 'MSO', 12)
INTO tablename ("ID", "Type", "Value")
VALUES ('B', 'MS',5)
INTO tablename ("ID","Type", "Value")
VALUES ('B', 'MS', -4)
INTO tablename ("ID", "Type", "Value")
VALUES ('B', 'MSH', 2)
INTO tablename ("ID", "Type", "Value")
VALUES ('B', 'MSH', 11)
INTO tablename ("ID", "Type", "Value")
VALUES ('B', 'MSO', -5)
INTO tablename ("ID", "Type", "Value")
VALUES ('B', 'MSO', 13)
SELECT * FROM dual
;
結果：
ID Type SUM_VALUE DIFF
----------------------
A MS 7 0
A MSH 16 -9
A MSO 7 0
B MS 1 0
B MSH 13 -12
B MSO 8 -7
uj5u.com熱心網友回復：
使用LAG決議函式：
SQL> with temp as
2 (select id, type, sum(value) sumval
3 from tablename
4 group by id, type
5 )
6 select id, type, sumval,
7 --
8 -case when type = 'MS' then 0
9 when type = 'MSH' then sumval - lag(sumval, 1) over (partition by id order by type)
10 when type = 'MSO' then sumval - lag(sumval, 2) over (partition by id order by type)
11 end diff
12 from temp
13 order by id, type;
ID TYPE SUMVAL DIFF
-- ---- ---------- ----------
A MS 7 0
A MSH 16 -9
A MSO 7 0
B MS 1 0
B MSH 13 -12
B MSO 8 -7
6 rows selected.
SQL>
與您的問題無關，但是-這是Oracle。避免雙引號和混合大小寫識別符號，您只會遇到這些問題。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/466335.html