sql 应用

[/color][color=white][color=black][color=white]Sqlserver 删除重复的记录 :
如果记录完全相同才算重复记录

select distinct * into #tmpp from tid
delete from tid
insert into tid select * from #tmpp
drop table #tmpp

有id主键(数字,自增1的), Sqlserve查询 并 删除重复的记录
有表  test
create table test(
[id] int primary key identity(1,1),
[name] varchar(20),
[age] int
) 
插入1000条重复的记录
declare @n int
set @n=0

while(1=1)
begin
set @n=@n+1
if(@n=1000)
break
else
insert into test values('jade',21)
end;

利用子查询 查出重复的记录
select * from test where age in (select age from test group by age having count(age)>1) order by age
删除
delete from test where age in (select age from test group by age having count(age)>1)

ms sql Convert函数的使用

SELECT SUBSTRING(title, 1, 2) AS Title, author
FROM bookinfo
WHERE CONVERT(char(20), price) LIKE '7%'
GO
select m_id,decode(m_sex,'0','女','1','男') m_sex from mess

查出薪水最高的3位 
select rownum,b_time,b_salary from (select b_time,b_salary from b order by b_salary desc) where rownum<=3;

rownum后面至可以用<或<=号,不可以用=,>号和其它的比较符号

删除重复的数据

delete a where a_id in (select a_id from a group by a_id having count(a_id)>1);

增加一列
alter table b add b_salary float;

查出第某(2)行的数据
select b_time,b_salary from(select rownum a,c.* from b c) where a=2;

查询出 第某(3)行到某(5)行的数据
select b_time,b_salary from(select rownum a,c.* from b c) where a between 3 and 5;

找出那些工资高于他们所在部门的平均工资的员工
select b_id,b_salary,b_part from b c where b_salary>(select avg(b_salary) from b where b_part=c.b_part);


select examno,性别=case sex
when '0' then '女'
when '1' then '男'
else '人妖'
end
from marks

select examno,(writenexam+labexam)/2 as '平均分',
'是否合格'=case 
when (writenexam+labexam)/2>70
then 'pass'
when (writenexam+labexam)/2<70
then 'fail' end,age,
性别=case sex
when '0' then '女'
when '1' then '男'
end,
状态=case sate
when '1' then '上班'
when '2' then '请假'
else '辞职' end
from marks
order by age[/color][/color]