常用的SQL语句常用的SQL语句供大家参考，希望对大家有所帮助。
说明：复制表(只复制结构,源表名：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)
重复记录
当设计表的时候没有建组合字段唯一约束，以后需要增加这一约束时，却发现表里已经有了很多重复记录了。
请看看我用的去掉表里组合字段重复的记录方法：
假设原始表名为source_table,字段名1为field_name1,字段名2为field_name2。
（当然稍加修改也可以用到三个及以上组合字段重复的情况）
第一步: 生成组合字段重复的临时表source_dup_simple
create table source_dup_simple
nologging
pctfree 1 pctused 99
as select field_name1,field_name2,count(0) as num from source_table
group by field_name1,field_name2 having count(0)>1;
第二步: 生成组合字段重复的主表里完整记录的临时表source_dup
create table source_dup
nologging
pctfree 1 pctused 99
as select t1.* from source_table t1,source_dup_simple t2
where t1.field_name1=t2.field_name1 and t1.field_name2=t2.field_name2;
第三步: 删去source_dup里的重复记录
--可选择:保留rowid小的记录
delete from source_dup a where rowid > (
select min(rowid) from source_dup b where
a.field_name1 = b.field_name1 and a.field_name2=b.field_name2);
commit;
--可选择:保留rowid大的记录
delete from source_dup a where rowid < (
select max(rowid) from source_dup b where
a.field_name1 = b.field_name1 and a.field_name2=b.field_name2);
commit;
注意:如果操作一万条以上的记录最好在source_dup的field_name1和field_name2字段上建索引. 如果想按别的删除规则，如保留日期最新的记录：
--可选择:保留时间字段date_field大的记录
delete from source_dup a where date_field < (
select max(date_field) from source_dup b where
a.field_name1 = b.field_name1 and a.field_name2=b.field_name2);
commit;
--可选择:保留时间字段date_field小的记录
delete from source_dup a where date_field > (
select min(date_field) from source_dup b where
a.field_name1 = b.field_name1 and a.field_name2=b.field_name2);
commit;
如果时间字段上有重复,还需要再次根据rowid来删一次
delete from source_dup a where rowid < (
select max(rowid) from source_dup b where
a.field_name1 = b.field_name1 and a.field_name2=b.field_name2);
commit;
第四步: 删去所有重复组合字段原始表里记录
delete from source_table
where field_name1||field_name2 in (select field_name1||field_name2 from source_dup_simple);
commit;
注意:如果操作一万条以上的记录最好在source_table的field_name1和field_name2字段上建索引.
第五步: 把剩下的没有重复的记录插回原始表
insert into source_table select * from source_dup;
commit;
获取序列的创建脚本：
select
'create sequence schema_name.'|| SEQUENCE_NAME || ' minvalue '||MIN_VALUE||' maxvalue '||MAX_VALUE||' start with '||LAST_NUMBER||' increment by '||INCREMENT_BY||' cache '||CACHE_SIZE||' ;'
from dba_sequences where SEQUENCE_OWNER='&your_schema_name';
查找id中的不连续号：
SELECT /*+ordered */ T2.id + 1, MIN (t3.id) - 1
FROM test T1, test T2, test t3
WHERE T1.id(+) = (T2.id + 1) AND T1.id IS NULL AND t3.id > T2.id
GROUP BY T2.id;
SQL> create table test
2 (id number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> insert into test values(2);
1 row created.
SQL> insert into test values(3);
1 row created.
SQL> insert into test values(4);
1 row created.
SQL> insert into test values(6);
1 row created.
SQL> insert into test values(8);
1 row created.
SQL> insert into test values(10);
1 row created.
SQL> insert into test values(11);
1 row created.
SQL> insert into test values(13);
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT /*+ordered */ T2.id + 1, MIN (t3.id) - 1
2 FROM test T1, test T2, test t3
3 WHERE T1.id(+) = (T2.id + 1) AND T1.id IS NULL AND t3.id > T2.id
4 GROUP BY T2.id;
T2.ID+1 MIN(T3.ID)-1
---------- ------------
5 5
7 7
9 9
12 12
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NUM VARCHAR2(10)
---------- ----------
1 20061101
1 20061102
1 20061103
1 20061104
1 20061105
1 20061106
1 20061107
2 20061101
2 20061103
2 20061106
2 20061107
2 20061109
2 20061110
3 20061101
3 20061103
3 20061106
3 20061107
3 20061108
3 20061109
3 20061110
3 20061111
怎样能查询出来这样的结果,查询出连续的记录来。
就像下面的这样？
2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125
SQL如下
SQL> r
1 SELECT b.id, MIN (b.num) Start_num, MAX (b.num) End_num
2 FROM (SELECT a.*, TO_NUMBER (a.num - ROWNUM) cc
3 FROM (SELECT *
4 FROM test
5 ORDER BY id, num) a) b
6* GROUP BY b.id, b.cc
ID START_NUM END_NUM
---------- ---------- ----------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009
以下是分析过程
SQL> SELECT *
2 FROM test
3 ORDER BY id, num;
ID NUM
---------- ----------
2013 00000120
2013 00000121
2013 00000122
2013 00000124
2013 00000125
2014 00000001
2014 00000002
2014 00000003
2014 00000004
2014 00000005
2014 00000007
2014 00000008
2014 00000009
13 rows selected.
SQL> r
1 SELECT a.*,rownum, TO_NUMBER (a.num - ROWNUM) cc
2 FROM (SELECT *
3 FROM test
4* ORDER BY id, num) a
ID NUM ROWNUM CC
---------- ---------- ---------- ----------
2013 00000120 1 119
2013 00000121 2 119
2013 00000122 3 119
2013 00000124 4 120
2013 00000125 5 120
2014 00000001 6 -5
2014 00000002 7 -5
2014 00000003 8 -5
2014 00000004 9 -5
2014 00000005 10 -5
2014 00000007 11 -4
2014 00000008 12 -4
2014 00000009 13 -4
13 rows selected.
select * from tableName t
where exists(select null from tableName where t.id=id and rowid<>t.rowid)
order by t.id
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472914/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9599/viewspace-472914/
扫一扫
4177
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
