求教一个关于GROUP的问题

weixin_41074823 2017-11-16 10:07:57
我的数组是这样的
ID,UID,INTOTIME
1 121 2017-10-1
2 122 2017-10-1
3 122 2017-10-18
4 122 2017-11-5
5 121 2017-5-5
6 121 2017-10-1

我想每个UID只取日期最新的一条数据,怎么办
...全文
94 7 点赞 打赏 收藏 举报
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wwfxgm 2017-11-16
引用 5 楼 weixin_41074823 的回复:
抱歉,我没有表达清楚,我想每个UID只取日期最新的一条数据,并且我只取回来ID号就行了 引用版主的代码即可达到目的呀。
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[UID] int,[INTOTIME] Date)
Insert #T
select 1,121,'2017-10-1' union all
select 2,122,'2017-10-1' union all
select 3,122,'2017-10-18' union all
select 4,122,'2017-11-5' union all
select 5,121,'2017-5-5' union all
select 6,121,'2017-10-1'
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY UID ORDER BY INTOTIME DESC ) AS num
FROM    #T
)
SELECT  ID 
FROM    cte
WHERE   num = 1
  • 打赏
  • 举报
回复
二月十六 2017-11-16
引用 5 楼 weixin_41074823 的回复:
抱歉,我没有表达清楚,我想每个UID只取日期最新的一条数据,并且我只取回来ID号就行了
只取id就只select id就行了
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[UID] int,[INTOTIME] Date)
Insert #T
select 1,121,'2017-10-1' union all
select 2,122,'2017-10-1' union all
select 3,122,'2017-10-18' union all
select 4,122,'2017-11-5' union all
select 5,121,'2017-5-5' union all
select 6,121,'2017-10-1'
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY UID ORDER BY INTOTIME DESC ) AS num
FROM    #T
)
SELECT  ID
FROM    cte
WHERE   num = 1
  • 打赏
  • 举报
回复
听雨停了 2017-11-16

use Tempdb
go
--> --> 听雨停了-->测试数据
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([ID] int,[UID] int,[INTOTIME] Date)
Insert #tab
select 1,121,'2017-10-1' union all
select 2,122,'2017-10-1' union all
select 3,122,'2017-10-18' union all
select 4,122,'2017-11-5' union all
select 5,121,'2017-5-5' union all
select 6,121,'2017-10-1'
--测试数据结束

;WITH cte AS (
SELECT *,
		--相同日期只取一条
       ROW_NUMBER()OVER(PARTITION BY uid ORDER BY intotime DESC) AS rn
       --相同日期取多条
       --dense_rank()OVER(PARTITION BY uid ORDER BY intotime DESC) AS rn
FROM   #tab
)
SELECT id,uid,intotime FROM cte WHERE rn=1

id          uid         intotime
----------- ----------- ----------
1           121         2017-10-01
4           122         2017-11-05
  • 打赏
  • 举报
回复
weixin_41074823 2017-11-16
抱歉,我没有表达清楚,我想每个UID只取日期最新的一条数据,并且我只取回来ID号就行了
  • 打赏
  • 举报
回复
二月十六 2017-11-16
如果要ID的话
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[UID] int,[INTOTIME] Date)
Insert #T
select 1,121,'2017-10-1' union all
select 2,122,'2017-10-1' union all
select 3,122,'2017-10-18' union all
select 4,122,'2017-11-5' union all
select 5,121,'2017-5-5' union all
select 6,121,'2017-10-1'
Go
--测试数据结束
;WITH cte AS (
SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY UID ORDER BY INTOTIME DESC ) AS num
FROM #T
)
SELECT ID ,
UID ,
INTOTIME
FROM cte
WHERE num = 1


  • 打赏
  • 举报
回复
顺势而为1 2017-11-16


Select *
From Table a
Where INTOTIME=(Select max(INTOTIME) From Table b Where b.UID=a.UID)

  • 打赏
  • 举报
回复
二月十六 2017-11-16
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[UID] int,[INTOTIME] Date)
Insert #T
select 1,121,'2017-10-1' union all
select 2,122,'2017-10-1' union all
select 3,122,'2017-10-18' union all
select 4,122,'2017-11-5' union all
select 5,121,'2017-5-5' union all
select 6,121,'2017-10-1'
Go
--测试数据结束
SELECT UID ,
MAX(INTOTIME) AS INTOTIME
FROM #T
GROUP BY UID


  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
加入

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2017-11-16 10:07
社区公告
暂无公告