with 語句：
WITH T1 AS(SELECT DISTINCT C_UNIT_CODE,C_CNTY_CODE,C_PRODUCT_ID,I_AUTH_LEVEL FROM TRX_USER_AUTH_LEVEL WHERE C_GRP_CODE = 'GRP1206' AND C_USER_ID = 'usr1206'),
T2 AS(SELECT DISTINCT B.C_MAIN_REF,B.C_UNIT_CODE,I_AUTH_LEVEL,B.C_CNTY_CODE,B.C_PRODUCT_ID FROM TRX_MATRIX_LIST A JOIN TRX_AUTH_LIST B ON A.C_MAIN_REF = B.C_MAIN_REF AND A.C_UNIT_CODE = B.C_UNIT_CODE AND A.C_CNTY_CODE = B.C_CNTY_CODE AND A.C_PRODUCT_ID = B.C_PRODUCT_ID AND A.I_OP_LEVEL = B.I_AUTH_LEVEL WHERE B.C_MAIN_REF NOT IN (SELECT DISTINCT C_MAIN_REF FROM TRX_AUTH_LIST WHERE C_USER_ID = 'usr1206')),
T3 AS(SELECT T2.C_MAIN_REF,T2.C_UNIT_CODE,T2.C_CNTY_CODE,T2.C_PRODUCT_ID FROM T2 JOIN T1 ON T1.C_UNIT_CODE = T2.C_UNIT_CODE AND T1.C_CNTY_CODE = T2.C_CNTY_CODE AND T1.C_PRODUCT_ID = T2.C_PRODUCT_ID AND T1.I_AUTH_LEVEL = T2.I_AUTH_LEVEL),
T4 AS(SELECT A. C_UNIT_CODE,A.C_TRX_STATUS,A.C_STATUS_NAME,A.C_PRODUCT_NAME,A.C_MAIN_REF,A.CUST_REF,A.COMP_NAME,A.SCB_REF,A.T_LOCKED_TIME,A.TXN_DATE,A.OTSTND_CCY,A.OTSTND_AMT,A.C_IS_TEMPLATE,A.C_GRP_CODE,A.C_FUNC_ID,A.C_CNTY_CODE,A.C_BK_GROUP_ID,A.C_MODULE,A.BANK_INIT,A.C_PRODUCT_ID,A.C_TRX_REF,A.I_EVENT_TIMES? FROM TRX_INBOX A JOIN T3 ON T3.C_MAIN_REF = A.C_MAIN_REF AND T3.C_UNIT_CODE = A.C_UNIT_CODE AND T3.C_CNTY_CODE = A.C_CNTY_CODE AND T3.C_PRODUCT_ID = A.C_PRODUCT_ID WHERE? (C_TRX_STATUS ='P'OR C_TRX_STATUS='T')AND (BANK_INIT <> 'T'OR BANK_INIT? IS NULL)AND (C_IS_TEMPLATE <> 'Y'OR C_IS_TEMPLATE IS NULL)? AND? C_BK_GROUP_ID? ='SCBBANK'),
T5 AS(SELECT DISTINCT A.C_UNIT_CODE,A.C_CNTY_CODE,B.C_PRODUCT_ID FROM TRX_OP_FUNC A JOIN TRX_FAP_RULE B ON A.C_FAP_ID = B.C_FAP_ID WHERE C_GRP_CODE = 'GRP1206' AND C_USER_ID = 'usr1206')
SELECT COUNT(*) AS RECCOUNT FROM T4 JOIN T5 ON T4.C_UNIT_CODE = T5.C_UNIT_CODE AND T4.C_CNTY_CODE = T5.C_CNTY_CODE AND T4.C_PRODUCT_ID = T5.C_PRODUCT_ID
UNION語句：
SELECT? C_MAIN_REF,I_AUTH_LEVEL,C_TRX_STATUS ,TXN_DATE,TXN_TIME FROM? shgt_auth WHERE C_MAIN_REF='SGGHYLL10030' And C_UNIT_CODE='CINDYGP'
AND C_CNTY_CODE='CN' AND C_trx_status = 'A'
UNION
select? B.C_MAIN_REF,B.I_AUTH_LEVEL,B.C_TRX_STATUS ,B.TXN_DATE,B.TXN_TIME from TRX_AUTH_LIST A,shgt_auth B
WHERE B.C_MAIN_REF='SGGHYLL10030' And B.C_UNIT_CODE='CINDYGP'
?
對指定表不寫日志：alter table *** activate not logged initially
創建表：
DELETE FROM TABLE EXIMTRX.KEYTEST;
DROP TABLE EXIMTRX.KEYTEST;
CREATE TABLE EXIMTRX.KEYTEST
(
??? USER_ID??? INTEGER,
??? USER_NAME? VARCHAR(10) NOT NULL,
??? USER_ADDR? VARCHAR(10) NOT NULL,
??? USER_MAIL? VARCHAR(10) NOT NULL,
??? USER_SEC?? VARCHAR(10) NOT NULL,
??? HASH?????? VARCHAR(10),
???PRIMARY KEY (USER_ID,USER_NAME,USER_ADDR,USER_MAIL,USER_SEC)
);
檢索數據庫表中的不重復記錄 (按B列來查詢，去除B列中的重復數據）
SELECT * FROM TEST WHERE id in (SELECT MIN(id) FROM TEST GROUP BY B)
1、查找表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、刪除表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷，只留有rowid最小的記錄
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
3、查找表中多余的重復記錄（多個字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、刪除表中多余的重復記錄（多個字段），只留有rowid最小的記錄
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重復記錄（多個字段），不包含rowid最小的記錄
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
?
?
復制表
create table trx_inbox_back like trx_inbox
insert into trx_inbox_back? select * from trx_inbox
創建視圖
drop view view_sql
create view view_sql as
select * from table where ....
create view viewTest(id,name,cnty,address) as
select a.id,a.col1,a.col2,b.col1 from tablea a ,tableb b where a.id<10000 and a.id=b.id;
select * from viewTest where id<5000
建立索引
create index idx_inbox_back on TRX_INBOX_BACK(C_CNTY_CODE,C_UNIT_CODE,C_PRODUCT_ID)
選取前100條記錄
select * from trx_inbox FETCH FIRST 100 ROWS ONLY
join 語句
SELECT DISTINCT A.CUST_REF,A.SCB_REF,A.COMP_NAME,A.C_PRODUCT_NAME,A.COUNTER_PARTY_NAME,A.C_STATUS_NAME,A.C_TRX_STATUS,A.C_UNIT_CODE? FROM TRX_INBOX_back A JOIN TRX_OP_FUNC B ON A.C_CNTY_CODE = B.C_CNTY_CODE AND A.C_UNIT_CODE = B.C_UNIT_CODE AND A.C_PRODUCT_ID = B.C_PRODUCT_ID AND B.C_GRP_CODE = 'HKGROUP' AND B.C_USER_ID = 'hkuser' WHERE ( A.C_TRX_STATUS = 'P' OR A.C_TRX_STATUS = 'T') AND (A.BANK_INIT???? <> 'T' OR A.BANK_INIT????? IS NULL) AND (A.C_IS_TEMPLATE <> 'Y' OR A.C_IS_TEMPLATE? IS NULL) AND? A.C_BK_GROUP_ID? ='SCBBANK'? AND A.C_MAIN_REF LIKE '%xcblcx%' AND A.C_PRODUCT_ID = 'P05102100000'
count指定字段 （去除指定字段重復行）注：指定字段中不能包括 INTEGER 類型
select count( distinct user_id || user_sec) as counter from test
通配符的一些用法：(關鍵字：like?%?[]?-)
select?*?from?tablename?where?column1?like?'[A-M]%'
這樣可以選擇出column字段中首字母在A-M之間的記錄
select?*?from?tablename?where?column1?like?'[ABC]%'
這樣可以選擇出column字段中首字母是A或者B或者C的記錄
select?*?from?tablename?where?column1?like?'[A-CG]%'
這樣可以選擇出column字段中首字母在A-C之間的或者是G的記錄
select?*?from?tablename?where?column1?like?'[^C]%'
這樣可以選擇出column字段中首字母不是C的記錄
脫字符（關鍵字：like?_）
通過使用下滑線字符（_），可以匹配任意單個字符
select?*?from?tablename?where?column1?like?'M_crosoft'?
匹配特殊字符：（[?]?_?-?%）
把他們都放到[]中就行了，比如：
select?*?from?tablename?where?column1?like?'%[%]%'?
匹配發音（關鍵字：SOUNDEX?DIFFERENCE）
如果不知道一個名字確切的發音，但是又多少知道一點，可以考慮使用SOUNDEX?DIFFERENCE函數。
select?*?from?tablename?where?DIFFERENCE(column1,'Laofei'>3)
DIFFERENCE返回0-4之間的數字，4是非常接近，0是差異非常大
要深入了解DIFFERENCE函數的工作原理，使用SOUNDEX函數返回DIFFERENCE函數所使用的音標碼
select?column1?as?column,SOUNDEX(column1)?'sound?like'?
注意：
DIFFERENCE函數比較兩個字符串的第一個字母和所有的輔音字母，該函數忽略任何元音字母（包括Y）,除非元音字母是該字符串的第一個字母。
使用這兩個函數在where中執行效果并不好，所以盡量少使用。