请问怎么更新三区段比字段的数据？

红球按1~11，12~22，23~33分为三个区段，如截图这两条数据应该显示都为6:0:0

CLOSE DATABASES all
USE ssq
SCAN
    STORE 0 TO h1,h2,h3
    FOR i=2 TO 7
        DO CASE
            CASE BETWEEN(EVALUATE(FIELD(i)),1,11)
                h1=h1+1
            CASE BETWEEN(EVALUATE(FIELD(i)),12,22)
                h2=h2+1
            CASE BETWEEN(EVALUATE(FIELD(i)),23,33)
                h3=h3+1
        ENDCASE
    ENDFOR
    REPLACE 三区段比 WITH STR(h1,1)+":"+STR(h2,1)+":"+STR(h3,1)
ENDSCAN

/*
-- 测试数据 sql2000 语法
create table #cp(序号 int,红球1 int,红球2 int,红球3 int,红球4 int,红球5 int,红球6 int,篮球 int,段比 varchar(20))
insert into #cp
select 1,1,2,3,4,5,6,8,'' union all
select 1,1,2,3,4,25,26,8,''
*/

-- 1.查询出结果
select 序号,红球1,红球2,红球3,红球4,红球5,红球6,篮球,
       cast(case when 红球1 between 1 and 11 then 1 else 0 end +
            case when 红球2 between 1 and 11 then 1 else 0 end +
            case when 红球3 between 1 and 11 then 1 else 0 end +
            case when 红球4 between 1 and 11 then 1 else 0 end +
            case when 红球5 between 1 and 11 then 1 else 0 end +
            case when 红球6 between 1 and 11 then 1 else 0 end as varchar(2))+':'+
       cast(case when 红球1 between 12 and 22 then 1 else 0 end +
            case when 红球2 between 12 and 22 then 1 else 0 end +
            case when 红球3 between 12 and 22 then 1 else 0 end +
            case when 红球4 between 12 and 22 then 1 else 0 end +
            case when 红球5 between 12 and 22 then 1 else 0 end +
            case when 红球6 between 12 and 22 then 1 else 0 end as varchar(2))+':'+
       cast(case when 红球1 between 23 and 33 then 1 else 0 end +
            case when 红球2 between 23 and 33 then 1 else 0 end +
            case when 红球3 between 23 and 33 then 1 else 0 end +
            case when 红球4 between 23 and 33 then 1 else 0 end +
            case when 红球5 between 23 and 33 then 1 else 0 end +
            case when 红球6 between 23 and 33 then 1 else 0 end as varchar(2)) as 段比
from #cp

