sql2005中的with,和row_number

with a as(
select * from table_1 where a>1
)
select * from a
SELECT ROW_NUMBER()OVER (PARTITION BY b order by b) as v,* from table_1

 

with可以省去了建临时表的麻烦

 

row_number PARTITION BY是分区统计的意思

上面搜出来的结果

v   b

1   a

2   a

3   a

1   b

1   c

把大于1的都删掉就可以删除表里b的重复项了

with 和 row_number 结合起来做分页就很方便了