蛙蛙推荐:整理了一些SQL数据库技巧,贴一下

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)
-----------------------------------------------------------------------------------------------------------------------
2.删除重复数据
-----------------------------------------------------------------------------------------------------------------------
一、具有主键的情况
a.具有唯一性的字段id(为唯一主键)
delete table
where id not in
(
select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，
那么只要col1字段内容相同即表示记录相同。

b.具有联合主键
假设col1+'''',''''+col2+'''',''''...col5 为联合主键
select * from  table where col1+'''',''''+col2+'''',''''...col5 in (
  select max(col1+'''',''''+col2+'''',''''...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件，
如只有col1，那么只要col1字段内容相同即表示记录相同。

c:判断所有的字段
  select * into #aa from table group by id1,id2,....
  delete table
  insert into table
  select * from #aa

二、没有主键的情况

a:用临时表实现
select identity(int,1,1) as id,* into #temp from ta
delete #temp
where id not in
(
  select max(id) from # group by col1,col2,col3...
)
delete table ta
inset into ta(...)
   select ..... from #temp

b:用改变表结构（加一个唯一字段）来实现
alter table 表 add  newfield int identity(1,1)
delete 表
where newfield not in
(
select min(newfield) from 表 group by 除newfield外的所有字段
)

alter table 表 drop column newfield
-----------------------------------------------------------------------------------------------------------------------

上一页  [1] [2] 

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18