表:
Id UserId PlaceId SignTIme
1 1 2 2011-9-1
2 1 2 2011-9-2
3 1 3 2011-9-9
4 1 4 2011-9-10
5 1 8 2011-9-13
6 2 8 2011-9-13
7 3 9 2011-9-13需要过滤(UserId,PlaceId)重复的数据项,并且根据(SignTime )倒序排序.
用Distinct过滤的话,就报“如果指定了 SELECT DISTINCT,那么 ORDER BY 子句中的项就必须出现在选择列表中。”的错误,如果把SignTime放大哦选择列中,就不能过滤重复数据了。
Select Distinct PlaceId,UserId From dbo.TBL_MPSignIn_UserSign Order By SignTIme Desc用Group By 过滤又要吧Order By 的字段放到Group By 列中同样不能过滤从父数据 报错:“ORDER BY 子句中的列 "dbo.TBL_MPSignIn_UserSign.SignTime" 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。”
Select UserId,PlaceId From dbo.TBL_MPSignIn_UserSign Group By UserId,PlaceId Order By SignTIme Desc用字查询,就会报:“除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。”的错误
Select Distinct PlaceId,UserId From (Select * From dbo.TBL_MPSignIn_UserSign Order By SignTIme Desc) A谁有好的办法,技能过滤掉重复的数据,有能按照非过滤列进行排序? 多谢
Id UserId PlaceId SignTIme
1 1 2 2011-9-1
2 1 2 2011-9-2
3 1 3 2011-9-9
4 1 4 2011-9-10
5 1 8 2011-9-13
6 2 8 2011-9-13
7 3 9 2011-9-13需要过滤(UserId,PlaceId)重复的数据项,并且根据(SignTime )倒序排序.
用Distinct过滤的话,就报“如果指定了 SELECT DISTINCT,那么 ORDER BY 子句中的项就必须出现在选择列表中。”的错误,如果把SignTime放大哦选择列中,就不能过滤重复数据了。
Select Distinct PlaceId,UserId From dbo.TBL_MPSignIn_UserSign Order By SignTIme Desc用Group By 过滤又要吧Order By 的字段放到Group By 列中同样不能过滤从父数据 报错:“ORDER BY 子句中的列 "dbo.TBL_MPSignIn_UserSign.SignTime" 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。”
Select UserId,PlaceId From dbo.TBL_MPSignIn_UserSign Group By UserId,PlaceId Order By SignTIme Desc用字查询,就会报:“除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。”的错误
Select Distinct PlaceId,UserId From (Select * From dbo.TBL_MPSignIn_UserSign Order By SignTIme Desc) A谁有好的办法,技能过滤掉重复的数据,有能按照非过滤列进行排序? 多谢
From dbo.TBL_MPSignIn_UserSign
Group By UserId,PlaceId
Order By max(SignTIme) Desc
create table #tt(id int,userid int,placeid int,signtime varchar(10))
goinsert into #tt select 1,1,2,'2011-9-1'
insert into #tt select 2,1,2,'2011-9-2'
insert into #tt select 3,1,3,'2011-9-9'
insert into #tt select 4,1,4,'2011-9-10'
insert into #tt select 5,1,8,'2011-9-13'
insert into #tt select 6,2,8,'2011-9-13'
insert into #tt select 7,3,9,'2011-9-13'select * from #tt t
where not exists(select 1 from #tt where t.userid=userid and t.placeid=placeid and t.signtime<signtime)
id userid placeid signtime
----------- ----------- ----------- ----------
2 1 2 2011-9-2
3 1 3 2011-9-9
4 1 4 2011-9-10
5 1 8 2011-9-13
6 2 8 2011-9-13
7 3 9 2011-9-13(6 行受影响)
From dbo.TBL_MPSignIn_UserSign
Group By UserId,PlaceId --------到这里都是在去重复
Order By max(SignTIme) Desc --按照SIGNTIME值最大的排序