1. rownum
select * from (select deptname, rownumber() over() rn from org) a where rn<3
DEPTNAME RN
-------------- --------------------
Head Office 1
New England 2
2 record(s) selected.
select deptname from org fetch first 2 rows only
DEPTNAME
--------------
Head Office
New England
2 record(s) selected.
2. date
select hex(current date) from sysibm.sysdummy1
1
--------
20070216
1 record(s) selected.
select '^^' from sysibm.sysdummy1 where '20070216' between hex(current date) and hex(current date -5 days)
1
--
0 record(s) selected.
SUBSTR(CHAR(HEX(CURRENT TIMESTAMP)),1,14)
3. nvl
SQL SELECT 절에서 collesce(col1,널대체값) 또는 value(col1,널대체값)
4. instr
userid >> test:1234
test:123
select substr(userid, LOCATE( ':', userid )+1) from test_table;
1234
123
5.rownum
db2 "select * from (select a.*,rownumber() over() rn from staff a fetch first 1 rows only )a where rn=0"
6. sequence
"일련번호" DECIMAL(15,0) NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +999999999999999
NO CYCLE
CACHE 20
NO ORDER ) )
그리고 db에서 제공하는 sequence를 사용하는 방법도 있습니다.
'보관함' 카테고리의 다른 글
오라클과 DB2 숫자 - 함수 비교 (0) | 2021.05.17 |
---|---|
HexToBin BinToHex (0) | 2021.05.17 |
'아이자와 미나미 ' 이번에도 MC를 맡게 되었습니다!꼭 봐줘~ (0) | 2021.05.17 |
클라라 (이성민 | CLARA) 그냥 예쁜? 아니 엄청 예쁨! (0) | 2021.05.16 |
문재인 대통령 사주팔자(四柱八字),운세 (0) | 2021.05.15 |