编程技巧擂台赛[四] - VFP论坛

得分:0

最优方案来了，怕楼主看得眼花缭乱啊，哈哈。就一句SELECT-SQL，但子查询颇多。

SELECT A.KSH,A.性别,A.必考分数,;
       B.抽选分数,C.自选分数,D.备选分数, ;
       A.必考分数+B.抽选分数+C.自选分数+D.备选分数总分数 ;
   FROM ;
      (SELECT T.KSH,T.性别,T.必考项目,B.分数必考分数 ;
          FROM 体考 T ;
             JOIN 标准 B ;
                ON T.性别+ALLTRIM(T.必考项目)=B.性别+ALLTRIM(B.项目名称) AND ;
                   T.必考项目数 BETWEEN B.下限 AND B.上限 ;
       UNION ALL ;
       SELECT DISTINCT T.KSH,T.性别,T.必考项目,0 ;
          FROM 体考 T ;
             JOIN (SELECT 项目名称,性别,MIN(下限) 下限,MAX(上限) 上限 ;
                      FROM 标准 ;
                      GROUP BY 项目名称,性别) B ;
                ON T.性别+ALLTRIM(T.必考项目)=B.性别+ALLTRIM(B.项目名称) AND ;
                   T.必考项目数 NOT BETWEEN B.下限 AND B.上限) A ;
      JOIN ;
         (SELECT T.KSH,T.性别,T.抽选项目,B.分数抽选分数 ;
             FROM 体考 T ;
                JOIN 标准 B ;
                   ON T.性别+ALLTRIM(T.抽选项目)=B.性别+ALLTRIM(B.项目名称) AND ;
                      T.抽定项目数 BETWEEN B.下限 AND B.上限 ;
          UNION ALL ;
          SELECT KSH,性别,抽选项目,0 ;
             FROM 体考 ;
             WHERE ALLTRIM(抽选项目) NOT IN ;
                   (SELECT DISTINCT ALLTRIM(项目名称) FROM 标准) ;
          UNION ALL ;
          SELECT DISTINCT T.KSH,T.性别,T.抽选项目,0 ;
             FROM 体考 T ;
                JOIN (SELECT 项目名称,性别,MIN(下限) 下限,MAX(上限) 上限 ;
                         FROM 标准 ;
                         GROUP BY 项目名称,性别) B ;
                  ON T.性别+ALLTRIM(T.抽选项目)=B.性别+ALLTRIM(B.项目名称) AND ;
                     T.抽定项目数 NOT BETWEEN B.下限 AND B.上限) B ;
         ON A.KSH=B.KSH ;
      JOIN ;
         (SELECT T.KSH,T.性别,T.自选项目,B.分数自选分数 ;
             FROM 体考 T ;
                JOIN 标准 B ;
                   ON T.性别+ALLTRIM(T.自选项目)=B.性别+ALLTRIM(B.项目名称) AND ;
                      T.自选项目数 BETWEEN B.下限 AND B.上限 ;
          UNION ALL ;
          SELECT KSH,性别,自选项目,0 ;
             FROM 体考 ;
             WHERE ALLTRIM(自选项目) NOT IN ;
                   (SELECT DISTINCT ALLTRIM(项目名称) FROM 标准) ;
          UNION ALL ;
          SELECT DISTINCT T.KSH,T.性别,T.自选项目,0 ;
             FROM 体考 T ;
                JOIN (SELECT 项目名称,性别,MIN(下限) 下限,MAX(上限) 上限 ;
                         FROM 标准 ;
                         GROUP BY 项目名称,性别) B ;
                   ON T.性别+ALLTRIM(T.自选项目)=B.性别+ALLTRIM(B.项目名称) AND ;
                      T.自选项目数 NOT BETWEEN B.下限 AND B.上限) C ;
         ON A.KSH=C.KSH ;
      JOIN ;
         (SELECT T.KSH,T.性别,T.备选项目,B.分数备选分数 ;
             FROM 体考 T ;
                JOIN 标准 B ;
                   ON T.性别+ALLTRIM(T.备选项目)=B.性别+ALLTRIM(B.项目名称) AND ;
                      T.备选项目数 BETWEEN B.下限 AND B.上限 ;
          UNION ALL ;
          SELECT KSH,性别,备选项目,0 ;
             FROM 体考 ;
             WHERE ALLTRIM(备选项目) NOT IN ;
                  (SELECT DISTINCT ALLTRIM(项目名称) FROM 标准) ;
          UNION ALL ;
          SELECT DISTINCT T.KSH,T.性别,T.备选项目,0 ;
             FROM 体考 T ;
                JOIN (SELECT 项目名称,性别,MIN(下限) 下限,MAX(上限) 上限 ;
                         FROM 标准 ;
                         GROUP BY 项目名称,性别) B ;
                   ON T.性别+ALLTRIM(T.备选项目)=B.性别+ALLTRIM(B.项目名称) AND ;
                      T.备选项目数 NOT BETWEEN B.下限 AND B.上限) D ;
         ON A.KSH=D.KSH ;
   ORDER BY 总分数 DESC, A.KSH ASC

