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 --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 行受影响)
select * from tb t where not exists(select 1 from tb whre 姓名=t.姓名 and 分数>t.分数) 1楼这样才标准!! 小F讲得详细了
select 姓名,max(科目) ,max(分数) from tab t where not exists(select 1 from tab where t.科目=科目 and t.分数>分数) group by 姓名
DECLARE @T2 TABLE(姓名 nvarchar(10),科目 nvarchar(10),分数 decimal(4,2)) insert into @T2 values('张三' , '语文' , 74) insert into @T2 values('张三' , '数学' , 83) insert into @T2 values('张三' , '物理' , 93) insert into @T2 values('李四' , '语文' , 74) insert into @T2 values('李四' , '数学' , 84) insert into @T2 values('李四' , '物理' , 94)select * from @T2 a where not exists(select 1 from @T2 where a.姓名=姓名 and 分数>a.分数)姓名 科目 分数 ---------- ---------- ------------------ 张三 物理 93.00 李四 物理 94.00(2 行受影响)
select * from tab t where not exists(select 1 from tab where t.科目=科目 and t.分数>分数)
学习高手们DECLARE @T2 TABLE(姓名 nvarchar(10),科目 nvarchar(10),分数 decimal(4,2)) insert into @T2 values('张三' , '语文' , 74) insert into @T2 values('张三' , '数学' , 83) insert into @T2 values('张三' , '物理' , 93) insert into @T2 values('李四' , '语文' , 74) insert into @T2 values('李四' , '数学' , 84) insert into @T2 values('李四' , '物理' , 94)select * from @T2 t where not exists (select 1 from @T2 a where t.姓名=a.姓名 and a.分数>t.分数) order by 分数 desc
if object_id('tab') is not null drop table tab go create table tab(sname nvarchar(100),score nvarchar(100),gender nvarchar(100)) insert tab select 'aa','英语','30' union all select 'aa','中文','80' union all select 'bb','英语','8' union all select 'bb','物理','90'select * from tabselect max(gender),sname,score=(select top 1 score from tab a where a.sname=b.sname order by gender desc) from tab b group by sname
select b.name,a.subject,b.score from tb a right join (select name,max(score)as score from tb group by name)as b on a.name = b.name
更正 select b.name,a.subject,b.score from tb a join (select name,max(convert(int,score))as score from tb group by name)as b on a.name = b.name and a.score = b.score
建表就不發了,下面是自己跑的不知道能否滿足你的需求select DCC.names,a.yy,DCC.xx from(select names, max(yy)as yy from DCC group by names)a inner join DCC on DCC.names=a.names and DCC.yy=a.yy order by DCC.names 執行結果: 小A 93 物理 小B 98 物理 小C 85 數學 小D 86 數學 小E 84 英語 小F 86 物理
改下:select * from tb a where not exists(select 1 from tb a whre 姓名=a.姓名 and 科目=a.科目 and 分数>a.分数)
/******************************************************************************************************************************************************
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 #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
--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 行受影响)
1楼这样才标准!!
小F讲得详细了
group by 姓名
insert into @T2 values('张三' , '语文' , 74)
insert into @T2 values('张三' , '数学' , 83)
insert into @T2 values('张三' , '物理' , 93)
insert into @T2 values('李四' , '语文' , 74)
insert into @T2 values('李四' , '数学' , 84)
insert into @T2 values('李四' , '物理' , 94)select *
from @T2 a
where not exists(select 1 from @T2 where a.姓名=姓名 and 分数>a.分数)姓名 科目 分数
---------- ---------- ------------------
张三 物理 93.00
李四 物理 94.00(2 行受影响)
insert into @T2 values('张三' , '语文' , 74)
insert into @T2 values('张三' , '数学' , 83)
insert into @T2 values('张三' , '物理' , 93)
insert into @T2 values('李四' , '语文' , 74)
insert into @T2 values('李四' , '数学' , 84)
insert into @T2 values('李四' , '物理' , 94)select * from @T2 t where not exists (select 1 from @T2 a where t.姓名=a.姓名 and a.分数>t.分数) order by 分数 desc
drop table tab
go
create table tab(sname nvarchar(100),score nvarchar(100),gender nvarchar(100))
insert tab
select 'aa','英语','30' union all
select 'aa','中文','80' union all
select 'bb','英语','8' union all
select 'bb','物理','90'select * from tabselect max(gender),sname,score=(select top 1 score from tab a where a.sname=b.sname order by gender desc) from tab b group by sname
from tb a right join
(select name,max(score)as score from tb group by name)as b on a.name = b.name
select b.name,a.subject,b.score
from tb a join
(select name,max(convert(int,score))as score from tb group by name)as b on a.name = b.name and a.score = b.score
inner join DCC on DCC.names=a.names and DCC.yy=a.yy order by DCC.names
執行結果:
小A 93 物理
小B 98 物理
小C 85 數學
小D 86 數學
小E 84 英語
小F 86 物理