SQLServer 表连接时使用top 1 去除重复数据
来源:互联网 发布:windows复制粘贴失效 编辑:程序博客网 时间:2024/06/09 23:30
left join SM_SOLine soline on soline.SO=so.ID and soline.DocLineNo=(select MAX(DocLineNo) from SM_SOLine where so=so.ID)
create table #test8
(
id int,
name varchar(50)
)
drop table #test9
create table #test9
(
id int,
name varchar(50)
)
insert into #test8
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc' union all
select 4,'ddd'
insert into #test9
select 3,'abc' union all
select 3,'ddd' union all
select 3,'eee' union all
select 3,'eee' union all
select 2,'ccc' union all
select 1,'ghd' union all
select 7,'hgd'
select A.id,A.name,B.name from #test8 A
inner join #test9 B on A.ID=B.ID
where not exists(select 1 from #test9 where id=B.id and name>B.name)
此处B表的Name字段必须是在B表中不重复的某字段,不然,会出现重复结果
use TestDB
drop table test8
create table test8
(
id int,
name1 varchar(50),
name2 varchar(50),
name3 varchar(50)
)
drop table test9
create table test9
(
id int,
name1 varchar(50),
name2 varchar(50),
name3 varchar(50)
)
insert into test8 --ItemMaster
select 1,'a','aaa','111' union all
select 2,'b','bbb','222' union all
select 3,'c','ccc','333' union all
select 4,'d','ddd','444' union all
select 5,'e','eee','555' union all
select 6,'f','fff','666' union all
select 7,'g','ggg','777' union all
select 8,'h','hhh','888'
insert into test9 --Gprice
select 1,'a','aaa','567' union all
select 2,'a','aaa','567' union all
select 3,'i','ccc','641' union all
select 4,'c','ccc','981' union all
select 5,'e','eee','126' union all
select 6,'f','fff','873' union all
select 7,'c','ccc','946' union all
select 8,'h','ddd','767'
--insert into test9 --Gprice
--select 1,'a','aaa','A' union all
--select 2,'a','aaa','A' union all
--select 3,'i','ccc','B' union all
--select 4,'c','ccc','C' union all
--select 5,'e','eee','D' union all
--select 6,'f','fff','E' union all
--select 7,'c','ccc','F' union all
--select 8,'h','ddd','G'
/*
1,a --
3,c --
5,e
6,f
*/
此写法会导致test8中的ID重复
select A.id,A.name1,A.name3,B.name2,B.name3 B_name3 from test8 A
inner join test9 B on A.name1=B.name1 and A.name2=B.name2
使用此方法可以解决问题:
select A.id,A.name1,A.name3,B.name2,MAX(B.name3) B_name3
from test8 A
inner join test9 B on A.name1=B.name1 and A.name2=B.name2
where not exists(select 1 from test9 where id=B.id and name3>B.name3)
group by A.id,A.name1,A.name3,B.name2
PS. B表里的name3可以是test8表里的除关键字段以外任意一个前提是有值的字段,
可以是整型,也可是字符串,都可显示正常,如下列结果
- SQLServer 表连接时使用top 1 去除重复数据
- sqlserver 去除重复的数据
- SqlServer去除重复的数据记录
- sqlserver通过ignore_dup_key索引去除重复数据
- Mysql左连接去除重复数据
- sqlserver 用到临时表去除重复列
- sqlserver去除重复记录
- SqlServer数据库同步 两张表的数据 去除重复数据
- Office 2003: 使用Excel去除重复数据
- SQLSERVER 怎样去除重复记录
- sqlserver去除重复列[行]
- SQLServer中用存储过程去除表中重复客户信息
- 填报表更新时去除重复数据
- sql 多表去除重复数据
- 去除mysql表中的重复数据
- 数据去除重复
- list去除重复数据
- 去除重复数据
- SQLServer Union 和 Union All 在Insert 语句中的不同效果
- View在屏幕中的位置
- iPhone SE销量惨淡?看看这些数据就知道了
- SQLServer inner join,left join,right join,outer join 备忘备忘
- Ubuntu搭建subversion+usvn环境
- SQLServer 表连接时使用top 1 去除重复数据
- 如何更改linux文件的拥有者及用户组(chown和chgrp)
- SQLServer 使用变量动态行转列
- 第5周项目1(5) 三角形类锥形
- SQLServer 临时表
- Mybatis MapperScannerConfigurer 自动扫描 将Mapper接口生成代理注入到Spring
- SQLServer2008 字符串函数一览表
- apk-tool 反编译
- SQLServer XXX IS NOT NULL