经典SQL语句

一.怎样删除一个表中某个字段重复的列呀,举个例子
表[table1]
id    name
1 aa
2 bb
3 cc
1 aa
2 bb
3 cc
我想最后的表是这样的
id    name
1 aa
2 bb
3 cc
回答:
将记录存到临时表#t中，重复的记录只存一条，然后将临时表#t中的记录再存回原表中，注意“select distinct id,class,name”要包含你需要的所有字段，否则有些字段就被删掉了。
在查询管理器里执行下面代码:
-----------------------------
SELECT DISTINCT id,, name
INTO #t
FROM table1 DELETE table1
          INSERT
        INTO table1
                  SELECT *
                FROM #t
------------------------------
二.找出既会VB又会PHP的人
表是这样的：
ID 员工 技能
1 1 VB
2 1 PHP
3 1 ASP
4 2 PHP
5 3 ASP
6 4 VB
7 4 ASP
要从这张表中找出既会VB又会PHP的人，SQL该怎么写啊？
回答:
---------------------------------------------------------------------------------------------
SELECT 员工 FROM [Table] WHERE 员工 IN(SELECT 员工 FROM [Table] WHERE 技能='VB' ) AND 技能='PHP'
----------------------------------------------------------------------------------------------
三.数据库合并问题
access里的两个表,想让两个表的内容合并

表[a]结构如下:
[id]        编号        自动编号
[name]      名称        文本
[price]     价格        数字
[guige]     规格        文本
[changjia]  生产厂家    文本
[baozhuang] 包装        文本
[danwei]    单位        文本
共有900条记录,除了id和name字段,其他均可以为空

表[b]结构如下:
[id]        编号        自动编号
[name]      名称        文本
[price]     价格        数字
[changjia]  生产厂家    文本
[danwei]    单位        文本
[xingzhi]   性质        文本
共有800条记录,除了id和name字段,比表[a]少几个字段,但还多一个[xingzhi]的字符安其它均可以为空

现在想生成一个新表[c],结构如下,而且内容是两个表的内容之和,
[id]        编号        自动编号
[name]      名称        文本
[price]     价格        数字
[guige]     规格        文本
[changjia]  生产厂家    文本
[baozhuang] 包装        文本
[danwei]    单位        文本
[xingzhi]   性质        文本

用sql语句也可以,手工操作也好,xml也好,别管怎么着吧,怎么实现呀,哥们要郁闷坏了,真要让我们再输入800条记录,我就挂了,
回答:
1.这样
-----------------------------
insert into c(id,name,.....)
select id,name,.....
from a
insert into c(id,name,.....)
select max(id)+1,name,.....
from b
------------------------------
2.更正：
如果直接在查询分析器里执行：
-------------------------------
insert into c(name,.....)
select name,.....
from a
insert into c(name,.....)
select name,.....
from b
--------------------------------
3.用union方法
---------------------------------
insert into [c] ([id] ,编号,自动编号)
select [id],编号,自动编号 from [a]
union
select [id],编号,自动编号 from [b]
-----------------------------------
4.asp的解决办法
------------------------------------------------------------
<% '循环检测a表
Set rs = Server.CreateObect("ADODB.RECORDSET")
????? rs.open "select * from a order by id",conn,1,1
????? Do while not rs.eof
????????? Call actAdd(rs("name"))??? '调用像b表添加内容的函数！
????? rs.MoveNext
????? Loop
rs.Close
Set rs = Nothing

Sub actAdd(txt)
Dim ts, sql
sql = "insert into b(name) values('"& txt &"')"
Set ts = Conn.Execute(sql)
????? ts.Close
Set ts = Nothing
end Sub
%>
------------------------------------------------------------------
5.asp的解决办法
-----------------------------------------------------------------------------------
<%
dim arr_temp1,arr_temp2,arr_data
set rs=conn.execute("select id,name,price,guige,changjia,baozhuang,danwei from a")
arr_temp1=rs.getrows
rs.close
set rs=nothing

set rs=conn.execute("select id,name,price,guige,changjia,danwei,xingzhi from b")
arr_temp2=rs.getrows
rs.close
set rs=nothing

rem 开始处理
redim arr_data(ubound(arr_temp1,2)+ubound(arr_temp2,2),7)
rem 把两个数组的内容复制进来
这一部分自己写了做两个循环
然后再存进数据库
%>
---------------------------------------------------------------------------------------
最后转一些经典的SQL语句:
1.蛙蛙推荐：一些精妙的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)
-----------------------------------------------------------------------------------------------------------------------
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+','...col 5 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.tblCustomer(customerID,DepartmentID,Salary),用一条SQL显示将每一个Customer的Salary加上该Customer所在的部门的平均Salary

drop table tblCustomer
create table tblCustomer
( CustomerID varchar(10),DepartmentID varchar(10),Salary int)
insert tblCustomer select '005','c003',99 union all
select '001','c001',45 union all
select '002','c002',120 union all
select '003','c003',55 union all
select '004','c001',88

