求一Sql语句（有关group by）

[xj150145223]

Table1
userID version score
1 1.0 2
1 2.0 3
1 3.0 4
2 2.0 1
2 4.0 3

求所有USERID 的最大version对应的Score

要求最后获得的是
1 3.0 4
2 4.0 3

[fengfeng_66]

select a.userid,a.version,a.score from table1 as a inner join (select b.userid, max(version) as cc from table1 as b group by b.userid) as b on a.userid = b.userid and a.verson = b.cc

我测试了，可以

[xuam]

select userID, version, score from table1 version in (select max(version)from table1 group by userID) 

[xuam]

丢了个where

select userID, version, score from table1 where version in (select max(version) from table1 group by userID)

[Quote=引用 1 楼 xuam 的回复:]
SQL code

select userID, version, score from table1 version in (select max(version)from table1 group by userID)
[/Quote]

[ljking0731]

--建表
create table #table1 (userid int, version float, score int)

insert into #table1
select 1,1.0,2
union all select 1,2.0,3
union all select 1,3.0,4
union all select 2,2.0,1
union all select 2,4.0,3

--结果
select * from #table1 where checksum(userid,version) in
(
select max(checksum(userid,version)) from #table1
group by userid
)

userid      version                score
----------- ---------------------- -----------
1           3                      4
2           4                      3

(2 行受影响)

[Lyongt]

Declare @T Table(
	userID		Int,
	version		Decimal(8,1), 
	score		Int

)

Insert Into @T
select 1,1.0,2
union all select 1,2.0,3
union all select 1,3.0,4
union all select 2,2.0,1
union all select 2,4.0,3


Select T0.UserID, T0.Version, T0.Score 
From @T T0 Inner Join (
	Select UserID, Max(Version) Ver 
	From @T  
	Group By UserID
) T1 On (T0.USerID = T1.UserID And T0.Version = T1.Ver)
Order By T0.UserID

[javatemptation]

/*
create table Table1
(
	userID int not null,
	[version] numeric(5,1) not null,
	score int not null
);
insert into Table1(userID,[version],score)
values
(1,1.0,2),
(1,2.0,3),
(1,3.0,4),
(2,2.0,1),
(2,4.0,3);
*/
--求所有USERID 的最大version对应的Score
select userID,MAX([version]) as [version],MAX(score) as score
from table1
group by userID;

[michaelgong]

select distinct t.userid,t.version,t.score from table1 t 
right join (select MAX(version) as version,userid as kk from table1 group by userid)b on t.version=b.version
userid      version                score
----------- ---------------------- -----------
1           3                      4
2           4                      3

(2 行受影响)

[bluesmiler]

select userid,version,score from
(select *,row_number() over (partition by userid order by version desc) id from tb1) a
where a.id=1

[--小F--]

--处理表重复记录(查询和删除)
/******************************************************************************************************************************************************
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


--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 行受影响)
*/

[cutebear2008]

你这句应该不对，如果UserID＝2 也有version 3.0呢！

select a.userID, a.version, score from tb a,(select userID,max(version) as version from tb group by userID)b where a.userID=b.userID and a.version=b.version

[Quote=引用 2 楼 xuam 的回复:]
丢了个where

SQL code

select userID, version, score from table1 where version in (select max(version) from table1 group by userID)




引用 1 楼 xuam 的回复:
SQL code

select userID, version, sco……
[/Quote]

[xiaoguanzhao]

/*建表*/
create table table1
(
userID varchar(8),
version varchar(8),
score integer
)
/*造数*/
insert into table1 values('1','1.0',2);
insert into table1 values('1','2.0',3);
insert into table1 values('1','3.0',4);
insert into table1 values('2','2.0',1);
insert into table1 values('2','4.0',3);


/*查询ＳＱＬ*/
select * from table1 where (userid,version) in (select userid,max(version) from table1 group by userid) ;

[cutebear2008]

有错误啊，兄弟！
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

[Quote=引用 10 楼 xiaoguanzhao 的回复:]
/*建表*/
create table table1
(
userID varchar(8),
version varchar(8),
score integer
)
/*造数*/
insert into table1 values('1','1.0',2);
insert into table1 values('1','2.0',3);
insert into table1 ……
[/Quote]

[wangxiaofeiwuqiao]

select * from table1 where version in 
(select max(version) from table1 group by userid)

select * from table1 a 
where not exists (select top 1 userid,version from table1 b where a.userid=b.userid and a.version<b.version )

[Shawn]

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
	DROP TABLE #temp
create table #temp (userid int, version VARCHAR(10), score int)
insert into #temp
select 1,1.0,2
union all select 1,'2.0',3
union all select 1,'3.0',4
union all select 2,'2.0',1
union all select 2,'4.0',3
GO
--SQL:
SELECT b.* FROM
(SELECT DISTINCT userid FROM #temp) a
CROSS APPLY
(SELECT TOP(1) * FROM #temp ORDER BY score DESC) b
/*
userid      version    score
----------- ---------- -----------
1           3.0        4
1           3.0        4
*/

[liang145]

select * from #Table1 as t1 where 
not exists(select 1 from #Table1 as t2  where t2.Userid=t1.userid and t2.version>t1.version)

[qgqch2008]

DECLARE @t TABLE ([userID] INT,[version] FLOAT,[score] INT)
INSERT @t
SELECT 1,1.0, 2 UNION ALL
SELECT 1,2.0, 3 UNION ALL
SELECT 1,3.0, 4 UNION ALL
SELECT 2,2.0, 1 UNION ALL 
SELECT 2,4.0, 3

--方法一：
SELECT [userID],[version],[score] FROM @t WHERE version IN (SELECT MAX(version) FROM @t GROUP BY userID)
--方法二：
SELECT * FROM @t a WHERE NOT EXISTS (SELECT 1 FROM @t b WHERE a.userID=b.userID AND a.version < b.version)

[andy_liucj]

create table table1
(
	userID int,
	version decimal(2,1),
	score int
)

insert table1
select 1, 1.0, 2 union all
select 1, 2.0, 3 union all
select 1, 3.0, 4 union all
select 2, 2.0, 1 union all
select 2, 4.0, 3

select userID, version, score from table1 t1 where version = (select max(version) from table1 t2 where t1.userID=t2.userID group by userID)

select userID, version, score from table1 t1 where not exists (select 1 from table1 t2 where t1.userid=t2.userid and t1.version<t2.version)

[andy_liucj]

[Quote=引用 2 楼 xuam 的回复:]
丢了个where

SQL code

select userID, version, score from table1 where version in (select max(version) from table1 group by userID)




引用 1 楼 xuam 的回复:
SQL code

select userID, version, sco……
[/Quote]你子查询和主查询之间userid都不关联，肯定不对的

[iXiaXianBing]

[Quote=引用 2 楼 xuam 的回复:]
丢了个where

SQL code

select userID, version, score from table1 where version in (select max(version) from table1 group by userID)




引用 1 楼 xuam 的回复:
SQL code

select userID, version, sco……
[/Quote]

你这个不行哦

[iXiaXianBing]

楼主来看哈我这个咋样...

select *
from Table1 a
where exist
	(select *
	 from Table b
	 where a.userID=b.userID
	     and b.userID=max(version)
	group by userID
	);