在线寻求一SQL语句，解决马上给分！

[karach]

我的记录如下：
Client_ID Client_style Style_Count
074800 a
074800 b
074800 c
074800 c
075400 a
075400 a
075400 b
我要的结果为：
Client_ID Client_style Style_Count
074800 a 1
074800 b 2
074800 c 3
074800 c 3
075400 a 1
075400 a 1
075400 b 2
意思就是：当Client_ID相同时候（同一个客户），Client_style不同时（同一个客户），Style_Count自动加1.请问怎么做？解决马上给分！

[karach]

某些数据还是没有问题，可是有些数据还是有问题，
因为我这里的数据有3W多条，
其中某一个Client_ID 可能有上千条不同的Client_style，望执教！救命！！
可否留个邮箱，我把数据发过去！
看看到底是怎么回事？

[BoningSword]

sqlserver我帮不了你,很少用不熟悉,要是oracle的话也许还可以的.

[zjcxc]

发过来看看.
[e-mail]

[DigJim]

发过来看看.
[e-mail]

[DigJim]

我帮你写了一个存储过程，希望能帮到你！
其实只用当中的一段代码也可以了，不用建成存储过程！

--把表名换成你的表名！
create proc pMyProc
as
DECLARE @ID varchar(10),@ID_B varchar(10)
DECLARE @style varchar(10),@style_B varchar(10)
DECLARE @count int
DECLARE @strSQL varchar(1000)
SET @ID_B=''
SET @style_B=''
SET @count=0

DECLARE cur_Client CURSOR FOR
SELECT DISTINCT Client_ID,Client_style FROM 表 ORDER BY Client_ID,Client_style

OPEN cur_Client

FETCH NEXT FROM cur_Client INTO @ID,@style

WHILE @@FETCH_STATUS = 0
BEGIN
IF(@ID_B<>@ID) SET @count=1 ELSE SET @count=@count+1
SET @strSQL='UPDATE 表 SET Style_Count='+CAST(@count AS VARCHAR(200))+' WHERE Client_ID='''
+@ID+''' AND Client_style='''+@style+''''
PRINT @strSQL
EXEC(@strSQL)
SET @ID_B=@ID
SET @style_B=@style

FETCH NEXT FROM cur_Client INTO @ID,@style
END

CLOSE cur_Client
DEALLOCATE cur_Client
GO

---------调用方法-----------
EXEC pMyProc

[DigJim]

稍微修改了一下
create proc pMyProc
as
DECLARE @ID varchar(10),@ID_B varchar(10)
DECLARE @style varchar(100)
DECLARE @count int
DECLARE @strSQL varchar(1000)
SET @ID_B=''
SET @count=0

DECLARE cur_Client CURSOR FOR
SELECT DISTINCT Client_ID,Client_style FROM 表 ORDER BY Client_ID,Client_style

OPEN cur_Client

FETCH NEXT FROM cur_Client INTO @ID,@style

WHILE @@FETCH_STATUS = 0
BEGIN
IF(@ID_B<>@ID) SET @count=1 ELSE SET @count=@count+1
SET @strSQL='UPDATE 表 SET Style_Count='+CAST(@count AS VARCHAR(200))+' WHERE Client_ID='''
+@ID+''' AND Client_style='''+@style+''''
EXEC(@strSQL)
SET @ID_B=@ID

FETCH NEXT FROM cur_Client INTO @ID,@style
END

CLOSE cur_Client
DEALLOCATE cur_Client
GO

---------调用方法-----------
EXEC pMyProc

[jabmoon]

必须要用SQL语句来解决吗？

[chieftech]

mission impossible

[BoningSword]

用什么数据库

[tiexinliu]

select Client_ID,Client_style,the_Style_Count=count(*) from yourtablename
group by Client_ID,Client_style



--------------------------------------------------------------------
看尽悲伤，庸人自扰不平事。叹尽荒凉，海阔天空谁人知。狂风劲兮，百花飘
扬乱舞香。捏花一笑，海不扬波断肠心！

[karach]

是啊，必须用SQL语句完成，用的是SQL Server 2000

[karach]

to tiexinliu(铁心刘) ,你这样写我试过拉，产生的数据有问题，可否再帮我考虑一下！

[hillhx]

select * ,(select count(distinct b.id2 ) from tab1 b where b.id1 = a.id1 and b.id2 <= a.id2)
from tbl a

[hillhx]

select * ,(select count(distinct b.Client_style) from tab1 b where b.Client_ID= a.Client_ID and b.Client_style <= a.Client_style)
from tbl a

[pengdali]

declare @a int,@b varchar(10),@c varchar(10)
select @a=0,@b='',@c=''
update 你的表 set @a=case when @b<>client_id then 1 when @c<>client_style then @a+1 else @a end,@b=client_id,@c=client_style,Style_Count=@a

[pengdali]

--测试：

create table #a(Client_ID varchar(10),Client_style varchar(10),Style_Count int)
insert #a values('074800','a',null)
insert #a values('074800','b',null)
insert #a values('074800','c',null)
insert #a values('074800','c',null)
insert #a values('075400','a',null)
insert #a values('075400','a',null)
insert #a values('075400','b',null)

declare @a int,@b varchar(10),@c varchar(10)
select @a=0,@b='',@c=''
update #a set @a=case when @b<>client_id then 1 when @c<>client_style then @a+1 else @a end,
@b=client_id,@c=client_style,Style_Count=@a

select * from #a
go
drop table #a

[DigJim]

呵呵！楼上的也是正确，但是太复杂了一点，没有考虑到相同 Client_ID和Client_style不用再比较了，已经update好了。

选出数据的时候不要选出Client_ID和Client_style都相同的。这样就不用在下面的语句中比较Client_style了，直接比较Client_ID就可以了！

[chmj718]

declare @i int
declare @oldid varchar(64)
declare @oldstyle varchar(64)
declare cur_1 cursor for
select Client_ID , Client_style
from tablename
order by Client_ID , Client_style

open cur_1
set @i = 1
fetch next from cur_1 into @id,@style
set @oldid = @id
set @oldstyle = @style

while @@FETCH_STATUS = 0
begin
if @oldid = @id and @oldstyle = @style
begin
update tablename set stylecount = @i
where Client_ID = @id and Client_style =@style
end
else if @oldid = @id and @oldstyle <> @style
begin
set @i = @i + 1
update tablename set stylecount = @i
where Client_ID = @id and Client_style =@style
end
else if @oldid <> @id
begin
set @i = 1
update tablename set stylecount = @i
where Client_ID = @id and Client_style =@style

end
set @oldid = @id
set @oldstyle = @style
fetch next from cur_1 into @id,@style
end
CLOSE cur_1
DEALLOCATE cur_1

[OpenVMS]

是个老问题了，大力的正确