求教一个sql select 语句问题

[狼异族]

现有一个table

A(name varchar(10), record int, date int)

现有多条记录 如下

'小甲',10, 20161209 
'小甲',10, 20161108 
'小甲',9, 20161008 
'小乙',10, 20161209 
'小乙',10, 20161108 
'小乙',10, 20161007

我想筛选出 所有 以

partition by name order by date desc

分组中record发生变动的日期，上面的列子筛选出的日期就有

20161007  --小乙从无到有
20161008 --小甲从无到有
20161108 --小甲record发生变化

问下这样的select语句怎么写啊？

[chen8410]

create table A(name varchar(10), record int, date int)

insert into A(name,record,date)
values ('小甲',9, 20161211), 
('小甲',10, 20161209), 
('小甲',10, 20161108), 
('小甲',9, 20161008), 
('小乙',10, 20161209), 
('小乙',10, 20161108), 
('小乙',10, 20161007)

;with CTE
as(select *,ROW_NUMBER() over(partition by name order by date) rn from A)

select a.* 
from CTE a left join CTE b on a.rn=b.rn+1 and a.name=b.name
where a.record<>b.record or b.record is null

--查询结果
name	record	date	rn
小甲	9	20161008	1
小甲	10	20161108	2
小甲	9	20161211	4
小乙	10	20161007	1

[卖水果的net]

你的要的结果，就是一列日期吗 ？ 建议楼主一个比较精确的结果出来；

[xxfvba]

--不知道理解的对不对 with A(name , record , date ) as (select '小甲',10, 20161209 union all select '小甲',10, 20161108 union all select '小甲',9, 20161008 union all select '小乙',10, 20161209 union all select '小乙',10, 20161108 union all select '小乙',10, 20161007) select date from (select *,rn=ROW_NUMBER() over (Partition by name,record order by date) from A) a where rn=1 order by date

[狼异族]

引用 2 楼 xxfvba 的回复:

--不知道理解的对不对 with A(name , record , date ) as (select '小甲',10, 20161209 union all select '小甲',10, 20161108 union all select '小甲',9, 20161008 union all select '小乙',10, 20161209 union all select '小乙',10, 20161108 union all select '小乙',10, 20161007) select date from (select *,rn=ROW_NUMBER() over (Partition by name,record order by date) from A) a where rn=1 order by date

这个有点问题，比如下面的数据

[狼异族]

'小乙',9, 20161209 
'小乙',10, 20161108 
'小乙',9, 20161007

这三个日期都应该被选出来的，但是你给的语句只能选出 20161108 20161007

[中国风]

e.g.

with CTET (name , record , date ) as
(
SELECT  '小甲' ,
        10 ,
        20161209
UNION ALL
SELECT  '小甲' ,
        10 ,
        20161108
UNION ALL
SELECT  '小甲' ,
        9 ,
        20161008
UNION ALL
SELECT  '小乙' ,
        9 ,
        20161209
UNION ALL
SELECT  '小乙' ,
        10 ,
        20161108
UNION ALL
SELECT  '小乙' ,
        9 ,
        20161007
)

SELECT * FROM (SELECT *,LAG(record,1,0)OVER(PARTITION BY name ORDER BY date) AS record2 FROM CTET) AS t WHERE record<>record2

 
 /*
 name	record	date	record2
小甲	9	20161008	0
小甲	10	20161108	9
小乙	9	20161007	0
小乙	10	20161108	9
小乙	9	20161209	10
*/