求一Sql语句（有关group by）

2011-03-18 08:57:46 394 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL GROUP BY 语句和SQL HAVING 子句 GROUP BY 语句可结合一些聚合函数来使用。阅读详情

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

...全文

基于稀疏表示字典学习的图像超分辨率-杨建超论文解析文章：《Image Super-Resolution via Sparse Representation》作者：杨建超信号稀疏的定义：令字典D的原子个数M，信号维度为N，则有以下三种关系： M = N时，D为完备的； M > N时，D为冗余的； M>>N时, D为过完备的。图解如下：借鉴了压缩感知的思想，通过对大量低分辨率图像和高分辨... 阅读详情

xj150145223

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

iXiaXianBing 2011-03-20

打赏
举报

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

select *

from Table1 a

where exist

	(select *

	 from Table b

	 where a.userID=b.userID

	     and b.userID=max(version)

	group by userID

	);

iXiaXianBing 2011-03-20

打赏
举报

[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]

你这个不行哦

andy_liucj 2011-03-19

打赏
举报

andy_liucj 2011-03-19

打赏
举报

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)

qgqch2008 2011-03-19

打赏
举报

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)

liang145 2011-03-19

打赏
举报



select * from #Table1 as t1 where 

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

Shawn 2011-03-18

打赏
举报

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

*/

wangxiaofeiwuqiao 2011-03-18

打赏
举报



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 )

cutebear2008 2011-03-18

打赏
举报

有错误啊，兄弟！
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]

xiaoguanzhao 2011-03-18

打赏
举报

/*建表*/
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 2011-03-18

打赏
举报

你这句应该不对，如果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

--小F-- 2011-03-18

打赏
举报

--处理表重复记录(查询和删除)

/******************************************************************************************************************************************************

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

*/

bluesmiler 2011-03-18

打赏
举报

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

michaelgong 2011-03-18

打赏
举报



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

javatemptation 2011-03-18

打赏
举报



/*

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;

Lyongt 2011-03-18

打赏
举报



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

ljking0731 2011-03-18

打赏
举报

--建表

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

xuam 2011-03-18

打赏
举报

丢了个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]

xuam 2011-03-18

打赏
举报



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

fengfeng_66 2011-03-18

打赏
举报

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

我测试了，可以

求一Sql语句（有关group by）

相关推荐