求超复杂SQL语句（分数不够，可以再加）(300)

原始数据库：姓名 数量 IPs1 10 127.0.0.1s3 8 192.168.1.168s3 3 192.168.1.168s2 8 192.168.1.158s3 12 192.168.1.158s4 16 192.168.1.158s5 20 127.0.0.1s4 15 127.0.0.1输出结果：IP 姓名 IP重复次数 重复数量127.0.0.1 s1,s5,s4 3 45192.168.1.158 s2,s3,s4 3 36192.168.1.168 s3 2 11要求：查询IP重复度SQL语句，并找出同一IP的姓名，IP重复次数 和 重复数量 并按IP重复次数排序

IP IP重复次数 重复数量这三列好办,相信你也会select ip,count(*),sum(数量)from tgroup by ip 姓名但姓名,这种表示方式,除了用cursor,我想不出什么好办法,如果你能接受的话,下面是我的代码declare @t1 table([ip] nvarchar(50),[IP重复次数] int,[重复数量] int,[姓名] nvarchar(255))declare @Ip nvarchar(50),@name nvarchar(20),@namestr nvarchar(250)insert @t1(ip,IP重复次数,重复数量)select ip,count(*),sum(数量)from ttgroup by ipDECLARE ip_cursor CURSOR FORselect ip from @t1OPEN ip_cursorFETCH NEXT FROM ip_cursor INTO @ipWHILE @@FETCH_STATUS = 0BEGIN set @namestr='' DECLARE name_cursor CURSOR FOR select 姓名 from tt where ip=@ip OPEN name_cursor FETCH NEXT FROM name_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN set @namestr=@namestr+@name+',' FETCH NEXT FROM name_cursor INTO @name end CLOSE name_cursor DEALLOCATE name_cursor update @t1 set 姓名=substring(@namestr,1,len(@namestr)-1) where ip= @ip FETCH NEXT FROM ip_cursor INTO @ipendCLOSE ip_cursor DEALLOCATE ip_cursorselect * from @t1

to 枝上柳绵可以用cursor但上面的代码，我在SQL 2000 查询分析器里，得不到结果不知哪儿出问题了。

示例：create table t([姓名] varchar(2),[数量] int,[IP] varchar(13))insert tselect 's1',10,'127.0.0.1' union allselect 's3',8,'192.168.1.168' union allselect 's3',3,'192.168.1.168' union allselect 's2',8,'192.168.1.158' union allselect 's3',12,'192.168.1.158' union allselect 's4',16,'192.168.1.158' union allselect 's5',20,'127.0.0.1' union allselect 's4',15,'127.0.0.1'1. 创建视图create view t1as select distinct IP,[姓名] from t2. 创建函数create function F_Str(@Col1 varchar(100)) returns nvarchar(4000) as begin declare @S nvarchar(4000) select @S=isnull(@S+',','')+ [姓名] from t1 where [IP]=@Col1 order by [姓名] return @S end 3.查询Select distinct [IP],[姓名]=dbo.F_Str(IP),(select COUNT([姓名]) from t where [IP]=a.[IP]) [IP重复次数],(select SUM([数量]) from t where [IP]=a.[IP]) [重复数量]from t a--查询结果：/*IP 姓名 IP重复次数 重复数量127.0.0.1 s1,s4,s5 3 45192.168.1.158 s2,s3,s4 3 36192.168.1.168 s3 2 11*/

我的表结构,表名(tt) 姓名 nvarchar 50 1 数量 int 4 1 ip nvarchar 50 1我的操作系统 xp sp2 ,sql2000运行通过的kaida写的比较优雅,呵呵

非常感谢 枝上柳绵 和 kaida