明月松间照 清泉石上流


                                        ——— 兵临城下   猫科动物
posts - 70, comments - 137, trackbacks - 0, articles - 23
  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

SQL 笔记(定时更新)

Posted on 2006-12-18 17:48 兵临城下 阅读(472) 评论(0)  编辑  收藏 所属分类: J2SE

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中执行效果并不好,所以尽量少使用。


只有注册用户登录后才能发表评论。


网站导航: