简单问题不简单

[tomyuansir]

declare @a table (id int identity(1,1) ,x int,y int,shopid int)
insert @a
select 1,1,1
union all
select 1,2,1
union all
select 2,1,1
union all
select 2,2,1
union all
select 2,5,2
union all
select 1,8,3
union all
select 1,7,3
union all
select 3,2,4
union all
select 3,1,4
union all
select 3,3,4
union all
select 4,1,4
union all
select 4,3,4
union all
select 4,2,4


select * from @a
需要结果：
x	y	shopid	       id	
1	1	1		1
1	2	1		2
2	1	1		3
2	2	1		4	
2	5	2		1	
1	8	3		2
1	7	3		1
3	2	4		5
3	1	4		4
3	3	4		6
4	1	4		1
4	3	4		3
4	2	4		2
1>找出数据的规律
2>实现如上效果

[-晴天]

xhopid,x,y,id排序

[-晴天]

select x,y,shopid,k=id-(select count(*) from @a where shopid<=a.shopid-1) from @a a order by shopid,x,id,y
/*
x           y           shopid      k
----------- ----------- ----------- -----------
1           1           1           1
1           2           1           2
2           1           1           3
2           2           1           4
2           5           2           1
1           8           3           1
1           7           3           2
3           2           4           1
3           1           4           2
3           3           4           3
4           1           4           4
4           3           4           5
4           2           4           6
*/

??????

[-晴天]

select x,y,shopid,id=id-(select count(*) from @a where shopid<=a.shopid-1)+case when shopid=4 and x=3 then 3 else 0 end+case when shopid=4 and x=4 then -3 else 0 end from @a a order by shopid,x,id,y
/*
x           y           shopid      id
----------- ----------- ----------- -----------
1           1           1           1
1           2           1           2
2           1           1           3
2           2           1           4
2           5           2           1
1           8           3           1
1           7           3           2
3           2           4           4
3           1           4           5
3           3           4           6
4           1           4           1
4           3           4           2
4           2           4           3

(13 行受影响)

*/

[Larry]

select a.*,b.rnk from @a as a
inner join
(select x,y,shopid,rank() over(partition by shopid order by x,y) as rnk from @a) as b
on a.x = b.x and a.y = b.y and a.shopid = b.shopid order by a.shopid,a.x,a.id