| 网站首页 | 业界新闻 | 群组 | 人才 | 下载频道 | 博客 | 代码贴 | 编程论坛
 雷速体育发帖软件开发QQ118000023 C语言培训|一对一辅导|零基础学编程 LightningChart 快速先进的.Net图表控件

已结贴   问题点数：20  回复次数：13

得分:15
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
得分:5
/*
-- 测试数据 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

得分:0

/*
-- 测试数据 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语句什么时候能出结果
得分:0

得分:0
* 在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"), ;
*!*    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
得分:0

-- 测试数据 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 彩票
得分:0

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编辑过]

得分: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
得分:0

* 用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"), ;
*!*    ENDFOR
*!*    BROWSE

* 但在SQL2000中，要13分钟
• 14
• 1/2页
• 1
• 2

Powered by Discuz, Processed in 0.037379 second(s), 8 queries.