select * from tblCustomer order by CustomerID
SELECT DepartmentID,AVG(Salary) AS AvgSalary FROM tblCustomer GROUP BY DepartmentID
select a.CustomerID,a.Salary,d.AvgSalary,a.Salary+d.AvgSalary as TotalSalary  from tblCustomer a inner join (SELECT DepartmentID,AVG(Salary) AS AvgSalary FROM tblCustomer GROUP BY DepartmentID) as d on a.DepartmentID = d.DepartmentID

2.现有以下数据表:

月份  编号       分数
01    A01         80
01    A02         75
01    A03         82

02    B01         85
02    B02         86
02    B03         92

请教如何写一条SQL语句统计出不同月份的最高分和最低分,得到如下结果:

月份  编号      最高分    编号    最低分
01    A03         82      A02       75
02    B03         92      B01       85

希望能用最简单的方法实现上述功能.
create table 表(月份 varchar(10),编号 varchar(10),分数 int)
insert 表 select '01','A01',80

union all select '01','A02',75
union all select '01','A03',82
union all select '02','B01',85
union all select '02','B02',86
union all select '02','B03',92
go

--查询(如果不考虑分数重复的问题),可以直接用:
select a.月份
 ,最高分编号=b.编号,a.最高分
 ,最低分编号=c.编号,a.最低分
from(
 select 月份,最高分=max(分数),最低分=min(分数)
 from 表
 group by 月份
)a join 表 b on a.月份=b.月份 and a.最高分=b.分数
 join 表 c on a.月份=c.月份 and a.最低分=c.分数

--如果分数相同时,只显示一条,就改用
select 月份
 ,最高分编号=(select top 1 编号 from 表 where 月份=a.月份 and 分数=max(a.分数))
 ,最高分=max(分数)
 ,最低分编号=(select top 1 编号 from 表 where 月份=a.月份 and 分数=min(a.分数))
 ,最低分=min(分数)
from 表 a
group by 月份
go

--删除测试
drop table 表

/*--测试结果

月份        最高分编号 最高分      最低分编号  最低分        
---------- ---------- ----------- ---------- -----------
01         A03        82          A02        75
02         B03        92          B01        85

（所影响的行数为 2 行）

月份        最高分编号 最高分      最低分编号  最低分        
---------- ---------- ----------- ---------- -----------
01         A03        82          A02        75
02         B03        92          B01        85

（所影响的行数为 2 行）
--*/

3列变为行
//列变为行(idea1)
select AccountID,count(*)  as Mailing   from tblEntityMailing
 where AccountID='' group by AccountID Union All
select AccountID,count(*)  as Payment   from tblBizPayment
 where AccountID='' group by AccountID Union All
select AccountID,count(*)  as WorkOrder from tblBizBusinessVariation
 where AccountID='' group by AccountID

//列变为行(idea2)
drop table test0615
Create table test0615(id int,a1 int,a2 int,a3 int,a4 int)
go
--
insert test0615 (id,a1,a2,a3,a4)
select 1,67,78,67,90
union all select 2,34,56,45,89
union all select 3,23,56,78,65
union all select 4,87,98,70,50
union all select 5,56,89,42,19

go

--查询
select id ,a1 as ax from test0615
union select id,a2 from test0615
union select id,a3 from test0615
union select id,a4 from test0615

4.
select c.Customer,
sum( case b.Tax1 when null then 0 else (case when  b.Tax1 in(c.Tax1,c.Tax2,c.Tax3,c.Tax4,c.Tax5) Then a.TransactionAmount else 0 end) end) as [Tax1Amount],
sum( case b.Tax2 when null then 0 else (case when  b.Tax2 in(c.Tax1,c.Tax2,c.Tax3,c.Tax4,c.Tax5) Then a.TransactionAmount else 0 end) end) as [Tax2Amount],
sum( case b.Tax3 when null then 0 else (case when  b.Tax3 in(c.Tax1,c.Tax2,c.Tax3,c.Tax4,c.Tax5) Then a.TransactionAmount else 0 end) end) as [Tax3Amount],
sum( case b.Tax4 when null then 0 else (case when  b.Tax4 in(c.Tax1,c.Tax2,c.Tax3,c.Tax4,c.Tax5) Then a.TransactionAmount else 0 end) end) as [Tax4Amount],
sum( case b.Tax4 when null then 0 else (case when  b.Tax5 in(c.Tax1,c.Tax2,c.Tax3,c.Tax4,c.Tax5) Then a.TransactionAmount else 0 end) end) as [Tax5Amount]
from  tblageBilltmp a inner join tblRevenue b on a.RevenueCode=b.RevenueCode inner join tblCustomer c on a.Customer=c.Customer group by c.Customer
5.