[color=black][/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][/color]
如果记录完全相同才算重复记录
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][/color]