| 网站首页 | 业界新闻 | 群组 | 人才 | 下载频道 | 博客 | 代码贴 | 编程论坛
雷速体育发帖软件开发QQ118000023C语言培训|一对一辅导|零基础学编程LightningChart 快速先进的.Net图表控件
共有 384 人关注过本帖
标题:请问怎么更新表中最后一字段的数据?
只看楼主 收藏
吹水佬
Rank: 16Rank: 16Rank: 16Rank: 16
等 级:版主
威 望:145
帖 子:5923
专家分:25849
注 册:2014-5-20
  得分:0 
试了一下用 REPLACE ...... ALL 大概27秒左右
测试环境:
    CPU: Core i5-3470 3.2G 4核
    RAM:4G
     OS:Windows 10 专业版 32位
    VFP:9.0
测试数据:
* 生成双色球原始数据表
    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
    CLOSE DATABASES ALL
    RETURN
测试代码:
USE ssq
PRIVATE hh1, hh2, hh3
s1 = SECONDS()
REPLACE hh WITH _fun() ALL
? SECONDS() - s1
GO TOP
BROWSE

FUNCTION _fun()
    STORE 0 TO hh1, hh2, hh3
    hh1 = hh1 + IIF(BETWEEN(h1,"01","11"),1,0) + ;
        IIF(BETWEEN(h2,"01","11"),1,0) + ;
        IIF(BETWEEN(h3,"01","11"),1,0) + ;
        IIF(BETWEEN(h4,"01","11"),1,0) + ;
        IIF(BETWEEN(h5,"01","11"),1,0) + ;
        IIF(BETWEEN(h6,"01","11"),1,0)
    hh2 = hh2 + IIF(BETWEEN(h1,"12","22"),1,0) + ;
        IIF(BETWEEN(h2,"12","22"),1,0) + ;
        IIF(BETWEEN(h3,"12","22"),1,0) + ;
        IIF(BETWEEN(h4,"12","22"),1,0) + ;
        IIF(BETWEEN(h5,"12","22"),1,0) + ;
        IIF(BETWEEN(h6,"12","22"),1,0)
    hh3 = hh3 + IIF(BETWEEN(h1,"23","33"),1,0) + ;
        IIF(BETWEEN(h2,"23","33"),1,0) + ;
        IIF(BETWEEN(h3,"23","33"),1,0) + ;
        IIF(BETWEEN(h4,"23","33"),1,0) + ;
        IIF(BETWEEN(h5,"23","33"),1,0) + ;
        IIF(BETWEEN(h6,"23","33"),1,0)
    RETURN STR(hh1,1)+":"+STR(hh2,1)+":"+STR(hh3,1)
ENDFUNC
2018-04-11 08:52
mywisdom88
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等 级:版主
威 望:78
帖 子:2490
专家分:6038
注 册:2015-3-25
  得分:0 
**由于我用SQL更新时,要用到唯一的序号,所以,我改变表结构,同样是1107568条记录数据


IF USED("彩票")
   SELECT 彩票
ELSE
   USE 彩票 IN 0
ENDIF

SELECT 彩票
*ZAP

LOCAL i,S1
*!*    i=1
*!*    RAND(-1)
*!*    S1=SECONDS()
*!*    FOR i=1 TO 1107568
*!*    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
*!*    ?"创建数据时间=",SECONDS()-S1
*!*    BROWSE
*!*    RETURN



* win7 32 4G内存,双核2.4,SQL 更新方式 ,58.5秒,58.2秒
GO TOP
s1=SECONDS()

update a set a.段比=b.段比 from 彩票 as a ;
 inner join (select 序号, ;
       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.序号

?" SQL更新时间=",SECONDS()-s1
***********************************************************

* win7 32 4G内存,双核2.4,VFP 更新方式 ,62.7秒,63.1秒
GO TOP
PRIVATE hh1, hh2, hh3
s1=SECONDS()
REPLACE 段比 WITH _fun() ALL

?" VFP更新时间=",SECONDS()-s1


GO TOP
BROWSE


FUNCTION _Fun()
  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)   
     RETURN STR(h1,1)+":"+STR(h2,1)+":"+STR(h3,1)
ENDFUNC
附件: 您没有浏览附件的权限,请 登录注册
2018-04-11 11:32
sdta
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等 级:版主
威 望:164
帖 子:7099
专家分:16663
注 册:2012-2-5
  得分:0 
生成测试数据11秒,生成比例结果21秒
2018-04-11 12:00
mywisdom88
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等 级:版主
威 望:78
帖 子:2490
专家分:6038
注 册:2015-3-25
  得分:0 
以下是引用sdta在2018-4-11 12:00:45的发言:

生成测试数据11秒,生成比例结果21秒

题外话,在VFP中运行T-SQL查询时,如果返回数据很多时,会自动显示进度条.
我想在数据不是很多的时候,也能使用他这个进度条,最好"提示信息"也能改.
知道怎么调出来不..如下图的进度110W,但我想几K就调用他..

附件: 您没有浏览附件的权限,请 登录注册
2018-04-11 12:32







关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.051612 second(s), 9 queries.
Copyright©2004-2018, BCCN.NET, All Rights Reserved