2,723
社区成员
发帖
与我相关
我的任务
分享
CREATE CURSOR T (机车型号 C(10),机车编号 C(10),设备名称 C(10),设备型号 C(10),设备编号 N(10),生产日期 D,生产厂家 C(10),标识 I)
INSERT INTO T VALUES ([SS4],[001A],[LLL主机],[2000],9049,{^2005-12-01},[株洲],1)
INSERT INTO T VALUES ([SS4],[001A],[LLL显示器1],[2000],10149,{^2007-02-01},[株洲],2)
INSERT INTO T VALUES ([SS4],[001A],[LLL显示器2],[2000],1234,{^2008-09-01},[株洲],3)
INSERT INTO T VALUES ([SS4],[001A],[TTT2箱],[2001],5985,{^2000-10-01},[思维],4)
INSERT INTO T VALUES ([SS4],[002A],[LLL主机],[2000],8076,{^2005-10-01},[株洲],1)
INSERT INTO T VALUES ([SS4],[002A],[LLL显示器1],[2000],10136,{^2007-02-01},[株洲],2)
INSERT INTO T VALUES ([SS4],[002A],[LLL显示器2],[2002],4567,{^2005-02-01},[株洲],3)
INSERT INTO T VALUES ([SS4],[002A],[TTT2箱],[2000],5902,{^2000-10-01},[思维],4)
SELECT DISTINCT 设备名称, 标识 FROM T ORDER BY 标识 INTO CURSOR T1
SCAN
SELECT * FROM T WHERE ALLTRIM(设备名称)==ALLTRIM(T1.设备名称) INTO CURSOR [S]+TRANSFORM(RECNO([T1]))
ENDSCAN
USE IN T1
SELECT S1.机车型号,S1.机车编号,S1.设备名称 设备名称,S1.设备型号 设备型号,S1.设备编号 设备编号,S1.生产日期 生产日期,S1.生产厂家 生产厂家,;
S2.设备名称 设备名称2,S2.设备型号 设备型号2,S2.设备编号 设备编号2,S2.生产日期 生产日期2,S2.生产厂家 生产厂家2,;
S3.设备名称 设备名称3,S3.设备型号 设备型号3,S3.设备编号 设备编号3,S3.生产日期 生产日期3,S3.生产厂家 生产厂家3,;
S4.设备名称 设备名称4,S4.设备型号 设备型号4,S4.设备编号 设备编号4,S4.生产日期 生产日期4,S4.生产厂家 生产厂家4 ;
FROM S1 INNER JOIN S2 ON S1.机车型号==S2.机车型号 AND S1.机车编号==S2.机车编号 ;
INNER JOIN S3 ON S1.机车型号==S3.机车型号 AND S1.机车编号==S3.机车编号 ;
INNER JOIN S4 ON S1.机车型号==S4.机车型号 AND S1.机车编号==S4.机车编号
CREATE CURSOR T (ID C(2),ZDM C(10),btz1 C(10),btz2 C(10)) &&保存不同记录的临时表
CREATE CURSOR TA (ID C(2),XM C(3),XB C(1))
CSTA=[01,A01,1;02,A02,1;03,A03,1;04,A04,2;05,A05,2;06,A06,2;07,A07,1;08,A08,1;09,A09,1;10,A10,2]
FOR I=1 TO ALINES(AA,CSTA,[;])
=ALINES(AAA,AA(I),[,])
INSERT INTO TA VALUES(AAA(1),AAA(2),AAA(3))
ENDFOR
CREATE CURSOR TB (ID C(2),XM C(3),XB C(1))
CSTB=[01,A11,1;02,A02,1;03,A03,2;04,A04,1;05,A05,2;06,A06,2;07,A07,1;08,A08,1;09,A09,1;10,A10,2]
FOR I=1 TO ALINES(BB,CSTB,[;])
=ALINES(BBB,BB(I),[,])
INSERT INTO TB VALUES(BBB(1),BBB(2),BBB(3))
ENDFOR
EE=AFIELDS(AA,'TA')
FOR I=1 TO EE
STORE "SELECT " TO ASQLA,ASQLB
IF UPPER(AA(I,1))<>'ID'
ASQLA=ASQLA+"'"+AA(I,1)+"' as zd,id,"+AA(I,1)+" FROM ta INTO CURSOR qta"
&ASQLA
ASQLB=ASQLB+"'"+AA(I,1)+"' as zd,id,"+AA(I,1)+" FROM tB INTO CURSOR qtB"
&ASQLB
ASQLC="INSERT INTO T SELECT a.ID,A.ZD,A."+AA(I,1)+",b."+AA(I,1)+" FROM qta a LEFT JOIN qtb b ON a.id=b.id WHERE A."+AA(I,1)+"<>B."+AA(I,1)
&ASQLC
ENDIF
NEXT
CREATE CURSOR T (机车型号 C(10),机车编号 C(10),设备名称 C(10),设备型号 C(10),设备编号 N(10),生产日期 D,生产厂家 C(10),标识 I)
INSERT INTO T VALUES ([SS4],[001A],[LLL主机],[2000],9049,{^2005-12-01},[株洲],1)
INSERT INTO T VALUES ([SS4],[001A],[LLL显示器1],[2000],10149,{^2007-02-01},[株洲],2)
INSERT INTO T VALUES ([SS4],[001A],[LLL显示器2],[2000],1234,{^2008-09-01},[株洲],3)
INSERT INTO T VALUES ([SS4],[001A],[TTT2箱],[2001],5985,{^2000-10-01},[思维],4)
INSERT INTO T VALUES ([SS4],[002A],[LLL主机],[2000],8076,{^2005-10-01},[株洲],1)
INSERT INTO T VALUES ([SS4],[002A],[LLL显示器1],[2000],10136,{^2007-02-01},[株洲],2)
INSERT INTO T VALUES ([SS4],[002A],[LLL显示器2],[2002],4567,{^2005-02-01},[株洲],3)
INSERT INTO T VALUES ([SS4],[002A],[TTT2箱],[2000],5902,{^2000-10-01},[思维],4)
SELECT DISTINCT 设备名称, 标识 FROM T ORDER BY 标识 INTO CURSOR T1
s = ''
SCAN
t = [S]+TRANSFORM(RECNO([T1]))
SELECT * FROM T WHERE ALLTRIM(设备名称)==ALLTRIM(T1.设备名称) INTO CURSOR &t
IF RECNO([T1]) > 1 then
s = s + [ INNER JOIN &t ON S1.机车型号==&t..机车型号 AND S1.机车编号==&t..机车编号]
endif
ENDSCAN
USE IN T1
SELECT * FROM S1 &s
CREATE CURSOR T (机车型号 C(10),机车编号 C(10),设备名称 C(10),设备型号 C(10),设备编号 N(10),生产日期 D,生产厂家 C(10),标识 I)
INSERT INTO T VALUES ([SS4],[001A],[LLL主机],[2000],9049,{^2005-12-01},[株洲],1)
INSERT INTO T VALUES ([SS4],[001A],[LLL显示器1],[2000],10149,{^2007-02-01},[株洲],2)
INSERT INTO T VALUES ([SS4],[001A],[LLL显示器2],[2000],1234,{^2008-09-01},[株洲],3)
INSERT INTO T VALUES ([SS4],[001A],[TTT2箱],[2001],5985,{^2000-10-01},[思维],4)
INSERT INTO T VALUES ([SS4],[002A],[LLL主机],[2000],8076,{^2005-10-01},[株洲],1)
INSERT INTO T VALUES ([SS4],[002A],[LLL显示器1],[2000],10136,{^2007-02-01},[株洲],2)
INSERT INTO T VALUES ([SS4],[002A],[LLL显示器2],[2002],4567,{^2005-02-01},[株洲],3)
INSERT INTO T VALUES ([SS4],[002A],[TTT2箱],[2000],5902,{^2000-10-01},[思维],4)
EE=AFIELDS(AA,'T')
ASQLA="select "
FOR j=1 to 4
aa1=1
ff=IIF(j=1,1,3)
FOR I=ff TO EE
IF AA(I,1)<>'标识'
ASQLA=ASQLA+"s"+TRANSFORM(J)+'.'+AA(I,1)+" as "+AA(I,1)+IIF(j=1,' ,',TRANSFORM(j)+' ,')
ENDIF
NEXT
NEXT
asqla=LEFT(asqla,len(asqla)-1)+' FROM S1 '
SELECT DISTINCT 设备名称, 标识 FROM T ORDER BY 标识 INTO CURSOR T1
CSTR=[]
CSTR1=[]
SCAN
SELECT * FROM T WHERE ALLTRIM(设备名称)==ALLTRIM(T1.设备名称) INTO CURSOR [S]+TRANSFORM(RECNO([T1]))
FOR I=3 TO FCOUNT()-1
CSTR=CSTR+[SELECT ']+S1.机车型号+[' 机车型号,']+S1.机车编号+[' 机车编号,;] +CHR(13)+CHR(10)+[S]+TRANSFORM(RECNO([T1]))+[.]+FIELD(I)+[ ]+FIELD(I)+IIF(RECNO([T1])<RECCOUNT([T1]),IIF(I<FCOUNT()-1,[,],[,;]+CHR(13)+CHR(10)),[ ])
ENDFOR
IF RECNO([T1])>1
CSTR1=CSTR1+CHR(13)+CHR(10)+[INNER JOIN ]+[S]+TRANSFORM(RECNO([T1]))+[ ON ]+[S1.机车型号==]+[S]+TRANSFORM(RECNO([T1]))+[.]+[机车型号]+[ AND ]+[S1.机车编号==]+[S]+TRANSFORM(RECNO([T1]))+[.]+[机车编号]+IIF(RECNO([T1])<RECCOUNT([T1]),[;]+CHR(13)+CHR(10),[])
ENDIF
ENDSCAN
asqla=asqla+cstr1
&asqla
CREATE CURSOR T (机车型号 C(10),机车编号 C(10),设备名称 C(10),设备型号 C
(10),设备编号 N(10),生产日期 D,生产厂家 C(10),标识 I)
INSERT INTO T VALUES ([SS4],[001A],[LLL主机],[2000],9049,{^2005-12-01},[
株洲],1)
INSERT INTO T VALUES ([SS4],[001A],[LLL显示器1],[2000],10149,{^2007-02-
01},[株洲],2)
INSERT INTO T VALUES ([SS4],[001A],[LLL显示器2],[2000],1234,{^2008-09-
01},[株洲],3)
INSERT INTO T VALUES ([SS4],[001A],[TTT2箱],[2001],5985,{^2000-10-01},[思
维],4)
INSERT INTO T VALUES ([SS4],[002A],[LLL主机],[2000],8076,{^2005-10-01},[
株洲],1)
INSERT INTO T VALUES ([SS4],[002A],[LLL显示器1],[2000],10136,{^2007-02-
01},[株洲],2)
INSERT INTO T VALUES ([SS4],[002A],[LLL显示器2],[2002],4567,{^2005-02-
01},[株洲],3)
INSERT INTO T VALUES ([SS4],[002A],[TTT2箱],[2000],5902,{^2000-10-01},[思
维],4)
EE=AFIELDS(AA,'T')
ASQLA="select "
SELECT MAX(标识) from t into array aa2
aa1=aa2(1)
FOR j=1 to aa1
ff=IIF(j=1,1,aa1-1)
FOR I=ff TO EE
ASQLA=ASQLA+IIF(AA(I,1)<>'标识',"s"+TRANSFORM(J)+'.'+AA(I,1)+" as "+AA
(I,1)+IIF(j=1,' ,',TRANSFORM(j)+' ,'),"")
NEXT
NEXT
asqla=LEFT(asqla,len(asqla)-1)+' FROM S1 '
SELECT DISTINCT 设备名称, 标识 FROM T ORDER BY 标识 INTO CURSOR T1
CSTR1=[]
SCAN
SELECT * FROM T WHERE ALLTRIM(设备名称)==ALLTRIM(T1.设备名称) INTO
CURSOR [S]+TRANSFORM(RECNO([T1]))
IF RECNO([T1])>1
CSTR1=CSTR1+CHR(13)+CHR(10)+[INNER JOIN ]+[S]+TRANSFORM(RECNO
([T1]))+[ ON ]+[S1.机车型号==]+[S]+TRANSFORM(RECNO([T1]))+[.]+[机车型号]+[
AND ]+[S1.机车编号==]+[S]+TRANSFORM(RECNO([T1]))+[.]+[机车编号]+IIF(RECNO
([T1])<RECCOUNT([T1]),[;]+CHR(13)+CHR(10),[])
ENDIF
ENDSCAN
asqla=asqla+cstr1
&asqla