SQL语句求助

Question

表 t:
id      ip                 state
1       192.168.1.1        0
2       192.168.1.1        0
3       192.168.1.2        0
4       192.168.1.4        0
5       192.168.1.1        0
6       192.168.1.3        0
7       192.168.1.3        0
8       192.168.1.1        0
9       192.168.1.2        0
我想把它分成3组，分组条件：相同的ip不能分在两组，一组可以有多个不同的ip。
分组后再按组修改state,例如：第一组的state 修改为1，第二组的state修改为2，第三组的state修改为3
修改后的数据：
表 t:
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        2
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
或者
表 t:
id      ip                 state
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数据随便改成什么样都行，只要符合以上条件
怎么写Sql语句

Answer 1

SQL SERVER 2000 还是2005+？

Answer 2

--sql 2000用子查询完成.
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)
goupdate tb 
set state = ((((select count(distinct ip) from tb where ip < t.ip) + 1) -1) % 3) + 1
from tb tselect * 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

Answer 3

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',0select 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
*/

Answer 4

--sql 2005用DENSE_RANK() OVER()完成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)
goupdate 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.idselect * 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

Answer 5

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
*/