可用分押宝游戏火热进行中...	专题改版：Java Web 专题

较复杂的赋值问题，急 ,在线等

楼主geniusqing（依帆）2005-08-02 08:30:59 在 MS-SQL Server / 应用实例提问

create table test(id varchar(4),fen varchar(4), counter numeric(6,2),sign varchar(4))
insert into test
select '1','0',0,''
union all select '2','1',4.2,''
union all select '3','2',4.7,''
union all select '4','3',4.9,''
union all select '5','4',5.1,''
union all select '6','4',6.1,''
union all select '7','3',7.2,''
union all select '8','0',7.4,''
union all select '9','1',7.5,''
union all select '10','2',10,''
union all select '11','0',15,''
赋值条件如下：选取min(counter), min(counter)+1内fen<>0的fen值, 如果sum(fen)>4,则min(counter)的fen值为4，其它数据的fen值为0，
如果sum(fen)<=4,每个数据的fen值不变，就这样循环计算
例如：
2 1 4.20
3 2 4.70
4 3 4.90
5 4 5.10
---------------
6 4 6.10
--------------
7 3 7.20
9 1 7.50
-------------
10 2 10.00
想得到的结果如下：
2 4 4.20
3 0 4.70
4 0 4.90
5 0 5.10
---------------
6 4 6.10
--------------
7 3 7.20
9 1 7.50
-------------
10 2 10.00
问题点数：50、回复次数：14Top

1 楼coolingpipe（冷箫轻笛）回复于 2005-08-02 09:39:22 得分 0

select id,case when sfen>4 and id=sid then 4 when sfen>4 and id<> sid then 0 when sfen<=4 then fen end as fen,
counter,sign
from
(select t3.id,t3.fen,t3.counter,t3.sign,t4.sid,t5.sfen from test t3 inner join
(select min(t1.id) as sid,t2.id from test t1 left join test t2 on t1.counter <= t2.counter and t1.counter >t2.counter - 1
group by t2.id
)t4 on t3.id = t4.id inner join
(select sum(cast(t2.fen as int)) as sfen,t1.id from test t1 left join test t2 on t1.counter <= t2.counter and t1.counter >t2.counter - 1
group by t1.id
)t5 on t4.sid=t5.id
)t6

测试结果
----------------------------------------------------------
id fen counter sign
1 0 .00
2 4 4.20
3 0 4.70
4 0 4.90
5 0 5.10
6 4 6.10
7 3 7.20
8 0 7.40
9 1 7.50
10 2 10.00
11 0 15.00

----------------------------------------------------------Top

2 楼coolingpipe（冷箫轻笛）回复于 2005-08-02 09:43:03 得分 0

需要解释一下的是楼主所说的min(counter), min(counter)+1内我取得是
min(counter)<=值<min(counter)+1 ,因为如果两个都等于的话算法是不对的
Top

3 楼geniusqing（依帆）回复于 2005-08-02 09:54:25 得分 0

就是在1米内,如果sum(fen)>4,则min(counter)的fen值为4，其它数据的fen值为0，
如果sum(fen)<=4,每个数据的fen值不变，就这样循环计算,
4.2-5.2,6.1-7.1,7.2-8.2,10-11这样分的,就是min(counter)<=值<(min(counter)+1)
coolingpipe(冷箫轻笛) 你的这样就不行了
create table test(id varchar(4),fen varchar(4), counter numeric(6,2),sign varchar(4))
insert into test
select '1','0',0,''
union all select '2','1',5,''
union all select '3','2',5.4,''
union all select '4','3',5.7,''
union all select '5','4',5.9,''
union all select '6','4',6.1,''
union all select '7','3',7.2,''
union all select '8','0',7.4,''
union all select '9','1',7.5,''
union all select '10','2',10,''
union all select '11','0',15,''

Top

4 楼coolingpipe（冷箫轻笛）回复于 2005-08-02 10:05:01 得分 0

我的结果哪个地方有问题?Top

5 楼coolingpipe（冷箫轻笛）回复于 2005-08-02 10:09:17 得分 0

明白了,我再看看Top

6 楼jiushaoye（打倒小日本）回复于 2005-08-02 13:48:47 得分 0

select id,
fen = case when (select count(fen) from @t where counter between a.counter and a.counter + 1) >= 4 then 4
else fen
end,
counter,
[sign]
from @t a
意思应该是这样吧,不过 else 后面的部分不知道怎么写,失败呀……Top

7 楼geniusqing（依帆）回复于 2005-08-02 14:01:28 得分 0

快帮帮我Top

8 楼coolingpipe（冷箫轻笛）回复于 2005-08-02 14:07:22 得分 0

思来想去，可能一条语句实现不了
我再想... ...Top

9 楼geniusqing（依帆）回复于 2005-08-02 16:05:10 得分 0

大家来帮我想想办法Top

10 楼coolingpipe（冷箫轻笛）回复于 2005-08-02 17:40:45 得分 0

你是想查询还是更新数据库？Top

11 楼geniusqing（依帆）回复于 2005-08-02 17:42:29 得分 0

更新数据库Top

12 楼coolingpipe（冷箫轻笛）回复于 2005-08-02 18:48:22 得分 50

一定要先测试一下，看我理解的跟你需要的是否还有差距
里面有一些数据转换都是根据你提供的数据特点来写的，比如fen我都转换成了INT，如果你的数据并非如此的话应该注意一下
--------------------------------------------------------
declare @id int
declare @maxid int
declare @counter decimal(5,2)
set @id=0
select @maxid=max(cast(id as int)) from test
while (@id<=@maxid)
begin
select @counter=min(counter) from test where cast(id as int)>@id
select @id=max(cast(id as int)) from test where counter>=@counter and counter<@counter+1
update t3
set t3.fen=t4.sfen
from test t3 ,
(select t1.id,case when t2.sfen>4 and t1.counter=@counter then 4
when t2.sfen>4 and t1.counter<>@counter then 0
else t1.fen end as sfen
from test t1,(select sum(cast(fen as int)) as sfen from test where counter>=@counter and counter<@counter+1
)t2
where counter>=@counter and counter<@counter+1
)t4
where t3.id=t4.id
end
Top

13 楼coolingpipe（冷箫轻笛）回复于 2005-08-02 18:49:36 得分 0

先回家了，呵呵
明天再来看Top

14 楼coolingpipe（冷箫轻笛）回复于 2005-08-03 10:07:22 得分 0

楼主~~~~~~~~哪去了？Top