如下
要求取出不重复单品 号码最大值 按照单品排序
最好用一条语句
部门 单品 供应商 名称 号码 订货量
10 10000010 P087 841 20111228 6
10 10000025 ZR2H 723 20111230 6
10 10000025 ZR2H A20 20120105 30
10 10000025 ZR2H 721 20111223 6
10 10000025 ZR2H 715 20111202 6
10 10000025 ZR2H 719 20111216 30
10 10000025 ZR2H A22 20120109 30
10 10000025 ZR2H A27 20120201 30
10 10000025 ZR2H 731 20120127 18
10 10000025 ZR2H A24 20120119 30
10 10000025 ZR2H A29 20120221 180
10 10000099 ZR2H 736 20120210 40
10 10000099 ZR2H A24 20120119 20
10 10000099 ZR2H A28 20120220 40
10 10000099 ZR2H 731 20120127 20
10 10000099 ZR2H A27 20120201 20
10 10000099 ZR2H 724 20111230 4
10 10000099 ZR2H 726 20120106 8
10 10000099 ZR2H 727 20120113 8
要求取出不重复单品 号码最大值 按照单品排序
最好用一条语句
部门 单品 供应商 名称 号码 订货量
10 10000010 P087 841 20111228 6
10 10000025 ZR2H 723 20111230 6
10 10000025 ZR2H A20 20120105 30
10 10000025 ZR2H 721 20111223 6
10 10000025 ZR2H 715 20111202 6
10 10000025 ZR2H 719 20111216 30
10 10000025 ZR2H A22 20120109 30
10 10000025 ZR2H A27 20120201 30
10 10000025 ZR2H 731 20120127 18
10 10000025 ZR2H A24 20120119 30
10 10000025 ZR2H A29 20120221 180
10 10000099 ZR2H 736 20120210 40
10 10000099 ZR2H A24 20120119 20
10 10000099 ZR2H A28 20120220 40
10 10000099 ZR2H 731 20120127 20
10 10000099 ZR2H A27 20120201 20
10 10000099 ZR2H 724 20111230 4
10 10000099 ZR2H 726 20120106 8
10 10000099 ZR2H 727 20120113 8
解决方案 »
- Sql设置了int类型的主键,为什么使用相同的值时不会报错?
- 删除现有的数据库备份(不让它自动备份了,怎么办??
- 必电脑名称后出现的问题~~~~
- 是不是SQL不要使用where abc<>''
- 如何将一列拆分成几列?
- 为何此处为空不可以?
- SQL查询分析器可以连接上,但是ADO连接不上,急待解决!!!
- 关于游标的问题
- 请问出现:“未与信任sql server连接相关联”错误的可能原因是什么?
- 我想学数据库开发但不知道学哪一门语言好,各位大虾帮帮我。送上我仅有的29分。
- 请大家帮我看一下这个SQL查询为什么是这样写的:“查询至少购买了01号客户购买的所有产品的客户号”?
- VC连接sqlite数据库
select * from bk_dpo2
join (select orderno from bk_dpo2 group by orderno having count(*) > 1)
select max(orderno) orderno , itemcode from bk_dpo2
group by itemcode,ordernoselect distinct itemcode
from bk_dpo2
where (select max (orderno) from bk_dpo2)
select 单品,max(号码) 号码 from t3 group by 单品 order by 单品 desc
-----------------------------------------
单品 号码
----------- -----------
10000099 20120220
10000025 20120221
10000010 20111228(3 行受影响)
select * from(
select id=row_number() over(partition by 单品 order by 号码 desc),部门,单品,名称,号码,订货量 from t3)tmp
where id=1 order by 号码 desc
---------------------------------------id 部门 单品 名称 号码 订货量
-------------------- ----------- ----------- ----- ----------- -----------
1 10 10000025 A29 20120221 180
1 10 10000099 A28 20120220 40
1 10 10000010 841 20111228 6(3 行受影响)
use DBTest
go
if OBJECT_ID('tabTest') is not null drop table tabTest
go
create table tabTest
(
SP int ,--单品
Code int --号码
)
go
insert into tabTest
select 1,20111228 union all
select 1,20111230 union all
select 2,20110101 union all
select 2,20110102
--Exist方式
select * from tabTest a
where not exists
(select 1 from tabTest b where a.Code<b.Code and a.SP=b.SP)
order by SP
--Row_Number方式
select * from
(
select *,ROW_NUMBER() over(partition by SP order by Code desc) as Num
from tabTest
) as b
where b.Num=1
ORDER BY b.SP
--Max方式
select SP,MAX(Code) as Code
from tabTest
group by SP
order by SP
--处理表重复记录(查询和删除)
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个记录--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 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 A1
4 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 A3
5 B B2(2 行受影响)
*/--2、删除重复记录有大小关系时,保留大或小其中一个记录
--> --> (Roy)生成測試數據if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #T a where exists(select 1 from #T where Name=a.Name and ID<a.ID)方法2:
delete a from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null方法3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select min(ID)from #T group by Name)方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)方法7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)select * from #T生成结果:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1(2 行受影响)
*/
--II、Name相同ID保留最大的一条记录:方法1:
delete a from #T a where exists(select 1 from #T where Name=a.Name and ID>a.ID)方法2:
delete a from #T a left join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null方法3:
delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select max(ID)from #T group by Name)方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID desc)方法7:
delete a from #T a where ID<any(select ID from #T where Name=a.Name)
select * from #T
/*
ID Name Memo
----------- ---- ----
3 A A3
5 B B2(2 行受影响)
*/--3、删除重复记录没有大小关系时,处理重复值
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'
Go方法1:
if object_id('Tempdb..#') is not null
drop table #
Select distinct * into # from #T--排除重复记录结果集生成临时表#truncate table #T--清空表insert #T select * from # --把临时表#插入到表#T中--查看结果
select * from #T/*
Num Name
----------- ----
1 A
2 B(2 行受影响)
*/--重新执行测试数据后用方法2
方法2:alter table #T add ID int identity--新增标识列
go
delete a from #T a where exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录
go
alter table #T drop column ID--删除标识列--查看结果
select * from #T/*
Num Name
----------- ----
1 A
2 B(2 行受影响)*/--重新执行测试数据后用方法3
方法3:
declare Roy_Cursor cursor local for
select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1
declare @con int,@Num int,@Name nvarchar(1)
open Roy_Cursor
fetch next from Roy_Cursor into @con,@Num,@Name
while @@Fetch_status=0
begin
set rowcount @con;
delete #T where Num=@Num and Name=@Name
set rowcount 0;
fetch next from Roy_Cursor into @con,@Num,@Name
end
close Roy_Cursor
deallocate Roy_Cursor--查看结果
select * from #T
/*
Num Name
----------- ----
1 A
2 B(2 行受影响)
*/