精巧 SQL
来源：互联网 发布：淘宝客服常用话术 编辑：程序博客网 时间：2024/05/10 14:17
order by 的数值型灵活使用 代码: select * from table_a where id=p_id order by decode(函数,'asc',1,'desc',-1)*jsny;
控制试图的访问时间： 6. 代码: create view ... as select ... from where exists(select x from dual where sysdate>=8:00am and sysdate<=5:00pm) 妙用decode实现排序 代码: select * from tabname order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss'); select * from tabname order by decode(mode,'FIFO',rq-sysdate, sysdate-rq) 找出某个时期内工作日数： 代码: select count(*) from ( select rownum-1 rnum from all_objects where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002- 02-01','yyyy-mm-dd')+1 ) where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not in ( '1', '7' ) 我觉得查询重复记录的语句就很经典 代码: select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd) 由它引申的就有很多有用的语句，如昨天回答别人的排序的难题 代码: select id,bdsszd from BADWDJ a where a.id = (select max(id) from BADWDJ b where a.bdsszd =b.bdsszd) order by id 树型结构表的查询： 代码: select ID,PARENT_ID from parent_child connect by prior id = parent_id start with id = 1; 1.decode这个函数一定需要会，我觉得sql的灵活很多地方都是通过这个function来体现的，相当于if，很好用。 2.group by，这个东东想想简单，其实好多统计功能是离不开这个操作的。oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。值得注意的是，当你对事物做过有效的人为归并之后执行group by 往往会更让人心旷神怡。 3.很表竖置的经典写法，也要记住： 代码: sum(decode( )) group by ... 注意：需要在一个subquery中确定一个横置判点。 4.树形结构表的遍历写法： 代码: select ...from .... start with ... connect by prior (父子关系表达式) 代码: select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var); 816以上的 一些分析函数如 rank() over() and row_number() over() 当然关于 group by rollup/cube使用的人恐怕特别少 如何实现最大匹配的操作? 例如:给定字符串 '1234', 而表中可能有记录项'1','12','123','1234','12345',要选出'1234'项 代码: select * from ( select col_FT from table_FT where instr('12345',col_FT)=1 order by length(col_FT) desc) where rownum =1 给你一个意想不到的东西 代码: SQL> select to_char(to_date(12,'yyyy'),'year') from dual; TO_CHAR(TO_DATE(12,'YYYY'),'YEAR') ------------------------------------------ twelve select to_char(sysdate,'day') from dual 还有 d、iw、mm等等格式 对于translate函数有一个功能 比如：找出某个字符串中完全是数字 代码: select * from xxx where translate(column1,'1234567890','') = column1; select trunc(sysdate) from dual; select trunc(sysdate,'mm') from dual; 大家构造几个例子看看就能明白 代码: select a,b,sum(c) from xxx group by rollup(a,b); select a,b,sum(c) from xxx group by cube(a,b); 怎么查找字符串里面包含有%的记录： 当然，常规方法就是利用 escape了 可如果不知道escape也行，比如 代码: select * from xxx where replace(a,'%','') = a; 利用decode解决动态sql的不确定条件查询的问题: 假设前台传入的都是变量 代码: select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var); 这样比 like :var||'%' 效率高 另:对于 代码: select ... from a,b where a.a = b.a(+) and b.a is null; 我想对于不明白的朋友,我要交代一下用处才好: 比如,你需要查找在a表中有而b表中没有的记录 也许你会选择 not in: select * from a aa where aa.a1 not in (select a1 from bb); 这是效率最低的 或者: select a1 from aa minus select a1 from bb; 所有这些写法,都不如下面下率高: 代码: select a.* from aa a,bb b where a.a1 = b.a1(+) and b.a1 is null; 给一个很普通的适用的最高效的外连接例子（不是什么新鲜玩意）： select ... from a,b where a.a = b.a(+) and b.a is null; 我要按年龄段（小于20，20-30，---）统计人数，我可以用 代码: select sum(decode(sign(age - 20),-1,1,0)), sum(decode(sign(age - 20),-1,0,(decode(sign(age - 30,-1,1,0))))), sum(decode(sign(age - 30),-1,0,(decode(sign(age - 40,-1,1,0))))), sum(decode(sign(age - 40),-1,0,(decode(sign(age - 50,-1,1,0))))), sum(decode(sign(age - 50),-1,0,1)) from xxx; 这样只做一遍表扫描 这是分了20以下和50以上的 类似的问题，自己扩展了 添加行号： 代码: select (select count(*) from a1 where item <= a.item) AS ROW, * FROM a1 as a order by item select * from table1 a where id in (select top 3 from table1 where 物品=a.物品 order by price desc) 每一种物品有很多价格，每一种物品选择排在前三的纪录 1。job的使用： 代码: DBMS_JOB.SUBMIT(:jobno,//job号 'your_procedure;',//要执行的过程 trunc(sysdate)+1/24,//下次执行时间 'trunc(sysdate)+1/24+1'//每次间隔时间 ); 删除job:dbms_job.remove(jobno); 修改要执行的操作:job:dbms_job.what(jobno,what); 修改下次执行时间：dbms_job.next_date(job,next_date); 修改间隔时间：dbms_job.interval(job,interval); 停止job:dbms.broken(job,broken,nextdate); 启动job:dbms_job.run(jobno); 例子： 代码: VARIABLE jobno number; begin DBMS_JOB.SUBMIT(:jobno, 'Procdemo;',//Procdemo为过程名称 SYSDATE, 'SYSDATE + 1/720'); commit; end; / 2。把一个表放在内存里 alter table tablename cache. 3。创建临时表 代码: Create GLOBAL TEMPORARY TABLE TABLENAME ( COL1 VARCHAR2(10), COL2 NUMBER ) ON COMMIT PRESERVE(Delete) ROWS ; 这种临时表不占用表空间，而且不同的SESSION之间互相看不到对方的数据 在会话结束后表中的数据自动清空，如果选了Delete ROWS，则在提交的时候即清 空数据，PRESERVE则一直到会话结束 4。加一个自动增加的id号 第一种方法： 第一步：创建SEQUENCE 代码: create sequence s_country_id increment by 1 start with 1 maxvalue 999999999; 第二步：创建一个基于该表的before insert 触发器，在触发器中使用该 代码: SEQUENCE create or replace trigger bef_ins_t_country_define before insert on t_country_define referencing old as old new as new for each row begin select s_country_id.nextval into :new.country_id from dual; end; / 第二种方法： 代码: Create or REPLACE TRIGGER TR1 BEFORE Insert ON temp_table FOR EACH ROW declare com_num NUMBER; BEGIN Select MAX(ID) INTO COM_NUM FROM TEMP_TABLE; :NEW.ID:=COM_NUM+1; END TR1; / 5。限制用户登录：创建一个概要文件 create profile CLERK_PROFILE limit session_per_user 1 #用户可拥有的会话次数 idle_time 10 #进程处于空闲状态的时间(10分钟) 然后就可以将该概要文件授予一个用户 alter user A profile CLERK_PROFILE; 6。使触发器为无效alter trigger yourtriggername disable 如果是对于某一个表的所有的触发器： alter table yourtablename disable all triggers 更改数据库时间显示格式： SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; 会话已更改。 1. 选取 TOP N 行记录 代码: A. Select * FROM CAT Where ROWNUM<=N B. Select * FROM ( Select * FROM CAT orDER BY TABLE_TYPE ) Where ROWNUM<=N 2. 选取N1-N2行记录 代码: A. Select TABLE_NAME,TABLE_TYPE FROM ( Select ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ) Where ROWSEQ BETWEEN N1+1 AND N2; 或: 代码: Select * FROM CAT Where ROWNUM<=N2 MINUS Select * FROM CAT Where ROWNUM B. Select TABLE_NAME,TABLE_TYPE FROM ( Select ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT orDER BY TABLE_TYPE) Where ROWSEQ BETWEEN N1+1 AND N2; 查主键名称： 代码: select * from user_constraints where table_name = 'ART' and constraint_type ='P'; 保存过程内容到文件 先修改init.ora 例如： utl_file_dir=/usr //路径为 oracle所在的盘:/usr 此过程将用户TEMP的P1过程的代码保存到ORACLE安装盘下/USR/TEXT.TXT中 代码: create or replace procedure TEST is file_handle utl_file.file_type; STOR_TEXT VARCHAR2(4000); N NUMBER; I NUMBER; begin I:=1; Select MAX(LINE) INTO N FROM ALL_SOURCE Where OWNER='TEMP' AND NAME='P1'; file_handle:=utl_file.fopen('/usr','test.txt','a'); WHILE I<=N LOOP Select TEXT INTO STOR_TEXT FROM ALL_SOURCE Where OWNER='TEMP' AND NAME='P1' AND LINE= I; I:=I+1; utl_file.put_line(file_handle,stor_text); END LOOP; utl_file.fclose(file_handle); commit; end TEST; / 0、建立分区表 代码: create table partition_test ( id number(9), tmpStr varchar2(10) ) partition by range(id) ( partition id01 values less than (3000000) tablespace test_tabspc1, partition id02 values less than (6000000) tablespace test_tabspc2, partition id03 values less than (9000000) tablespace test_tabspc3, partition id04 values less than (12000000) tablespace test_tabspc4, partition id05 values less than (MAXVALUE) tablespace test_tabspc5 ) / 1、建立局部分区索引 Create index your_index on caishui.partition_test(id) local ( partition id01 tablespace test_tabspc1, partition id02 tablespace test_tabspc2, partition id03 tablespace test_tabspc3, partition id04 tablespace test_tabspc4, partition id05 tablespace test_tabspc5 ) / 2、重建某一个分区的索引 alter index your_index rebuild partition id01 tablespace test_tabspc1 / 3、增加分区 alter table caishui.partition_test add partition id06 values less than (15000000) tablespace test_tabspc6 / 4、有影响 5、可以 Alter TABLE PARTITION_TEST MERGE PARTITIONS id01, id02 INTO PARTITION 新分区名 / 6、外部数据文件 d:/test.txt 1|猪八戒 2|孙悟空 3|唐僧 建一个控制文件 d:/test.ctl load data infile 'd:/test.txt' append into table partition_test FIELDS TERMINATED BY "|" (id,tmpStr) 将数据文件的数据导入数据库 sqlldr userid=caishui/password control=d:/test.ctl 如何正确利用Rownum来限制查询所返回的行数？ 软件环境： 1、Windows NT4.0+ORACLE 8.0.4 2、ORACLE安装路径为：C:/ORANT 含义解释： 1、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2， 依此类推，这个伪字段可以用于限制查询返回的总行数。 2、rownum不能以任何基表的名称作为前缀。 使用方法： 现有一个商品销售表sale，表结构为： 代码: month char(6) --月份 sell number(10,2) --月销售金额 create table sale (month char(6),sell number); insert into sale values('200001',1000); insert into sale values('200002',1100); insert into sale values('200003',1200); insert into sale values('200004',1300); insert into sale values('200005',1400); insert into sale values('200006',1500); insert into sale values('200007',1600); insert into sale values('200101',1100); insert into sale values('200202',1200); insert into sale values('200301',1300); insert into sale values('200008',1000); commit; SQL> select rownum,month,sell from sale where rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标） ROWNUM MONTH SELL --------- ------ --------- 1 200001 1000 SQL> select rownum,month,sell from sale where rownum=2;（1以上都查不到记录） 没有查到记录 SQL> select rownum,month,sell from sale where rownum>5; （由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立，查不到记录） 没有查到记录 只返回前3条纪录 SQL> select rownum,month,sell from sale where rownum<4; ROWNUM MONTH SELL --------- ------ --------- 1 200001 1000 2 200002 1100 3 200003 1200 如何用rownum实现大于、小于逻辑？（返回rownum在4—10之间的数据）（minus操作，速度会受影响） SQL> select rownum,month,sell from sale where rownum<10 2 minus 3 select rownum,month,sell from sale where rownum<5; ROWNUM MONTH SELL --------- ------ --------- 5 200005 1400 6 200006 1500 7 200007 1600 8 200101 1100 9 200202 1200 想按日期排序，并且用rownum标出正确序号（有小到大） SQL> select rownum,month,sell from sale order by month; ROWNUM MONTH SELL --------- ------ --------- 1 200001 1000 2 200002 1100 3 200003 1200 4 200004 1300 5 200005 1400 6 200006 1500 7 200007 1600 11 200008 1000 8 200101 1100 9 200202 1200 10 200301 1300 查询到11记录. 可以发现，rownum并没有实现我们的意图，系统是按照记录入库时的顺序给记录排的号，rowid也是顺序分配的 SQL> select rowid,rownum,month,sell from sale order by rowid; ROWID ROWNUM MONTH SELL ------------------ --------- ------ --------- 000000E4.0000.0002 1 200001 1000 000000E4.0001.0002 2 200002 1100 000000E4.0002.0002 3 200003 1200 000000E4.0003.0002 4 200004 1300 000000E4.0004.0002 5 200005 1400 000000E4.0005.0002 6 200006 1500 000000E4.0006.0002 7 200007 1600 000000E4.0007.0002 8 200101 1100 000000E4.0008.0002 9 200202 1200 000000E4.0009.0002 10 200301 1300 000000E4.000A.0002 11 200008 1000 查询到11记录. 正确用法，使用子查询 SQL> select rownum,month,sell from (select month,sell from sale group by month,sell) where rownum<13; ROWNUM MONTH SELL --------- ------ --------- 1 200001 1000 2 200002 1100 3 200003 1200 4 200004 1300 5 200005 1400 6 200006 1500 7 200007 1600 8 200008 1000 9 200101 1100 10 200202 1200 11 200301 1300 按销售金额排序，并且用rownum标出正确序号（有小到大） SQL> select rownum,month,sell from (select sell,month from sale group by sell,month) where rownum<13; ROWNUM MONTH SELL --------- ------ --------- 1 200001 1000 2 200008 1000 3 200002 1100 4 200101 1100 5 200003 1200 6 200202 1200 7 200004 1300 8 200301 1300 9 200005 1400 10 200006 1500 11 200007 1600 查询到11记录. 利用以上方法，如在打印报表时，想在查出的数据中自动加上行号，就可以利用rownum。 返回第5—9条纪录，按月份排序 SQL> select * from (select rownum row_id ,month,sell 2 from (select month,sell from sale group by month,sell)) 3 where row_id between 5 and 9; ROW_ID MONTH SELL ---------- ------ ---------- 5 200005 1400 6 200006 1500 7 200007 1600 8 200008 1000 9 200101 1100 (1) 查所及杀锁 代码: select l.session_id sid, l.locked_mode lockmode, l.oracle_username db_user, l.os_user_name os_user, s.machine, s.schemaname, o.object_name tablename, q.sql_text from v$locked_object l, v$session s, v$sql q, all_objects o where l.session_id=s.sid and s.type='USER' and s.sql_address=q.address and l.object_id=o.object_id alter system kill session 'sid,SERIAL#' 1.having 子句的用法 having 子句对 group by 子句所确定的行组进行控制,having 子句条件中只允许涉及常量,聚组函数或group by 子句中的列. 2.外部联接"+"的用法 外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与 后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度.例如,下面这条命令执行起来很慢 代码: select a.empno from emp a where a.empno not in (select empno from emp1 where job=’SALE’); 倘若利用外部联接,改写命令如下: 代码: select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job=’SALE’; 可以发现,运行速度明显提高. 3.删除表内重复记录的方法 可以利用这样的命令来删除表内重复记录: 代码: delete from table_name a where rowid< (select max(rowid) from table_name where column1=a.column1 and column2=a.column2 and colum3=a.colum3 and ...); 问：用ORACLE的like（匹配操作命令）操作时，要查的条件含有特殊符号（_或%），该怎样写？ 如我要找出以tt_开头的表，若安以下写法只能取出以tt开头的表， 因为_在like中用意是任意单一字符。 代码: Select Tname FROM tab Where Tname like 'tt_％' 答：Select * FROM tab Where tname LIKE 'TT/_%' ESCAPE '/' 自增字段： orACLE一般的做法是同时使用序列和触发器来生成一个自增字段. Create SEQUENCE SEQname INCREMENT BY 1 START WITH 1 MAXVALUE 99999999 / Create TRIGGER TRGname BEFORE Insert ON table_name REFERENCING NEW AS :NEW FOR EACH ROW Begin Select SEQname.NEXTVAL INTO :NEW.FIELDname FROM DUAL; End; / 动态sql: 在oracle8.1.5中： 用execute immediate来实现 declare tsql varchar2(200); begin tsql:='insert into '||tname||'values ('aaa','bbb')'; execute immediate tsql; end; / 说明：复制表(只复制结构,源表名：a 新表名：b) SQL: select * into b from a where 1<>1 说明：拷贝表(拷贝数据,源表名：a 目标表名：b) SQL: insert into b(a, b, c) select d,e,f from b; 说明：显示文章、提交人和最后回复时间 SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 说明：外连接查询(表名1：a 表名2：b) SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 说明：日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 说明：两张关联表，删除主表中已经在副表中没有的信息 SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 说明：-- SQL: 代码: Select A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1, (Select X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (Select NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 Where TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (Select NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 Where TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') && '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, Where X.NUM = Y.NUM （+） AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B Where A.NUM = B.NUM 说明：-- SQL: select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩 说明： 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） SQL: Select a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC FROM (Select a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b Where a.tel = b.telfax) a GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') 说明：四表联查问题： SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 说明：得到表中最小的未使用的ID号 SQL: 代码: Select (CASE WHEN EXISTS(Select * FROM Handle b Where b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle Where NOT HandleID IN (Select a.HandleID - 1 FROM Handle a) 说明：复制表(只复制结构,源表名：a 新表名：b) SQL: select * into b from a where 1<>1 说明：拷贝表(拷贝数据,源表名：a 目标表名：b) SQL: insert into b(a, b, c) select d,e,f from b; 说明：显示文章、提交人和最后回复时间 SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 说明：外连接查询(表名1：a 表名2：b) SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 说明：日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 说明：两张关联表，删除主表中已经在副表中没有的信息 SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 说明：-- SQL: Select A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1, (Select X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (Select NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 Where TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (Select NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 Where TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, Where X.NUM = Y.NUM （+） AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B Where A.NUM = B.NUM 说明：-- SQL: select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩 说明： 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） SQL: Select a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC FROM (Select a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b Where a.tel = b.telfax) a GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') 说明：四表联查问题： SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 说明：得到表中最小的未使用的ID号 SQL: Select (CASE WHEN EXISTS(Select * FROM Handle b Where b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle Where NOT HandleID IN (Select a.HandleID - 1 FROM Handle a) 1. 行列转换--普通 假设有张学生成绩表(CJ)如下 Name Subject Result 张三 语文 80 张三 数学 90 张三 物理 85 李四 语文 85 李四 数学 92 李四 物理 82 想变成 姓名 语文 数学 物理 张三 80 90 85 李四 85 92 82 declare @sql varchar(4000) set @sql = 'select Name' select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']' from (select distinct Subject from CJ) as a select @sql = @sql+' from test group by name' exec(@sql) 2. 行列转换--合并 有表A, id pid 1 1 1 2 1 3 2 1 2 2 3 1 如何化成表B: id pid 1 1,2,3 2 1,2 3 1 创建一个合并的函数 create function fmerg(@id int) returns varchar(8000) as begin declare @str varchar(8000) set @str='' select @str=@str+','+cast(pid as varchar) from 表A where id=@id set @str=right(@str,len(@str)-1) return(@str) End go --调用自定义函数得到结果 select distinct id,dbo.fmerg(id) from 表A 3. 如何取得一个数据表的所有列名 方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。 SQL语句如下： declare @objid int,@objname char(40) set @objname = 'tablename' select @objid = id from sysobjects where id = object_id(@objname) select 'Column_name' = name from syscolumns where id = @objid order by colid 是不是太简单了？ 呵呵 不过经常用阿. 4. 通过SQL语句来更改用户的密码 修改别人的,需要sysadmin role EXEC sp_password NULL, 'newpassword', 'User' 如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa 5. 怎么判断出一个表的哪些字段不允许为空？ select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename 6. 如何在数据库里找到含有相同字段的表？ a. 查已知列名的情况 Select b.name as TableName,a.name as columnname From syscolumns a INNER JOIN sysobjects b ON a.id=b.id AND b.type='U' AND a.name='你的字段名字' b. 未知列名查所有在不同表出现过的列名 Select o.name As tablename,s1.name As columnname From syscolumns s1, sysobjects o Where s1.id = o.id And o.type = 'U' And Exists ( Select 1 From syscolumns s2 Where s1.name = s2.name And s1.id <> s2.id ) 7. 查询第xxx行数据 假设id是主键： select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id) 如果使用游标也是可以的 fetch absolute [number] from [cursor_name] 行数为绝对行数 8. SQL Server日期计算 a. 一个月的第一天 Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) b. 本周的星期一 Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) c. 一年的第一天 Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) d. 季度的第一天 Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) e. 上个月的最后一天 Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) f. 去年的最后一天 Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) g. 本月的最后一天 Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) h. 本月的第一个星期一 select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()) ), 0) i. 本年的最后一天 Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
- 精巧 SQL
- 精巧的sql注入总结
- 使用MySQL字符串运算实施精巧化SQL注入攻击
- 使用MySQL字符串运算实施精巧化SQL注入攻击
- 小海精巧通讯录 bt
- c++精巧cookie
- 设计精巧的openERP
- 设计精巧的openERP
- Bootstrap精巧布局
- 两个精巧的算法
- Bootstrap精巧布局
- Linux 精巧命令
- Bootstrap精巧布局
- Python 精巧知识点备忘
- 精巧完整的日历程序
- 精巧的代码（待续）
- 精巧好用的DelayQueue
- 精巧好用的DelayQueue
- AS3中的一些基本定义，Function与Method的区别
- Extjs中的combobox的动态加载问题
- 关于内存字节对齐
- 设计大师Donald Norman和Bill Buxton签书会在南京举行
- 我看中小企业的发展之路-下一个百度，腾讯也许就是你
- 精巧 SQL
- 我看博客与微博 平民文化与艺术文化
- 公室——教你如何把打印稿变成电子稿
- 我看QQ与360 双输
- 如何开发 Web 应用程序
- PHP，获取配置文件中，允许上传文件最大尺寸
- ioctl 设置非阻塞SOCKET函数
- 安全的清理Debug Release文件夹
- 我看中国IT企业未来的生存之路