图片附件: 游客没有浏览图片的权限，请登录或注册

思路是这样的，每个项目分三种情况：1)完全与标准表中的合拍。即，性别、项目名称同时匹配，且项目数落在上下限内；2)项目名称根本不在标准表中。分数为0；3)虽然性别、项目名称同时匹配，但项目数在上下限外。这种情况，分数也是0。除了必考项目外，所有其它项目都用UNION子句串联起三种情况的查询，以得到完整结果。必考项目因为全是50米跑，不存在项目名不在标准表中的情形，所以必考项目的查询仅有1)和3)两种情况。最后，总的SELECT-SQL命令把四种项目的查询结果联接起来，形成最终答案。这句SELECT-SQL命令用时不超过5秒，应该算是出结果最快的方案了。
为了让你看得清楚，特意用颜色区分三种情形：1)红色；2)绿色；3)蓝色。

[ 本帖最后由 taifu945 于 2013-1-1 16:37 编辑 ]

第 12 楼

得分:0

楼上第二项和第三项，可以用left jion 来确定。所以可以省略。
将楼上代码加以利用修改，时间从5秒多减少到1秒多。

程序代码：

SELECT A.KSH,A.性别,A.必考分数,;
       B.抽选分数,C.自选分数,D.备选分数, ;
       A.必考分数+B.抽选分数+C.自选分数+D.备选分数 总分数 ;
   FROM ;
      (SELECT T.KSH,T.性别,T.必考项目,NVL(B.分数,0) 必考分数 ;
          FROM 体考 T ;
             LEFT JOIN 标准 B ;
                ON T.性别==B.性别 AND T.必考项目==B.项目名称 AND ;
                  BETWEEN( T.必考项目数,B.下限 , B.上限)) A ;
      JOIN ;
         (SELECT T.KSH,T.性别,T.抽选项目,NVL(B.分数,0) 抽选分数 ;
             FROM 体考 T ;
               left JOIN 标准 B ;
                   ON T.性别==B.性别 AND T.抽选项目==B.项目名称 AND ;
                     BETWEEN(T.抽定项目数,B.下限,B.上限 )) B;
                       ON A.KSH=B.KSH ;
      JOIN ;
         (SELECT T.KSH,T.性别,T.自选项目,NVL(B.分数,0) 自选分数 ;
             FROM 体考 T ;
               left JOIN 标准 B ;
                   ON T.性别==B.性别 AND T.自选项目==B.项目名称 AND ;
                      BETWEEN(T.自选项目,B.下限, B.上限)) C ;
                      ON A.KSH=C.KSH ;
      JOIN ;
         (SELECT T.KSH,T.性别,T.备选项目,NVL(B.分数,0) 备选分数 ;
             FROM 体考 T ;
                LEFT JOIN 标准 B ;
                   ON T.性别==B.性别 AND T.备选项目==B.项目名称 AND ;
                     BETWEEN( T.备选项目数,B.下限 , B.上限 )) D;
                      ON A.KSH=D.KSH ;
                      ORDER BY 总分数 DESC, A.KSH ASC;
  INTO CURSOR tmpx

此代码比起我十楼的代码，在我的电脑上测试还要慢些。
我十楼的代码时间平均不到0.6秒。

[ 本帖最后由月沐庭轩于 2013-1-1 19:13 编辑 ]

坚持学习vfp，QQ：306805680

第 13 楼

得分:0

以下是引用月沐庭轩在2013-1-1 19:08:27的发言：

