34,575
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #temp (id int,ip varchar(11),state int)
insert into #temp
select 1,'192.168.1.1',0 union all
select 2,'192.168.1.1',0 union all
select 3,'192.168.1.2',0 union all
select 4,'192.168.1.4',0 union all
select 5,'192.168.1.1',0 union all
select 6,'192.168.1.3',0 union all
select 7,'192.168.1.3',0 union all
select 8,'192.168.1.1',0 union all
select 9,'192.168.1.2',0
GO
--SQL:
;WITH cte AS
(
SELECT
rowno = DENSE_RANK() OVER(ORDER BY ip)%3+1, *
FROM #temp
)
UPDATE cte
SET [state] = rowno
--RESULT
SELECT * FROM #temp
ORDER BY [state], ip
/*
6 192.168.1.3 1
7 192.168.1.3 1
8 192.168.1.1 2
1 192.168.1.1 2
2 192.168.1.1 2
5 192.168.1.1 2
4 192.168.1.4 2
3 192.168.1.2 3
9 192.168.1.2 3
*/
create table tb(id int,ip varchar(15),state int)
insert into tb values(1 ,'192.168.1.1', 0)
insert into tb values(2 ,'192.168.1.1', 0)
insert into tb values(3 ,'192.168.1.2', 0)
insert into tb values(4 ,'192.168.1.4', 0)
insert into tb values(5 ,'192.168.1.1', 0)
insert into tb values(6 ,'192.168.1.3', 0)
insert into tb values(7 ,'192.168.1.3', 0)
insert into tb values(8 ,'192.168.1.1', 0)
insert into tb values(9 ,'192.168.1.2', 0)
go
update tb
set state = ((n.px - 1) % 3) + 1
from tb m,
(select t.* , px = DENSE_RANK() OVER(order by ip) from tb t) n
where m.id = n.id
select * from tb
/*
id ip state
----------- --------------- -----------
1 192.168.1.1 1
2 192.168.1.1 1
3 192.168.1.2 2
4 192.168.1.4 1
5 192.168.1.1 1
6 192.168.1.3 3
7 192.168.1.3 3
8 192.168.1.1 1
9 192.168.1.2 2
(9 行受影响)
*/
drop table tb
declare @t table (id int,ip varchar(11),state int)
insert into @t
select 1,'192.168.1.1',0 union all
select 2,'192.168.1.1',0 union all
select 3,'192.168.1.2',0 union all
select 4,'192.168.1.4',0 union all
select 5,'192.168.1.1',0 union all
select 6,'192.168.1.3',0 union all
select 7,'192.168.1.3',0 union all
select 8,'192.168.1.1',0 union all
select 9,'192.168.1.2',0
select a.id,a.ip,b.rid from @t a
left join (
select ip,ntile(3) over (order by ip) as rid from @t
group by ip)
b on a.ip=b.ip
/*
id ip rid
----------- ----------- --------------------
1 192.168.1.1 1
2 192.168.1.1 1
3 192.168.1.2 1
4 192.168.1.4 3
5 192.168.1.1 1
6 192.168.1.3 2
7 192.168.1.3 2
8 192.168.1.1 1
9 192.168.1.2 1
*/
create table tb(id int,ip varchar(15),state int)
insert into tb values(1 ,'192.168.1.1', 0)
insert into tb values(2 ,'192.168.1.1', 0)
insert into tb values(3 ,'192.168.1.2', 0)
insert into tb values(4 ,'192.168.1.4', 0)
insert into tb values(5 ,'192.168.1.1', 0)
insert into tb values(6 ,'192.168.1.3', 0)
insert into tb values(7 ,'192.168.1.3', 0)
insert into tb values(8 ,'192.168.1.1', 0)
insert into tb values(9 ,'192.168.1.2', 0)
go
update tb
set state = ((((select count(distinct ip) from tb where ip < t.ip) + 1) -1) % 3) + 1
from tb t
select * from tb
/*
id ip state
----------- --------------- -----------
1 192.168.1.1 1
2 192.168.1.1 1
3 192.168.1.2 2
4 192.168.1.4 1
5 192.168.1.1 1
6 192.168.1.3 3
7 192.168.1.3 3
8 192.168.1.1 1
9 192.168.1.2 2
(所影响的行数为 9 行)
*/
drop table tb