-- 2.根据上面结果,更新数据,正常情况,上面步骤是不要的.
update a set a.段比=b.段比 from #cp as a
inner join (select 序号,红球1,红球2,红球3,红球4,红球5,红球6,篮球,
       cast(case when 红球1 between 1 and 11 then 1 else 0 end +
            case when 红球2 between 1 and 11 then 1 else 0 end +
            case when 红球3 between 1 and 11 then 1 else 0 end +
            case when 红球4 between 1 and 11 then 1 else 0 end +
            case when 红球5 between 1 and 11 then 1 else 0 end +
            case when 红球6 between 1 and 11 then 1 else 0 end as varchar(2))+':'+
       cast(case when 红球1 between 12 and 22 then 1 else 0 end +
            case when 红球2 between 12 and 22 then 1 else 0 end +
            case when 红球3 between 12 and 22 then 1 else 0 end +
            case when 红球4 between 12 and 22 then 1 else 0 end +
            case when 红球5 between 12 and 22 then 1 else 0 end +
            case when 红球6 between 12 and 22 then 1 else 0 end as varchar(2))+':'+
       cast(case when 红球1 between 23 and 33 then 1 else 0 end +
            case when 红球2 between 23 and 33 then 1 else 0 end +
            case when 红球3 between 23 and 33 then 1 else 0 end +
            case when 红球4 between 23 and 33 then 1 else 0 end +
            case when 红球5 between 23 and 33 then 1 else 0 end +
            case when 红球6 between 23 and 33 then 1 else 0 end as varchar(2)) as 段比
 from #cp) b
 on a.序号=b.序号

-- 查看更新后的结果
select * from #cp

110万条数据，用SQL语句什么时候能出结果

不知道...,下午测试1下,分别用这2种

* 在VFP 9.0中测试结果
IF USED("彩票")
   SELECT 彩票
ELSE
   USE 彩票 IN 0
ENDIF
* 生成测试数据,只用了10秒
LOCAL i
*!*    i=1
*!*    RAND(-1)
*!*    FOR i=11 TO 1100000
*!*    INSERT INTO 彩票 VALUES (i,PADL(CEILING(RAND()*33),2,"0"),PADL(CEILING(RAND()*33),2,"0"), ;
*!*                               PADL(CEILING(RAND()*33),2,"0"),PADL(CEILING(RAND()*33),2,"0"), ;
*!*                               PADL(CEILING(RAND()*33),2,"0"),PADL(CEILING(RAND()*33),2,"0"), ;
*!*                               PADL(CEILING(RAND()*33),2,"0"), "")
*!*    ENDFOR
*!*    BROWSE

* win7 32 4G内存,双核2.4,3分钟都内出结果
* 为了去除公式计算,去掉了 EVALUATE()
LOCAL h1,h2,h3,s1
*!*    s1=SECONDS()
*!*     SCAN
*!*         STORE 0 TO h1,h2,h3
*!*         h1 = h1 + IIF(BETWEEN(红球1,'01','11'),1,0) + ;
*!*                   IIF(BETWEEN(红球2,'01','11'),1,0) + ;
*!*                   IIF(BETWEEN(红球3,'01','11'),1,0) + ;
*!*                   IIF(BETWEEN(红球4,'01','11'),1,0) + ;
*!*                   IIF(BETWEEN(红球5,'01','11'),1,0) + ;
*!*                   IIF(BETWEEN(红球6,'01','11'),1,0)
*!*         h2 = h2 + IIF(BETWEEN(红球1,'12','22'),1,0) + ;
*!*                   IIF(BETWEEN(红球2,'12','22'),1,0) + ;
*!*                   IIF(BETWEEN(红球3,'12','22'),1,0) + ;
*!*                   IIF(BETWEEN(红球4,'12','22'),1,0) + ;
*!*                   IIF(BETWEEN(红球5,'12','22'),1,0) + ;
*!*                   IIF(BETWEEN(红球6,'12','22'),1,0)               
*!*         h3 = h3 + IIF(BETWEEN(红球1,'23','33'),1,0) + ;
*!*                   IIF(BETWEEN(红球2,'23','33'),1,0) + ;
*!*                   IIF(BETWEEN(红球3,'23','33'),1,0) + ;
*!*                   IIF(BETWEEN(红球4,'23','33'),1,0) + ;
*!*                   IIF(BETWEEN(红球5,'23','33'),1,0) + ;
*!*                   IIF(BETWEEN(红球6,'23','33'),1,0)   
*!*         REPLACE 段比 WITH STR(h1,1)+":"+STR(h2,1)+":"+STR(h3,1)
*!*     ENDSCAN
*!*     ?SECONDS()-s1
*!*     
*!*     BROWSE

* win7 32 4G内存,双核2.4,大概60秒
s1=SECONDS()
update a set a.段比=b.段比 from 彩票 as a ;
 inner join (select 序号,红球1,红球2,红球3,红球4,红球5,红球6,篮球, ;
       tran(iif(between(红球1 ,'01','11'),1,0) + ;
            iif(between(红球2 ,'01','11'),1,0) + ;
            iif(between(红球3 ,'01','11'),1,0) + ;
            iif(between(红球4 ,'01','11'),1,0) + ;
            iif(between(红球5 ,'01','11'),1,0) + ;
            iif(between(红球6 ,'01','11'),1,0)) + ":" +  ;
       tran(iif(between(红球1 ,'12','22'),1,0) + ;
            iif(between(红球2 ,'12','22'),1,0) + ;
            iif(between(红球3 ,'12','22'),1,0) + ;
            iif(between(红球4 ,'12','22'),1,0) + ;
            iif(between(红球5 ,'12','22'),1,0) + ;
            iif(between(红球6 ,'12','22'),1,0)) + ":" +  ;
       tran(iif(between(红球1 ,'23','33'),1,0) + ;
            iif(between(红球2 ,'23','33'),1,0) + ;
            iif(between(红球3 ,'23','33'),1,0) + ;
            iif(between(红球4 ,'23','33'),1,0) + ;
            iif(between(红球5 ,'23','33'),1,0) + ;
            iif(between(红球6 ,'23','33'),1,0)) as 段比 ;
 from 彩票) as b ;
 on a.序号=b.序号

?SECONDS()-s1
BROWSE

-- 测试数据 sql2000 语法
-- create table 彩票(序号 int,红球1 varchar(2),红球2 varchar(2),红球3 varchar(2),红球4 varchar(2),红球5 varchar(2),红球6 varchar(2),篮球 varchar(2),段比 varchar(10))
-- create index 彩票_ind on 彩票(序号)
/*
-- 生成测试数据,用了13分钟
declare @i int
set @i=1
while @i<1100000
begin
 insert into 彩票
 select @i,right('00'+cast(ceiling(rand()*33) as varchar(2)),2),
           right('00'+cast(ceiling(rand()*33) as varchar(2)),2),
           right('00'+cast(ceiling(rand()*33) as varchar(2)),2),
           right('00'+cast(ceiling(rand()*33) as varchar(2)),2),
           right('00'+cast(ceiling(rand()*33) as varchar(2)),2),
           right('00'+cast(ceiling(rand()*33) as varchar(2)),2),
           right('00'+cast(ceiling(rand()*33) as varchar(2)),2),''
 set @i= @i+1
end


select * from 彩票
*/


-- 查询更新,第1次,用了55秒,第2,3,4次,只用了25秒
update a set a.段比=b.段比 from 彩票 as a
 inner join (select 序号,红球1,红球2,红球3,红球4,红球5,红球6,篮球,
        cast(case when 红球1 between '01' and '11' then 1 else 0 end +
             case when 红球2 between '01' and '11' then 1 else 0 end +
             case when 红球3 between '01' and '11' then 1 else 0 end +
             case when 红球4 between '01' and '11' then 1 else 0 end +
             case when 红球5 between '01' and '11' then 1 else 0 end +
             case when 红球6 between '01' and '11' then 1 else 0 end as varchar(2))+':'+
        cast(case when 红球1 between '12' and '22' then 1 else 0 end +
             case when 红球2 between '12' and '22' then 1 else 0 end +
             case when 红球3 between '12' and '22' then 1 else 0 end +
             case when 红球4 between '12' and '22' then 1 else 0 end +
             case when 红球5 between '12' and '22' then 1 else 0 end +
             case when 红球6 between '12' and '22' then 1 else 0 end as varchar(2))+':'+
        cast(case when 红球1 between '23' and '33' then 1 else 0 end +
             case when 红球2 between '23' and '33' then 1 else 0 end +
             case when 红球3 between '23' and '33' then 1 else 0 end +
             case when 红球4 between '23' and '33' then 1 else 0 end +
             case when 红球5 between '23' and '33' then 1 else 0 end +
             case when 红球6 between '23' and '33' then 1 else 0 end as varchar(2)) as 段比
 from 彩票) b
  on a.序号=b.序号

--select * from 彩票

斑竹,我测试了1下,好像用SQL比VFP的 scan ....快很多,
欢迎大家测试
1.VFP 建立110W测试数据,只用了10秒,但SQL建立测试数据,用了13分钟
2.VFP 用扫描方式,我测试了3分钟,还没结束,大概更新到70W数据,用VFP SQL方式,用时大概60秒
3.在SQL2000中,第1次更新,大概用了55秒,第2,3,4次,更新,只用了25秒.

VFP 表结构(序号 i,红球1 v(2),红球2 v(2),红球3 v(2),红球4 v(2),红球5 v(2),红球6 v(2),篮球 v(2),段比 v(10))

[此贴子已经被作者于2018-4-10 17:27编辑过]

试下下面测试数据生成需要多少时间
* 生成双色球原始数据表
CREATE CURSOR t1 (x c(2))
FOR i=1 TO 33
    INSERT INTO t1 VALUES (PADL(i,2,"0"))
ENDFOR
SELECT a.x h1,b.x h2,c.x h3,d.x h4,e.x h5,f.x h6,SPACE(10) hh FROM t1 a;
INNER JOIN t1 b ON b.x>a.x ;
INNER JOIN t1 c ON c.x>b.x ;
INNER JOIN t1 d ON d.x>c.x ;
INNER JOIN t1 e ON e.x>d.x ;
INNER JOIN t1 f ON f.x>e.x ORDER BY 1,2,3,4,5,6 INTO TABLE ssq

* 用8楼的表，生成测试数据,只用了10秒
LOCAL i
*!*    i=1
*!*    RAND(-1)
*!*    FOR i=11 TO 1100000
*!*    INSERT INTO 彩票 VALUES (i,PADL(CEILING(RAND()*33),2,"0"),PADL(CEILING(RAND()*33),2,"0"), ;
*!*                               PADL(CEILING(RAND()*33),2,"0"),PADL(CEILING(RAND()*33),2,"0"), ;
*!*                               PADL(CEILING(RAND()*33),2,"0"),PADL(CEILING(RAND()*33),2,"0"), ;
*!*                               PADL(CEILING(RAND()*33),2,"0"), "")
*!*    ENDFOR
*!*    BROWSE

* 但在SQL2000中，要13分钟