楼上第二项和第三项，可以用left jion 来确定。所以可以省略。
将楼上代码加以利用修改，时间从5秒多减少到1秒多。
SELECT A.KSH,A.性别,A.必考分数,;
       B.抽选分数,C.自选分数,D.备选分数, ;
       A.必考分数+B.抽选分数+C.自选分数+D.备选分数总分数 ;
   FROM ;
      (SELECT T.KSH,T.性别,T.必考项目,NVL(B.分数,0) 必考分数 ;
          FROM 体考 T ;
             LEFT JOIN 标准 B ;
                ON T.性别==B.性别 AND T.必考项目==B.项目名称 AND ;
                  BETWEEN( T.必考项目数,B.下限 , B.上限)) A ;
      JOIN ;
         (SELECT T.KSH,T.性别,T.抽选项目,NVL(B.分数,0) 抽选分数 ;
             FROM 体考 T ;
               left JOIN 标准 B ;
                   ON T.性别==B.性别 AND T.抽选项目==B.项目名称 AND ;
                     BETWEEN(T.抽定项目数,B.下限,B.上限 )) B;
                       ON A.KSH=B.KSH ;
      JOIN ;
         (SELECT T.KSH,T.性别,T.自选项目,NVL(B.分数,0) 自选分数 ;
             FROM 体考 T ;
               left JOIN 标准 B ;
                   ON T.性别==B.性别 AND T.自选项目==B.项目名称 AND ;
                      BETWEEN(T.自选项目,B.下限, B.上限)) C ;
                      ON A.KSH=C.KSH ;
      JOIN ;
         (SELECT T.KSH,T.性别,T.备选项目,NVL(B.分数,0) 备选分数 ;
             FROM 体考 T ;
                LEFT JOIN 标准 B ;
                   ON T.性别==B.性别 AND T.备选项目==B.项目名称 AND ;
                     BETWEEN( T.备选项目数,B.下限 , B.上限 )) D;
                      ON A.KSH=D.KSH ;
                      ORDER BY 总分数 DESC, A.KSH ASC;
  INTO CURSOR tmpx
此代码比起我十楼的代码，在我的电脑上测试还要慢些。
我十楼的代码时间平均不到0.6秒。

月版高明！NVL()函数和左连接技术是亮点，学习中...

第 14 楼

得分:80

程序代码：

SELECT ksh,NVL(分数,0) AS tmp FROM 体考 LEFT JOIN 标准 ;
    ON 标准.项目名称=体考.必考项目 AND 标准.性别=体考.性别 ;
    AND  BETWEEN(体考.必考项目数,下限,上限) INTO CURSOR cursor1 
UPDATE  体考  SET 体考.必考分数= cursor1.tmp FROM cursor1 WHERE 体考.ksh=cursor1.ksh
SELECT  ksh,NVL(分数,0) AS tmp FROM 体考 LEFT JOIN 标准 ;
    ON  标准.项目名称=体考.抽选项目 AND 标准.性别=体考.性别 ;
    AND  BETWEEN(体考.抽定项目数,下限,上限)  INTO CURSOR cursor1
UPDATE  体考 SET 体考. 抽选分数= cursor1.tmp FROM cursor1  WHERE 体考.ksh=cursor1.ksh
SELECT  ksh,NVL(分数,0) AS tmp FROM 体考 LEFT JOIN 标准 ;
    ON 标准.项目名称=体考.自选项目 AND 标准.性别=体考.性别;
    AND  BETWEEN(体考.自选项目数,下限,上限)  INTO CURSOR cursor1
UPDATE  体考 SET 体考.自选分数= cursor1.tmp  FROM cursor1 WHERE 体考.ksh=cursor1.ksh
SELECT  ksh,NVL(分数,0) AS tmp FROM 体考 LEFT JOIN 标准 ;
    ON 标准.项目名称=体考.备选项目 AND 标准.性别=体考.性别 ;
    AND  BETWEEN(体考.备选项目数,下限,上限) INTO CURSOR cursor1
UPDATE  体考 SET 体考.备选分数= cursor1.tmp  FROM cursor1 WHERE 体考.ksh=cursor1.ksh
UPDATE  体考  SET 总分数=必考分数+抽选分数+自选分数+备选分数

一句update句子

程序代码：

UPDATE  体考  SET 体考.必考分数=tmp1,抽选分数=tmp2,自选分数=tmp3,备选分数=tmp4,;
    总分数=必考分数+抽选分数+自选分数+备选分数;
    FROM ((SELECT ksh,NVL(分数,0) AS tmp1 FROM 体考 LEFT JOIN 标准 ;
    ON 标准.项目名称=体考.必考项目 AND 标准.性别=体考.性别 ;
    AND  BETWEEN(体考.必考项目数,下限,上限)) a ;
    JOIN(SELECT ksh,NVL(分数,0) AS tmp2 FROM 体考 LEFT JOIN 标准 ;
    ON  标准.项目名称=体考.抽选项目 AND 标准.性别=体考.性别 ;
    AND  BETWEEN(体考.抽定项目数,下限,上限))b ON  a.ksh=b.ksh;
    JOIN (SELECT ksh,NVL(分数,0) AS tmp3 FROM 体考 LEFT JOIN 标准 ;
    ON 标准.项目名称=体考.自选项目 AND 标准.性别=体考.性别;
    AND  BETWEEN(体考.自选项目数,下限,上限))c ON a.ksh=c.ksh;
    JOIN (SELECT  ksh,NVL(分数,0) AS tmp4 FROM 体考 LEFT JOIN 标准 ;
    ON 标准.项目名称=体考.备选项目 AND 标准.性别=体考.性别 ;
    AND  BETWEEN(体考.备选项目数,下限,上限))d ON a.ksh=d.ksh);
    WHERE a.ksh=体考.ksh