常用的SQL语句

说明：复制表(只复制结构,源表名：a 新表名：b)
SQL: select * into b from a where 1<>1

说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
insert into b(a, b, c) select d,e,f from b

说明：合并数据(表名1：a 表名2：b)
select a,b,c from a union select d,e,f from b

说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b )
或者:
select a,b,c from a where a IN (1,2,3)

说明：显示文章、提交人和最后回复时间
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)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

说明：在线视图查询(表名1：a )
select * from (Select a,b,c FROM a) T where t.a > 1

自连接取出冗余数据
把所有姓名相同的只取出一个
select a.name from table_name a where a.id in
(select b.id from table_name b where a.id<>b.id)
同理删除冗余数据
delete from table_name  where table_name.id in
(select b.id from table_name b where table_name.id<>b.id)

选择在每一组b值相同的数据中对应的a最大的(换成average或别的函数或子查询,你会有意想不到的发现)记录的所有信息.
类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.
这种相关子查询和delete(insert)等结合起来,可以用于消除数据库中某个字段(或某些)的重复值
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

   ( //----这个(except)我没用过：（)
(select a from tableA ) except (select a from tableB) except (select a from tableC)

这个是超长的
Select DISTINCT TSD.Time_Sheet_Dtl_Record_No,TSD.User_Record_No,TP.Period_Start_Date ,TP.Period_End_Date INTO #temp FROM Time_Sheet_Details TSD, Time_Sheet_Period TP ,User_Group_User_Relationship UGUR,User_Group_Master UGM ,User_Data_Access_Right UDAR Where TSD.status ='TS_WFMGRA' AND DATEDIFF(dd,TP.period_start_date,TSD.work_Date)>=0 AND DATEDIFF(dd,TP.period_end_date,TSD.work_Date)<=0 AND UGUR.User_Record_No = TSD.User_Record_No AND UGM.User_Group_Record_No=UGUR.User_Group_Record_No AND UGM.User_Group_Name IN('Technician','Engineer') AND UDAR.User_Record_No = TSD.User_Record_No AND UDAR.Division_Record_No IN(1) Select DISTINCT A.User_Record_No, B.Staff_No, B.Full_Name,B.Job_Title, SUM(working_hour) AS Working_Hours,SUM(ot) AS OT_HOURS, C.Period_Start_Date,C.Period_End_Date INTO #temp2 FROM Time_Sheet_Details A INNER JOIN User_Master B ON B.User_Record_No= A.User_Record_No INNER JOIN #temp C ON C.Time_Sheet_Dtl_Record_No = A.Time_Sheet_Dtl_Record_No GROUP BY A.User_Record_No, B.Staff_No,B.Full_Name,B.Job_Title, C.Period_Start_Date,C.Period_End_Date HAVING COUNT(*) = DATEDIFF(d,C.Period_Start_Date,C.Period_End_Date)+1 Select A.User_Record_No,A.Staff_No,A.Full_Name,A.Job_Title ,A.Working_Hours,A.OT_HOURS,A.Period_Start_Date,A.Period_End_Date ,DM.Division_Code INTO #temp3 FROM #temp2 AS A INNER JOIN User_Data_Access_Right UDAR ON UDAR.User_Record_No = A.User_Record_No INNER JOIN Division_Master DM ON DM.Division_Record_No = UDAR.Division_Record_No Select * From #temp3 order by 1,7,8; Select Count(Distinct User_Record_No+Period_Start_Date+Period_End_Date) From #temp3 Drop TABLE #temp ,#temp2,#temp3

微软不公布SQLserver2000中的函数:
print pwdcompare('helloworld', pwdencrypt('helloworld'))
print pwdcompare('hello', pwdencrypt('world'))

随机取出10条数据
select top 10 * from tablename order by newid()

功能：
type   vender pcs
电脑   A        1
电脑   A        1
光盘   B        2
光盘   A        2
手机   B        3
手机   C        3
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type

FUNCTION：DELELTE REPEAT RECORDS
Delete  from tablename where id not in (select max(id) from tablename group by col1,col2,...)

select name from sysobjects where type='U''列出数据库里所有的表名
select name from syscolumns where id=object_id('TableName')'列出表里的所有的字段名

source data
kzx4dm xbdm jylsfsdm ...
11      2       10
11      2       10
12      2       10
12      1       20
12      1       20
destination:
kzx4dm  bys_count yjs_count jy_ratio
11        2           2        1.00
12        3           1        1/3
Select DISTINCT kzx4dm,(Select COUNT(jylsfsdm) FROM tablename Where kzx4dm=TA.kzx4dm) AS bys_count,(Select COUNT(jylsfsdm) FROM tablename Where kzx4dm=TA.kzx4dm Where jylsfsdm=10) AS yjs_count,yjs_count/bys_count AS jy_ratioFROM tablename AS TA
Select DISTINCT kzx4dm,(Select COUNT(jylsfsdm) FROM tablename Where kzx4dm=TA.kzx4dm) AS bys_count,(Select COUNT(jylsfsdm) FROM tablename Where kzx4dm=TA.kzx4dm AND jylsfsdm=10) AS yjs_count,yjs_count/bys_count AS jy_ratioFROM tablename AS TA

二维表 T(F1,F2,F3,F4,F5,F6,F7) 表示如下关系:
  学生ID    学生姓名    课程ID    课程名称     成绩      教师ID    教师姓名
    S3        王五        K4        政治        53         T4       赵老师  
    S1        张三        K1        数学        61         T1       张老师  
    S2        李四        K3        英语        88         T3       李老师  
    S1        张三        K4        政治        77         T4       赵老师  
    S2        李四        K4        政治        67         T5       周老师  
    S3        王五        K2        语文        90         T2       王老师  
    S3        王五        K1        数学        55         T1       张老师  
    S1        张三        K2        语文        81         T2       王老师  
    S4        赵六        K2        语文        59         T1       王老师  
    S1        张三        K3        英语        37         T3       李老师  
    S2        李四        K1        数学        81         T1       张老师  
  请以一句 T-SQL (Ms SQL Server) 或 Jet SQL (Ms Access) 在 原表 T 基础上作答
1.如果 T 表还有一字段 F0 数据类型为自动增量整型(唯一,不会重复),
  而且 T 表中含有除 F0 字段外,请删除其它字段完全相同的重复多余的脏记录数据:
delete from t  where f0 in(select max(f0) from t group by f1,f2,f3,f4,f5,f6,f7 having count(f0)>1)     '好像是错
Delete Legal_Dispute_Lawyer Where Lawyer_Record_No IN(Select Lawyer_Record_No FROM Legal_Dispute_Lawyer LDL Where Lawyer_Record_No(Select TOP 1 Lawyer_Record_No FROM Legal_Dispute_Lawyer Where LD=LDL.LD AND Name=LDL.Name AND Email=LDL.Email AND Phone_No=LDL.Phone_No AND Fax_No=LDL.Fax_No))
2.列印各科成绩最高和最低的记录: (就是各门课程的最高、最低分的学生和老师)
  课程ID,课程名称,最高分,学生ID,学生姓名,教师ID,教师姓名,最低分,学生ID,学生姓名,教师ID,教师姓名
select tb.f4,tb.f3,tb1.f5,tb.f1,tb.f2,tb.f6,tb.f7,tb2.f5,tb2.f1,tb2.f2,tb2.f6,tb2.f7 from t tb where
f5=(select max(f5) from t where t.f4=tb.f4)
join select f2,f7 from t tb2 where
f5=(select min(f5) from t where t.f4=tb2.f4)
on tb.f4=tb2.f4
先完成一个,想想在做下一个.
3.按成绩从高到低顺序,列印所有学生四门(数学,语文,英语,政治)课程成绩: (就是每个学生的四门课程的成绩单)
  学生ID,学生姓名,数学,语文,英语,政治,有效课程数,有效平均分
  (注: 有效课程即在 T 表中有该学生的成绩记录,如不明白可不列印"有效课程数"和"有效平均分")
select tb1.f1,tb1.f2,count(tb1.f5) as scores,sum(tb1.f5) as scoresum, avg(tb1.f5) AS average,
tb2.f5,tb3.f5,tb4.f5,tb5.f5
from t as tb1
left join t as tb2
on tb1.f0=tb2.f0 and tb2.f3=k4
left join t as tb3
on tb1.f0=tb3.f0 and tb3.f3=k3
left join t as tb4
on tb1.f0=tb4.f0 and tb4.f3=k2
left join t as tb5
on tb1.f0=tb5.f0 and tb5.f3=k1
grout by tb1.f2 order by tb1.scoresum desc
4.按各科不及格率的百分数从低到高和平均成绩从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用"N行"表示): (就是分析哪门课程难)
  课程ID,课程名称,平均成绩,及格百分数
select  f3,f4, (select count(f1) from t where t.f4=tb.f4 and f5<60)/(select count(f1) from t where t.f4=tb.f4) as failper,((select sum(f5) from t where t.f4=tb.f4)/ (select count(f5) from t where t.f4=tb.f4)) as averagescore
from t tb order by failper asc, as averagescore desc