Oracle返回上一个不为空的值，含表结构和测试数据，直接就能跑

最新推荐文章于 2024-07-09 17:21:41 发布
最新推荐文章于 2024-07-09 17:21:41 发布
阅读量668 收藏 1
点赞数
文章标签： 大数据 oracle sql 数据库 数据库开发
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_56496488/article/details/125552911
版权

碰到一个朋友发出这样的问题

如何把sl字段为空的值赋为上一个不为空的值

那么来了 ，建个表先

create table T_SYS_HOLIDAY
(
  id             VARCHAR2(50) default SYS_GUID() not null,
  year           VARCHAR2(4),
  month          VARCHAR2(2),
  day            NUMBER,
  isholiday      VARCHAR2(1) default 0 not null,
  modifypersonid NUMBER,
  modifytime     DATE default SYSDATE,
  others         VARCHAR2(100),
  time           DATE,
  sl             NUMBER
);

 插入一年的测试数据

prompt Importing table t_sys_holiday...
set feedback off
set define off
insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('A508FF8A7E754FD395DD69613672A828', '2021', '12', 31, '0', null, to_date('31-12-2020 15:03:14', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('31-12-2021', 'dd-mm-yyyy'), 1872170743.78009);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('B865A3C607784D269152D4E59F7ABC3C', '2022', '11', 18, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('18-11-2022', 'dd-mm-yyyy'), 1702233675.26361);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('890F07A854D248A2BAC02ADA2CD6537D', '2022', '11', 19, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('19-11-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('DE54238868253BFEE0530CD8620A947E', '2022', '04', 11, '0', null, to_date('06-05-2022 16:26:54', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('11-04-2022', 'dd-mm-yyyy'), 1898039543.28192);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('DE588AB07F5F7FE9E0530CD8620ADE2E', '2022', '07', 20, '0', null, to_date('06-05-2022 21:07:33', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('20-07-2022', 'dd-mm-yyyy'), 1703466895.13971);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('DE588AB07F607FE9E0530CD8620ADE2E', '2022', '10', 28, '0', null, to_date('06-05-2022 21:07:33', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('28-10-2022', 'dd-mm-yyyy'), 1749072206.18071);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('B95D11663D2C44C69B903AB8E28CC957', '2022', '11', 20, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('20-11-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('1C63CF5A160C4A1DA3D8AD849AE1C8F3', '2022', '11', 21, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('21-11-2022', 'dd-mm-yyyy'), 1737139933.71026);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('4873F08168574BE6B29DCD6D8BA0DD5C', '2022', '11', 22, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('22-11-2022', 'dd-mm-yyyy'), 1888644131.75162);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('F07E628678424509A1C8E35913A56650', '2022', '11', 23, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('23-11-2022', 'dd-mm-yyyy'), 1762864390.82754);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('DEA524402343493FAA71D405AAA158C2', '2022', '11', 24, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('24-11-2022', 'dd-mm-yyyy'), 1841408196.40745);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('1CD5CAD45C3742179F9BE6DFCDF6A805', '2022', '11', 25, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('25-11-2022', 'dd-mm-yyyy'), 1844539863.65246);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('A4E377AB03204E118AEDAAC923F40928', '2022', '11', 26, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('26-11-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('859A9270156846AA97A344A57936A40A', '2022', '11', 27, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('27-11-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('2A0DFD90E24E490F869A4DD1CBB5871E', '2022', '11', 28, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('28-11-2022', 'dd-mm-yyyy'), 1861270250.96896);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('B075398235BE4D57BD84D5F8E79EF065', '2022', '11', 29, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('29-11-2022', 'dd-mm-yyyy'), 1765391067.0753);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('4DA2E8A8DB644747BF1D57C86168A0B5', '2022', '11', 30, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('30-11-2022', 'dd-mm-yyyy'), 1870888032.34242);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('DEFDCC95C46D4AD7AF2F1B22C705A301', '2022', '12', 1, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('01-12-2022', 'dd-mm-yyyy'), 1799942161.22311);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('E55AFD6E94BD45D69ED668B01EE99F42', '2022', '12', 2, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('02-12-2022', 'dd-mm-yyyy'), 1843889234.23264);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('13B4EBC5CB6B473FAFBF9BF7645E545D', '2022', '12', 3, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('03-12-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('0FAC9AF18C204D6DAA8D2093D15A4456', '2022', '12', 4, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('04-12-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('31E8B5889CE544D0B449A23716CC0E8A', '2022', '12', 5, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('05-12-2022', 'dd-mm-yyyy'), 1753669050.33691);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('FA86E1BF8FBC4A4E8D7F2B751E88C1B4', '2022', '12', 6, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('06-12-2022', 'dd-mm-yyyy'), 1790230544.50291);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('E71016ED38284A879D1DB68274DBCD89', '2022', '12', 7, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('07-12-2022', 'dd-mm-yyyy'), 1796106482.17648);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('B5B4FFE67E7D42B4A1F22D5000EE1755', '2022', '12', 8, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('08-12-2022', 'dd-mm-yyyy'), 1741891838.56174);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('E82C6AF67D8448C5BE5E3D38A4D392F0', '2022', '12', 9, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('09-12-2022', 'dd-mm-yyyy'), 1723137783.44224);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('575C8FF9C8A042BD9DD0D8830C09907A', '2022', '12', 10, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('10-12-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('E441E0C833FF4C7C812A851E861DE9D1', '2022', '12', 11, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('11-12-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('DEC4D7F65C1E464EA05011E816DAAC0C', '2022', '12', 12, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('12-12-2022', 'dd-mm-yyyy'), 1704988006.14337);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('378511504BC74FA3846BE658E632DFDC', '2022', '12', 13, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('13-12-2022', 'dd-mm-yyyy'), 1889116648.55519);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('BC282BF5D04144DB939B527FFA8167E2', '2022', '12', 14, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('14-12-2022', 'dd-mm-yyyy'), 1839163503.03608);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('ED21C4A7FF6449C39AB7BC749C489112', '2022', '12', 15, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('15-12-2022', 'dd-mm-yyyy'), 1727971688.30545);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('4E1C8F46F2ED4ADF8C25AFA48C45F8E0', '2022', '12', 16, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('16-12-2022', 'dd-mm-yyyy'), 1729490505.35848);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('F00BAB3CD23D4A6997B43ED9BF5D3A31', '2022', '12', 17, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('17-12-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('E357401CCAD7494F8DE1AC33BC7E13C9', '2022', '12', 18, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('18-12-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('04ACE31F1C5641EFB28064C9FFDB5CA9', '2022', '12', 19, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('19-12-2022', 'dd-mm-yyyy'), 1886410301.65603);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('FB03EB0E50D0447B93802A74A0F1FEBA', '2022', '12', 20, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('20-12-2022', 'dd-mm-yyyy'), 1813881077.68988);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('FEF927BBF0174640AB38F2568DD6B70E', '2022', '12', 21, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('21-12-2022', 'dd-mm-yyyy'), 1757021897.87729);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('E8B8866E40524299A480B0D30048D4C2', '2022', '12', 22, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('22-12-2022', 'dd-mm-yyyy'), 1862152920.73828);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('051A83DD00F14AEFB8B7013BBCB79954', '2022', '12', 23, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('23-12-2022', 'dd-mm-yyyy'), 1735555830.57915);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('677CC146D32A4677BF80ED718D11EC14', '2022', '12', 24, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('24-12-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('927DB349311A4802AD49275E6D73E6BD', '2022', '12', 25, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('25-12-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('70E087B15EFF474AAB4ACDDE077A9321', '2022', '12', 26, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('26-12-2022', 'dd-mm-yyyy'), 1880631057.01797);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('3B55B66B87394929A958FB6B858354F8', '2022', '12', 27, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('27-12-2022', 'dd-mm-yyyy'), 1774049836.82477);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('7F13DE5A3E4342488FFA5E750AA171B5', '2022', '12', 28, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('28-12-2022', 'dd-mm-yyyy'), 1740202891.03009);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('0B07790E9DDE42AB83C7B96A3C0BBDB7', '2022', '12', 29, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('29-12-2022', 'dd-mm-yyyy'), 1772298633.57721);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('4EF69B0FA8EC41EE9EB3E7E0A731E472', '2022', '12', 30, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('30-12-2022', 'dd-mm-yyyy'), 1801908382.06072);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('088E79B14B0F4DE2A6EA03B914951207', '2022', '12', 31, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('31-12-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('E792B63C9EE14DEC9610E774F23289FC', '2022', '06', 19, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('19-06-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('5AB1778053F448E2A22D2D5C235279E6', '2022', '06', 20, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('20-06-2022', 'dd-mm-yyyy'), 1709610095.75108);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('44106C772133465D81E93DE07192EB8A', '2022', '06', 21, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('21-06-2022', 'dd-mm-yyyy'), 1899207470.2441);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('3B9A8A9A9CF943DEACFA14EE6FA8B849', '2022', '06', 22, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('22-06-2022', 'dd-mm-yyyy'), 1856747063.39454);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('CB50522F0D25493E90978B07A35A3349', '2022', '06', 23, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('23-06-2022', 'dd-mm-yyyy'), 1708388109.32963);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('B12598DD1ABE4984B051D50A0EAE7CB6', '2022', '06', 24, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('24-06-2022', 'dd-mm-yyyy'), 1728540259.55107);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('D3B0315B11224DC7AB8D1904F6E5E107', '2022', '06', 25, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('25-06-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('739A6589B5A748B2B59DE0F4AF668462', '2022', '06', 26, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('26-06-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('79282A8B238448458600F9048AF9B244', '2022', '06', 27, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('27-06-2022', 'dd-mm-yyyy'), 1843502790.10533);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('B60CF35FCBE04708814F9662DACB049B', '2022', '06', 28, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('28-06-2022', 'dd-mm-yyyy'), 1778817398.59465);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('6E5565739476453887DDCF4E14443E51', '2022', '06', 29, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('29-06-2022', 'dd-mm-yyyy'), 1817512521.76913);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('B41774E8FA174173814E64CB1B689890', '2022', '06', 30, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('30-06-2022', 'dd-mm-yyyy'), 1872417450.39846);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('FEC84C1EBEDA4F2297767077C6EF6BF6', '2022', '07', 1, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('01-07-2022', 'dd-mm-yyyy'), 1722026532.72135);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('34373FC2A559446CACA841F3259ECB01', '2022', '07', 2, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('02-07-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('BD1721B6D95B4CF78E2BBE03EBF66A85', '2022', '07', 3, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('03-07-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('18FAB7CB42354756B8FAA4F940BBD660', '2022', '07', 4, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('04-07-2022', 'dd-mm-yyyy'), 1755494163.85934);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('B7E2B5406E0A4D9FB70D6A4D139ECCB2', '2022', '07', 5, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('05-07-2022', 'dd-mm-yyyy'), 1807941632.14682);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('E7EFBBF3B53749B6A62CF5AC05C08B31', '2022', '07', 6, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('06-07-2022', 'dd-mm-yyyy'), 1728846867.94973);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('3A7F772A6AB24C45801A7171AC14A07F', '2022', '07', 7, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('07-07-2022', 'dd-mm-yyyy'), 1884219254.54901);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('F2BC3457B06F49EDA1048B00DE5840A3', '2022', '07', 8, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('08-07-2022', 'dd-mm-yyyy'), 1862397175.07772);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('BBDF5FE81CF74660BBA3F6D0492340F9', '2022', '07', 9, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('09-07-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('B5C348DF48B84BF9A955EE71B055B900', '2022', '07', 10, '1', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('10-07-2022', 'dd-mm-yyyy'), null);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('735FB0CD7504410CA1093677FEBB2530', '2022', '07', 11, '0', null, to_date('17-12-2021 14:55:23', 'dd-mm-yyyy hh24:mi:ss'), null, to_date('11-07-2022', 'dd-mm-yyyy'), 1895673626.89884);

insert into t_sys_holiday (ID, YEAR, MONTH, DAY, ISHOLIDAY, MODIFYPERSONID, MODIFYTIME, OTHERS, TIME, SL)
values ('6EEA55AED4924668A7F423AA17685945', '2022', '07', 12, '0', null, to_date('17-1
最低0.47元/天 解锁文章
木及言风
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
hive 补全某字段的空值，取字段上一条非空记录的值
bitzwc的博客
12-07 1万+
一、问题 我们经过遇到这样的问题，要补全一个表某列的空值，策略是按某个规则排序后，取上一个非空的值替代。 二、思路 1、首先分组获取行号 2、找到被替代值所在的行号，以及下一个替代值的行号 3、把开始行号到下一个替代值行号的区间（左闭右开区间）数组展开，得到被替代值在每个行的值 4、原表join3生成的表 三、实现 1、先做一个测试表，有三列字段：uid,time,event。分别...
Oracle SQL中返回第一个值不为空的列的函数coalesce的用法
IT_ZZQ的博客
02-14 4433
场景描述： 比如我们在select某一个值的时候，要从多个列中按照先后顺序依次取值，直到取到第一个不为空的值，比较笨的方法是： select nvl(colmun1,nvl(colmun2,nvl(colmun3,nvl(colmun4,colmun5)))) from dual; 这样写虽然可以达到目的，但是写起来很麻烦，可读性差，修改起来也比较麻烦 提供一种便捷一些的写法，供参考： ...
coalesce返回所有值中第一个不为空的值的应用场景
靡不有初，鲜克有终
06-27 281
COALESCE` 是 SQL 中的一个函数，它接受两个或多个参数，并返回第一个非 `NULL`（非空）的值。如果没有一个值是非 `NULL` 的，`COALESCE` 将返回 `NULL`。假设有一个员工表 `employees`，包含员工的姓名 `name` 和昵称 `nickname`。在这个例子中，如果 `nickname` 是 `NULL`，`COALESCE` 将返回 `name`；如果 `name` 也是 `NULL`，则返回 `NULL`。
Oracle中查询某字段不为空或者为空的SQL语句
qq_43171656的博客
12-10 7220
比如 insert into table a (a1,b1)values(“a1”,’’); 对于这种情况，因为表里存的是”，其实是没有内容的，要查询这个字段，不能直接使用 select * from a where b1=’’; sql中判断非空不能用等号，因为null在sql中被看作特殊符号，必须使用关键字 is和not 应该如此使用： select * from A where b1 is null 或者： select * from A where b1 is not null ...
Oracle中对空字符串的判断
大宇进阶之路的博客
07-20 7648
习惯了编程的人喜欢用!=，但这样都是错误的，应该使用isnull或者isnotnull，或者使用nvl函数。=null我们看上去这是对的，但是和null的比较都为fasle，所以结果是false。首先理解，在oracle中，默认将空字符视为null，而对null进行判断都为false。oracle中常见的一个小错误就是字符串判断是否为空。或者使用nvl函数，nvl(字符串，为空时的默认值)...
JDBC查询返回数据集一直为空，明明数据库(MySQL)有数据的解决办法
12-14
在使用Java的JDBC进行MySQL数据库操作时，有时可能会遇到这样一个困扰：明明数据库中存在数据，但是通过JDBC执行查询语句后返回的数据集却始终为空。这种情况通常是由于编码问题导致的，具体来说，是项目编码与...
Oracle中如何用一条SQL快速生成10万条测试数据
12-22
在Oracle数据库管理中，生成大量测试数据是进行性能测试、数据建模以及验证查询效率等场景下不可或缺的一环。本文将深入探讨如何利用一条SQL语句在Oracle中快速生成10万条测试数据，这对于数据库管理员和开发人员来...
Oracle全表扫描及其执行计划
12-14
6. 数据块小于DB_FILE_MULTIBLOCK_READ_COUNT：Oracle会尝试一次性读取多个数据块，如果表的数据块大小小于这个参数值，全表扫描可能发生。 为了演示全表扫描，我们可以创建一个测试环境。首先创建一个表`T`，并...
oracle不走索引可能原因.docx
04-29
1. **INDEX SKIP SCAN**: 当创建了一个组合索引，但是查询只使用了索引的非第一列时，Oracle可能会选择不使用索引，因为它无法直接通过索引来定位数据。INDEX SKIP SCAN是一种优化策略，允许数据库跳过索引的第一个...
Oracle中大批量删除数据的方法
12-16
首先，这个过程定义了一个名为`delBigTab`的存储过程，它接受三个参数：`p_TableName`（要删除数据的表名）、`p_Condition`（删除条件）和`p_Count`（每次删除的行数）。`pragma autonomous_transaction`指令表明这...
SQL常用函数
weixin_45946210的博客
08-30 388
例如，对于字符串 “你好世界”，CHAR_LENGTH 函数将返回 5，因为它包含了 5个字符。COALESCE()函数是一种在SQL中常用的函数，用于返回参数列表中的第一个非空值。如果所有参数都为空，则返回NULL。根据你的需求，选择适合的函数来计算字符串的长度。CHAR_LENGTH(str) 和 LENGTH(str) 函数都用于计算字符串的长度，但在某些情况下它们可能会有所不同。另外，不同的数据库管理系统可能在 ROUND() 函数的行为和语法上略有差异，请根据具体的数据库系统文档进行参考。
COALESCE函数
热门推荐
老王玩编程
03-18 2万+
COALESCE函数是用来处理NULL值的一个函数。
sql技巧：将空值替换为序列的上一个非空值
TC147147147的博客
12-09 1301
方法简要说明方法优势方法劣势方法1开窗函数直接取，需要忽略空值效率最高、代码量最少很多数据库语言不支持开窗函数里忽略空值方法2做分组的辅助列，再p_id,辅助列聚合开窗用max取代码量少，可读性好，执行效率高，几乎所有数据库都支持方法3在select后面取字段时，用子查询关联原表，并限制死只取1个值效率比较高，代码量较少很多数据库语言不支持方法4做分组的辅助列，将每个p_id对应的的dept合并为数组，然后再按辅助列的顺序将数组拆开。适用大多数sql数据库，执行效率还可以。
Oracle提取上一行数据的有效方法（oracle 上一行数据）
最新发布
Z_408的专栏
07-09 177
这个查询语句将返回每个员工薪资（sal）和他们所在部门号（deptno），以及他们上一个员工的薪资（prev_sal）。OVER子句指定按照部门号进行排序，这意味着使用LAG函数返回的是同一部门号内上一个员工的薪资。Oracle 8i之后，引入了LAG和LEAD函数，这两个函数可以用于查询某个列的当前行和它前面或后面的行。这个查询使用了自连接，将员工表（emp）连接到自身，查询条件是当前员工的薪资是前一个员工的薪资加一。这个查询语句将返回SMITH的上一个员工的姓名和薪资。方法二：使用自连接查询。
2021-06-22Oracle常用函数
WANG1690333108的博客
06-22 1858
常用Oracle数据库函数 1、–获取第一个不为null的变量 COALESCE(T1.CNAME, T2.CNAME, T3.CNAME, T4.CnNAME) --获取第一个不为null的变量 2、–第一个为空则取第二个 NVL( B.CSID, A.CSID ) 3、null 1）任何数据都可以为空 2）如果null参与运算，则结果都空 3）空值null与字符串进行拼接操作，则null相当于没有（||） 4、nvl(字段1,值1):处理null的函数，
oracle 不为空
08-19
在Oracle中，判断某个值是否为空通常使用IS NULL或IS NOT NULL运算符。在引用中的示例中，使用了IS NOT NULL来判断'sdfds'是否为空，如果不为空返回1，否则返回2。而在引用中的示例中，使用了'!='来判断'sdfds'是否为空，如果不为空返回1，否则返回2。在引用中的示例中，是一个创建表的语句，与判断Oracle中的值是否为空无关。所以，提供的引用内容中没有直接回答"Oracle不为空"的信息。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* [Oracle中对空字符串的判断](https://blog.csdn.net/wjzholmes/article/details/125895134)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *3* [Oracle返回上一个不为空的值，含表结构和测试数据，直接就能跑](https://blog.csdn.net/weixin_56496488/article/details/125552911)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值