Go语言视频零基础入门到精通

一度

测试数据:

use master     
go   
if exists(select * from sysdatabases where name='test')   
drop database test   
go   
create database test   
go   
use test   
go   
create table tab(id int,number int)   
go   
insert into tab values(1,1)   
insert into tab values(2,1)   
insert into tab values(3,5)   
insert into tab values(4,20)   
insert into tab values(5,20)   
insert into tab values(6,100)  
use master go if exists(select * from sysdatabases where name='test') drop database test go create database test go use test go create table tab(id int,number int) go insert into tab values(1,1) insert into tab values(2,1) insert into tab values(3,5) insert into tab values(4,20) insert into tab values(5,20) insert into tab values(6,100)


select * from tab结果集：
id     number
1      1
2      1
3      5
4      20
5      20
6      100


--------------------------------------------------------------------------------




数据有了，下面看题目
1.过滤number重复数据,如下：
id     number
1      1
3      5
4      20
6      100

思路：
      按number分组，然后取出id最小值，根据id查询数据表
实现代码1：

select * from tab where id in(select min(id) from tab group by number)  
select * from tab where id in(select min(id) from tab group by number)


实现代码2：

select a.* from tab as a inner join (select min(id) as 'id' from tab group by number) as b   
on a.id=b.id  
select a.* from tab as a inner join (select min(id) as 'id' from tab group by number) as b on a.id=b.id



--------------------------------------------------------------------------------




2.删除number重复数据，保留一条，如下：
id     number
1      1
3      5
4      20
6      100

思路：
      按number分组重复数据大于1，然后取出id最大值，根据id删除
实现代码：

delete from tab where id in(select max(id) from tab group by number having count(*)>1)  
delete from tab where id in(select max(id) from tab group by number having count(*)>1)


注意以上代码了么？是可以实现删除，但是是如果有3条重复的数据怎么办？这条语句只能删除一条，为了让删除语句更通用，更强大，看下面
思路：
      先求出重复数据大于1，id最小值，然后过滤此id，根据number匹配删除。
实现代码：

delete from tab where id in(select a.id from tab as a inner join (select min(id) as id,number from tab group by number having count(*)>1) as b on a.id>b.id and a.number=b.number)  
delete from tab where id in(select a.id from tab as a inner join (select min(id) as id,number from tab group by number having count(*)>1) as b on a.id>b.id and a.number=b.number)



--------------------------------------------------------------------------------




3.将number重复数据全部删除，如下：
id     number
3      5
6      100
思路：
      按number分组重复数据大于1，取出number，根据number删除
实现代码：

delete from tab where number in(select number from tab group by number having count(*)>1)  
delete from tab where number in(select number from tab group by number having count(*)>1)



--------------------------------------------------------------------------------




4.将number重复数据全部更新为0，如下：
id     number
1      0
2      0
3      5
4      0
5      0
6      100

思路：
      按number分组重复数据大于1，取出number，根据number更新
实现代码：

update tab set number=0 where number in(select number from tab group by number having count(*)>1)  

zj905