實用閱讀表
| 房間號 | 電讀 | 閱讀日期 |
|---|---|---|
| N201 | 279.8 | 2022 年 2 月 15 日 |
| N201 | 240.6 | 2022 年 1 月 16 日 |
| N201 | 240.6 | 2021 年 12 月 15 日 |
| N202 | 299.8 | 2022 年 2 月 15 日 |
| N202 | 259.8 | 2022 年 1 月 15 日 |
UTILITYINVOICE 表應如下所示：
| 房間號 | 電子 | 電前 | 發票日期 |
|---|---|---|---|
| N201 | 279.8 | 240.6 | 2022 年 2 月 19 日 |
| N202 | 299.8 | 259.8 | 2022 年 2 月 19 日 |
我能夠ELECCURRENT通過執行插入值（或當前月份的二月份讀數）：
INSERT INTO UTILITYINVOICE (ROOMNUMBER, ELECCURRENT, INVOICEDATE)
SELECT
ROOMNUMBER, NVL(ELECTRICITYREADING, 0), SYSDATE
FROM UTILITYREADING
WHERE (extract(MONTH from DATEOFREADING) = extract(MONTH from SYSDATE))
AND (extract(YEAR from DATEOFREADING) = extract(YEAR from SYSDATE));
我的問題是獲取二月份的讀數，為了獲取一月份的讀數，我嘗試了這個但沒有成功：
UPDATE UTILITYINVOICE
SET ELECPREVIOUS = (SELECT NVL(ELECTRICITYREADING, 0)
FROM UTILITYREADING)
WHERE EXISTS (SELECT 1 FROM utilityreading
WHERE (extract(MONTH from UTILITYREADING.DATEOFREADING) = extract(MONTH from SYSDATE)-1)
AND (extract(YEAR from UTILITYREADING.DATEOFREADING) = extract(YEAR from SYSDATE))
AND utilityreading.roomnumber = utilityinvoice.roomnumber);
uj5u.com熱心網友回復：
我會說你需要
LAG分析功能（選擇以前的電量讀數）ROW_NUMBER（按日期降序對每個房間號的行 進行排序）- 然后獲取排名最高的行
SQL> insert into utilityinvoice (roomnumber, eleccurrent, elecprevious, invoicedate)
2 with temp as
3 (select roomnumber,
4 electricityreading as eleccurrent,
5 lag(electricityreading) over (partition by roomnumber order by dateofreading) elecprevious,
6 row_number() Over (partition by roomnumber order by dateofreading desc) rn
7 from utilityreading
8 )
9 select roomnumber, eleccurrent, elecprevious, sysdate
10 from temp
11 where rn = 1;
2 rows created.
SQL> select * from utilityinvoice;
ROOM ELECCURRENT ELECPREVIOUS INVOICEDAT
---- ----------- ------------ ----------
N201 279,8 240,6 02/19/2022
N202 299,8 259,8 02/19/2022
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/429538.html