统计多表联合查询 去重复记录的方法
统计多表联合查询 去重复记录的方法
两表结构不一样，或者一样的，多个表结合起来查询的，都可以
[解决办法]
例子吗？--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL DROP TABLE [a]GOCREATE TABLE [a]([id] INT,[name] NVARCHAR(10),[parentid] INT)INSERT [a]SELECT 1,'area',0 UNION ALLSELECT 2,'category',0 UNION ALLSELECT 3,'north',1 UNION ALLSELECT 4,'south',1 UNION ALLSELECT 5,'Shanghai',4 UNION ALLSELECT 6,'Beijing',3 UNION ALLSELECT 7,'pudong',5 UNION ALLSELECT 8,'xuhui',5 UNION ALLSELECT 9,'chaoyang',6 UNION ALLSELECT 10,'desk',2 UNION ALLSELECT 11,'chair',2 UNION ALLSELECT 12,'bed',2GO--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL DROP TABLE [b]GOCREATE TABLE [b]([id] INT,[area] INT,[city] INT,[district] NVARCHAR(10))INSERT [b]SELECT 1,4,5,'pudong' UNION ALLSELECT 2,4,5,'xuhui' UNION ALLSELECT 3,4,6,'chaoyang'GO--> 生成测试数据表:cIF NOT OBJECT_ID('[c]') IS NULL DROP TABLE [c]GOCREATE TABLE [c]([id] INT,[category] INT,[area] INT,[city] INT,[district] INT)INSERT [c]SELECT 1,10,4,5,7 UNION ALLSELECT 2,10,4,5,7 UNION ALLSELECT 3,11,4,5,8 UNION ALLSELECT 4,11,3,6,9 UNION ALLSELECT 5,10,3,6,9GO-->SQL查询如下:--SELECT * FROM [a]--SELECT * FROM [b]--SELECT * FROM [c]-->SQL查询如下:select a0.name area, a1.name city, a2.name district, '' address, MAX(case a3.name when 'desk' then 数量 else 0 end) 桌子数量, MAX(case a3.name when 'chair' then 数量 else 0 end) 椅子数量, MAX(case a3.name when 'bed' then 数量 else 0 end) 床数量from ( select category,area,city,[district],COUNT(1) 数量 from c group by category,area,city,[district] ) c join a a0 on a0.id=c.area join a a1 on a1.id=c.[city] join a a2 on a2.id=c.district join a a3 on a3.id=c.category left join b on b.area=c.area and c.city=b.city group by a0.name,a1.name,a2.name/*area city district address 桌子数量 椅子数量 床数量---------- ---------- ---------- ------- ----------- ----------- -----------north Beijing chaoyang 1 1 0south Shanghai pudong 2 0 0south Shanghai xuhui 0 1 0(3 行受影响)*/
[解决办法]
--处理表重复记录(查询和删除)/******************************************************************************************************************************************************1、Num、Name相同的重复值记录,没有大小关系只保留一条2、Name相同,ID有大小关系时,保留大或小其中一个记录整理人：中国风(Roy)日期:2008.06.06******************************************************************************************************************************************************/--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)--> --> (Roy)生成測試數據 if not object_id('Tempdb..#T') is null drop table #TGoCreate table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))Insert #Tselect 1,N'A',N'A1' union allselect 2,N'A',N'A2' union allselect 3,N'A',N'A3' union allselect 4,N'B',N'B1' union allselect 5,N'B',N'B2'Go--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2方法1:Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)方法2:select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID方法3:select * from #T a where ID=(select min(ID) from #T where Name=a.Name)方法4:select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5:select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)方法6:select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0方法7:select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)方法8:select * from #T a where ID!>all(select ID from #T where Name=a.Name)方法9(注:ID为唯一时可用):select * from #T a where ID in(select min(ID) from #T group by Name)--SQL2005:方法10:select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID方法11:select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1生成结果:/*ID Name Memo----------- ---- ----1 A A14 B B1(2 行受影响)*/--II、Name相同ID最大的记录,与min相反:方法1:Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)方法2:select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID方法3:select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID方法4:select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5:select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)方法6:select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0方法7:select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)方法8:select * from #T a where ID!<all(select ID from #T where Name=a.Name)方法9(注:ID为唯一时可用):select * from #T a where ID in(select max(ID) from #T group by Name)--SQL2005:方法10:select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID方法11:select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1生成结果2:/*ID Name Memo----------- ---- ----3 A A35 B B2(2 行受影响)*/
[解决办法]
--处理表重复记录(查询和删除)/******************************************************************************************************************************************************1、Num、Name相同的重复值记录,没有大小关系只保留一条2、Name相同,ID有大小关系时,保留大或小其中一个记录整理人：中国风(Roy)日期:2008.06.06******************************************************************************************************************************************************/--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)--> --> (Roy)生成測試數據 if not object_id('Tempdb..#T') is null drop table #TGoCreate table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))Insert #Tselect 1,N'A',N'A1' union allselect 2,N'A',N'A2' union allselect 3,N'A',N'A3' union allselect 4,N'B',N'B1' union allselect 5,N'B',N'B2'Go--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2方法1:Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)方法2:select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID方法3:select * from #T a where ID=(select min(ID) from #T where Name=a.Name)方法4:select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5:select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)方法6:select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0方法7:select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)方法8:select * from #T a where ID!>all(select ID from #T where Name=a.Name)方法9(注:ID为唯一时可用):select * from #T a where ID in(select min(ID) from #T group by Name)--SQL2005:方法10:select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID方法11:select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1生成结果:/*ID Name Memo----------- ---- ----1 A A14 B B1(2 行受影响)*/--II、Name相同ID最大的记录,与min相反:方法1:Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)方法2:select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID方法3:select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID方法4:select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5:select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)方法6:select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0方法7:select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)方法8:select * from #T a where ID!<all(select ID from #T where Name=a.Name)方法9(注:ID为唯一时可用):select * from #T a where ID in(select max(ID) from #T group by Name)--SQL2005:方法10:select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID方法11:select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1生成结果2:/*ID Name Memo----------- ---- ----3 A A35 B B2(2 行受影响)*/
[解决办法]