有表a1，字段为x1,x2,x3,x4,x5,x6，字符型，长度2，
     x1  x2 x3  x4  x5  x6              需要的结果是
1、02,30,65,98,05,35                                   02,05,30,35,65,98
2、51,01,06,05,07,02                                   01,02,05,06,07,51
3、41,42,56,68,72,95                                   41,42,56,68,72,95
..................
..................
现在要用sql语句将x1x2x3x4x5x6从小到大排列并且放在另外6个字段

用SQL排序是否可行？

好象是无法用SQL实现吧。

9.0版( 其中,mysort()为升序自定义函数 ):

sele *,subs(zc,1,2) z1,subs(zc,3,2) z2,subs(zc,5,2) z3;
      ,subs(zc,7,2) z4,subs(zc,9,2) z5,subs(zc,11,2) z6;
     from ( sele *,mysort(x1,x2,x3,x4,x5,x6) zc from 你的表 ) k into curs tmp


附测试图:


[ 本帖最后由 茵梦湖 于 2012-7-3 13:36 编辑 ]

茵梦湖版主，那个mysort()升序自定义函数，怎么做的，公布一下，谢谢！

VFP6.0代码

CREATE CURSOR TABNAMEA (X1 C(2),X2 C(2),X3 C(2),X4 C(2),X5 C(2),X6 C(2),B1 C(2),B2 C(2),B3 C(2),B4 C(2),B5 C(2),B6 C(2))
INSERT INTO TABNAMEA (X1,X2,X3,X4,X5,X6) VALUES ("02","30","65","98","05","35")
INSERT INTO TABNAMEA (X1,X2,X3,X4,X5,X6) VALUES ("51","01","06","05","07","02")
INSERT INTO TABNAMEA (X1,X2,X3,X4,X5,X6) VALUES ("95","76","32","56","69","43")
SCAN
   SCATTER TO SJ FIELDS LIKE X*
   IF ASORT(SJ)=1
      GATHER FROM SJ FIELDS LIKE B*
   ELSE
      ? "第"+TRANSFORM(RECNO())+"条记录排序失败"
   ENDIF
ENDSCAN
BROWSE

不是什么时候都要用SQL语句，要简单明了易懂

VFP9.0代码

CREATE CURSOR TABNAMEA (X1 C(2),X2 C(2),X3 C(2),X4 C(2),X5 C(2),X6 C(2))
INSERT INTO TABNAMEA VALUES ("02","30","65","98","05","35")
INSERT INTO TABNAMEA VALUES ("51","01","06","05","07","02")
INSERT INTO TABNAMEA VALUES ("95","76","32","56","69","43")
SELECT *,GETWORDNUM(AB,1,",") B1,GETWORDNUM(AB,2,",") B2,GETWORDNUM(AB,3,",") B3,;
         GETWORDNUM(AB,4,",") B4,GETWORDNUM(AB,5,",") B5,GETWORDNUM(AB,6,",") B6;
     FROM ( SELECT *,Asort_1(X1,X2,X3,X4,X5,X6) AB FROM TABNAMEA ) C INTO CURSOR CX
BROWSE
FUNCTION Asort_1
PARAMETERS A1,A2,A3,A4,A5,A6
IF PARAMETERS()<6
   RETURN
ENDIF
LOCAL SJ(PARAMETERS())
CSTR=""
FOR I=1 TO PARAMETERS()
    SJ(I)=EVALUATE("A"+TRANSFORM(I))
ENDFOR
=ASORT(SJ)
FOR I=1 TO ALEN(SJ)
    CSTR=CSTR+SJ(I)+IIF(I<ALEN(SJ),",","")  
ENDFOR
RETURN CSTR
ENDFUNC