有这样一张业务资料表:
CREATE TABLE [dbo].[JlMingdan] (
[UseId] [varchar] (50) , '用户ID
[RegTime] [smalldatetime] NULL ,'提交时间
[RegCshu] [int] NULL ,'提交次序
[SpCount] [float] NULL , '积分
[JhFlag] [char] (1) ,等级(分0、A、B、C、D四级)
[Cname] [varchar] (50) NULL ,'中文名
[Phone] [varchar] (50) NULL ,
[Email] [varchar] (100) NULL ,
[state] [varchar] (50) NULL ,
[city] [varchar] (50) NULL ,
[street] [varchar] (200) NULL ,
)
用户每月只能提交一次,按提交的次数确定用户的等级和积分。每月用户提交的资料都已存在表中。
现在要统计的是:挑选出每位用户积分最高的一次的资料,按积分高低取前50名,积分相同的按提交时间先后排。有一点头痛的是同一用户的最高分不同记录中会相同。
我的问题是:能不能用一条SQL语句查出这个结果,或用什么简单的方法?
CREATE TABLE [dbo].[JlMingdan] (
[UseId] [varchar] (50) , '用户ID
[RegTime] [smalldatetime] NULL ,'提交时间
[RegCshu] [int] NULL ,'提交次序
[SpCount] [float] NULL , '积分
[JhFlag] [char] (1) ,等级(分0、A、B、C、D四级)
[Cname] [varchar] (50) NULL ,'中文名
[Phone] [varchar] (50) NULL ,
[Email] [varchar] (100) NULL ,
[state] [varchar] (50) NULL ,
[city] [varchar] (50) NULL ,
[street] [varchar] (200) NULL ,
)
用户每月只能提交一次,按提交的次数确定用户的等级和积分。每月用户提交的资料都已存在表中。
现在要统计的是:挑选出每位用户积分最高的一次的资料,按积分高低取前50名,积分相同的按提交时间先后排。有一点头痛的是同一用户的最高分不同记录中会相同。
我的问题是:能不能用一条SQL语句查出这个结果,或用什么简单的方法?
from jlmingdan
group by useid
order by spcount desc,regtime asc
from jlmingdan
group by useid
order by spcount desc,regtime asc
select * from
{ select top 50 max(spcount) as spcount,min(regtime) as regtime
from jlmingdan
group by useid
order by spcount desc,regtime asc
} P
order by P.SpCount Desc
( select top 50 max(spcount) as spcount,min(regtime) as regtime
from jlmingdan
group by useid
order by spcount desc,regtime asc
) P
order by P.SpCount Desc
这样也不行,只能得到max(spcount) as spcount,min(regtime) as regtime这两项内容啊!
select a.*
from jlmingdan as a inner join
(select top 50 max(spcount) as spcount,min(regtime),min(id) as regtime
from jlmingdan
group by useid
order by spcount desc,regtime asc) as b on a.id=b.id
select a.*
from jlmingdan as a inner join
(select top 50 max(spcount) as spcount,min(regtime),min(id) as regtime
from jlmingdan
group by useid
order by spcount desc,regtime asc) as b on a.id=b.id
这个也不行啊,报错,找不到b.id列!
from JlMingdan as jr,(
select useid, max(spcount)as spcount, max(regcshu)as regcshu from (
select j.useid , j.spcount,regcshu from (
select useid, max(spcount)as spcount from jlmingdan group by useid
)as data, jlmingdan as j
where j.useid = data.useid and j.spcount = data.spcount
) as tempdata group by useid
)as datar
where jr.useid = datar.useid and jr.spcount = datar.spcount and jr.regcshu = datar.regcshu
order by jr.spcount desc, regtime asc
select a.*
from jlmingdan as a inner join
(select top 50 max(spcount) as spcount,min(regtime),min(id) as regtime
from jlmingdan
group by useid
order by spcount desc,regtime asc) as b on a.id=b.id
这个也不行啊,报错,找不到b.id列!----------------------------------------
晕,自己都不会检查下
select top 50 max(spcount) as spcount,min(regtime),min(id) as regtime
--------------》
select top 50 max(spcount) as spcount,min(regtime) as regtime,min(id) as id
[SQL Server]没有为第2列(属于'b')指定列!
[SQL Server]没有为第2列(属于'b')指定列!--------------------------------------------
认真检查下看,min(regtime)没命名别名(as ...)
select top 50 max(spcount) as spcount,min(regtime) as regtime,min(id) as id
from jlmingdan as a inner join
(select top 50 max(spcount) as spcount,min(regtime),userid
from jlmingdan
group by useid
order by spcount desc,regtime asc) as b on a.userid=b.userid