110,534
社区成员
发帖
与我相关
我的任务
分享
select id,time,[卡号] from (select *,ROW_NUMBER() over (partition by [卡号] order by [time] desc) as rw
from [table1] ) as t1 where rw = 1 and id =2
用这个会有问题吗?[/quote]
select id,time,[卡号] from (select *,ROW_NUMBER() over (partition by [卡号] order by [time] desc) as rw
from [table1] ) as t1 where rw = 1 and id =2
这个是oracle的写法,如果是sqlserver,还是用#26这样写吧
select ta.ID, ta.time, ta.[卡号] from (select 卡号,min(time) as time from [your table] group by 卡号 ) as tb
left join [your table] as ta
on ta.卡号=tb.卡号 and ta.time=tb.time
[/quote]
select id,time,[卡号] from (select *,ROW_NUMBER() over (partition by [卡号] order by [time] desc) as rw
from [table1] ) as t1 where rw = 1 and id =2
可是跟这条语句的实现功能不一样;select id,time,[卡号] from (select *,ROW_NUMBER() over (partition by [卡号] order by [time] desc) as rw
from [table1] ) as t1 where rw = 1 and id =2
用这个会有问题吗?[/quote]
select id,time,[卡号] from (select *,ROW_NUMBER() over (partition by [卡号] order by [time] desc) as rw
from [table1] ) as t1 where rw = 1 and id =2
这个是oracle的写法,如果是sqlserver,还是用#26这样写吧
select ta.ID, ta.time, ta.[卡号] from (select 卡号,min(time) as time from [your table] group by 卡号 ) as tb
left join [your table] as ta
on ta.卡号=tb.卡号 and ta.time=tb.time
select id,time,[卡号] from (select *,ROW_NUMBER() over (partition by [卡号] order by [time] desc) as rw
from [table1] ) as t1 where rw = 1 and id =2
我用着这个,如下结果:
表:table1
ID time 卡号 分数
1 2016-7 05 65
2 2016-8 01 70
2 2016-6 01 78
2 2016-5 03 66
4 2016-3 02 75
查询结果如下:
ID time 卡号
2 2016-8 01
2 2016-5 03
select id,time,[卡号] from (select *,ROW_NUMBER() over (partition by [卡号] order by [time] desc) as rw
from [table1] ) as t1 where rw = 1 and id =2
用这个会有问题吗?select tb.ID, tb.time, tb.[卡号] from (select min(ID) as ID from [your table] group by [卡号]) as ta
left join [your table] as tb
on ta.ID=tb.ID
这样的关系运算即可。SQL Server 会自动将“In”运算改为关系运算符,所以你如果了解普通的关系运算(left join、inner join)那么对 sql 的知识就更加完备。
如果要取每一个“卡号”第一次发生的准确时间,可以这样写select ta.ID, ta.time, ta.[卡号] from (select 卡号,min(time) as time from [your table] group by 卡号 ) as tb
left join [your table] as ta
on ta.卡号=tb.卡号 and ta.time=tb.time
第二个跟第一个其实非常类似。会了第一个,就很容易理解第二个查询。[/quote]
太无耻了居然抄我的。。我要告老师!
select id,time,[卡号] from (
select *,ROW_NUMBER() over (partition by [卡号] order by [time] desc) as rw
from [table1] ) as t1
where rw = 1;
参考资料:SQLServer数据去重问题
select tb.ID, tb.time, tb.[卡号] from (select min(ID) as ID from [your table] group by [卡号]) as ta
left join [your table] as tb
on ta.ID=tb.ID
这样的关系运算即可。SQL Server 会自动将“In”运算改为关系运算符,所以你如果了解普通的关系运算(left join、inner join)那么对 sql 的知识就更加完备。
如果要取每一个“卡号”第一次发生的准确时间,可以这样写select ta.ID, ta.time, ta.[卡号] from (select 卡号,min(time) as time from [your table] group by 卡号 ) as tb
left join [your table] as ta
on ta.卡号=tb.卡号 and ta.time=tb.time
第二个跟第一个其实非常类似。会了第一个,就很容易理解第二个查询。selec * from(select * from table1 order by 分数 desc) temp group by 分数