我想根據表中一個總和列的條件在查詢結果中保留前 n 行。我已經添加了行號和按子句磁區，但停留在這一步。
我已經寫了下面的查詢
SELECT ID, WEIGHT, ACC
, ROW_NUMBER() OVER (PARTITION BY ID
ORDER BY ID ,WEIGHT asc, ACC asc) as ROWNUMBER
, SUM (WEIGHT) OVER (PARTITION BY ID as SUM_WEIGHT
FROM TABLE1
它給了我下面的結果（一個樣本）：
ID WEIGHT ACC ROW_NUMBER SUM_WEIGHT
1 1 157691 1 1
2 0.95 338897 1 0.95
3 1 315356 1 1
4 1 314455 1 2
4 1 347079 2 2
5 0.2 297082 1 0.3
5 0.1 356082 2 0.3
我希望我的查詢回傳的是“如果 sum_weight>1 僅保留行號 1，否則保留 ID 的所有行”，如下所示：
ID WEIGHT ACC ROW_NUMBER SUM_WEIGHT
1 1 157691 1 1
2 0.95 338897 1 0.95
3 1 315356 1 1
4 1 314455 1 2
5 0.2 297082 1 0.3
5 0.1 356082 2 0.3
uj5u.com熱心網友回復：
將查詢包裝在外部查詢中并在那里過濾：
SELECT *
FROM (
SELECT ID,
WEIGHT,
ACC,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, WEIGHT asc, ACC asc)
as ROWNUMBER,
SUM (WEIGHT) OVER (PARTITION BY ID) as SUM_WEIGHT
FROM TABLE1
)
WHERE rownumber = 1
OR sum_weight <= 1;
其中，對于樣本資料：
CREATE TABLE table1 (ID, WEIGHT, ACC) AS
SELECT 1, 1, 157691 FROM DUAL UNION ALL
SELECT 2, 0.95, 338897 FROM DUAL UNION ALL
SELECT 3, 1, 315356 FROM DUAL UNION ALL
SELECT 4, 1, 314455 FROM DUAL UNION ALL
SELECT 4, 1, 347079 FROM DUAL UNION ALL
SELECT 5, 0.2, 297082 FROM DUAL UNION ALL
SELECT 5, 0.1, 356082 FROM DUAL;
輸出：
| ID | 重量 | ACC | 行號 | SUM_WEIGHT |
|---|---|---|---|---|
| 1 | 1 | 157691 | 1 | 1 |
| 2 | .95 | 338897 | 1 | .95 |
| 3 | 1 | 315356 | 1 | 1 |
| 4 | 1 | 314455 | 1 | 2 |
| 5 | .1 | 356082 | 1 | .3 |
| 5 | .2 | 297082 | 2 | .3 |
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/506870.html
下一篇：Oracle-基于一列的結果