求教一个关于GROUP的问题

[weixin_41074823]

我的数组是这样的
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只取日期最新的一条数据，怎么办

[二月十六]

--测试数据
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

[顺势而为1]

Select *
From Table a
Where INTOTIME=(Select max(INTOTIME) From Table b Where b.UID=a.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 ,
        UID ,
        INTOTIME
FROM    cte
WHERE   num = 1

[weixin_41074823]

抱歉，我没有表达清楚，我想每个UID只取日期最新的一条数据，并且我只取回来ID号就行了

[听雨停了]

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

[二月十六]

引用 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

[wwfxgm]

引用 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