VFP 愛用者社區

[bx1166]

Sele 1
Use original.dbf &your dbf name here
Copy to tempfile
Sele 1
Use tempfile
Copy to mtemp for type='C'
Dele for type='C'
Pack
Sele 2
Use mtemp
Go top &&or scan endscan
Do while .not. Eof()
Sele 1
Locate for date=b.date .and. Item=b.item .and. No=(0-b.no) .and. (.not. Deleted())
If .not. Eof()
Dele
End if
Sele 2
Skip
End do
Sele 1
Pack
Brow


存成prg
Run 這個prg
這樣看看結果對不對跑跑看

[yctsai]

1.我比較喜歡用select 指令來挑資料
2.select * from x where id+... IN (select id+... from cursor y) into cursor z
所以我用....代表這裡...是需要研究的
3.也許這樣
select * from x where type='C' into table y
select * from x where id+name+se_no+date+item+lo+alltrim(str(no)) IN (select id+name+se_no+date+item+lo+alltrim(str(no*-1)) from y) into table z
select * from x where id+name+se_no+date+item+loalltrim(str(no)) NOT IN (select id+name+se_no+date+item+lo+alltrim(str(no)) from z) into table zz

[sthuang66]

to bx1166
您的方法跑完只有消掉type=C,第五第六兩筆!第一第二還在

[sthuang66]

to yctsai
select * from x where type='C' into table y
select * from x where id+name+se_no+date+item+lo+alltrim(str(no)) IN (select id+name+se_no+date+item+lo+alltrim(str(no*-1)) from y) into table z
select * from x where id+name+se_no+date+item+lo+alltrim(str(no)) NOT IN (select id+name+se_no+date+item+lo+alltrim(str(no)) from z) into table zz
您的方法我操作後會剩下4,5,6三筆資料!1,2,3筆去掉了

我附上檔案供參考http://vfp.sunyear.com.tw/files/x_406.xls

因為DBF不能PO!所以貼上XLS

[sthuang66]

TO yctsai
我參考你之前寫的B. 用SUM方法好像可以了
----------------------------------------------------------------
SE='ID+NAME+SE_NO+DATE+ITEM+LO'
SELECT *,&SE AS SEQ FROM X INTO TABLE X1
SELECT ID,NAME,SE_NO,DATE,ITEM,LO,SEQ,SUM(NO) AS NO FROM X1 GROUP BY ID,NAME,SE_NO,DATE,ITEM,LO,SEQ INTO TABLE X2
SELECT * FROM X2 WHERE NO<>0 INTO TABLE X3
QUIT

[bx1166]

[sthuang66]

TO bx1166
謝謝你的關注與指教
----------------------------------------------------------------------------------------
Sele 1
Use original.dbf &your dbf name here
Copy to tempfile
Sele 1
Use tempfile
Copy to mtemp for type='C'
Dele for type='C'
Pack
Sele 2
Use mtemp
Go top &&or scan endscan
Do while .not. Eof()
Sele 1
Locate for date=b.date .and. Item=b.item .and. No=(0-b.no)
If .not. Eof()
Dele
Endif
Sele 2
Skip
Enddo
Sele 1
Pack
Brow
------------------------------------------------------------
第二筆還是沒有去掉

[bx1166]

修正一下，第一跟第二筆其實是一樣，所以得再修正此處

[perry]

您不是測Select了???
sele id,name,se_no,date,item,lo,sum(no) as no from Yourdbf grou by id,name,date,item,lo into curs x1
sele * from x1 wher no#0 into curs x2
brow
clos all
clea all

[bx1166]

我覺得select無法解決問題,現在只有幾筆,一旦重複到上幾千幾萬筆時,如何挑出多筆重複的?

[perry]

其實不會有問題!!
會出問題是在 type 是否是 'C' 和 '3' 相對
sele id,name,se_no,date,item,lo,sum(no) as no from Yourdbf wher type='C' or type='3' grou by id,name,date,item,lo into curs x1

[perry]

若用 scan 換個角度或許效率較高
set dele on
sele 0
sele * from yourdbf wher type ='3' into dbf x1
sele 0
sele * from yourdbf wher type='C' into dbf x2
sele x1
go top
scan
sele x2
go top
if eof()
exit
endi
loca for id=x1.id and se_no=x1.se_no and date=x1.date and item=x1.item and lo=x1.lo
if found()
dele
sele x1
dele
endi
sele x1
ends

[sthuang66]

TO PERRY
--------------------------------------------------------
set dele on
sele 0
sele * from yourdbf wher type ='3' into dbf x1
sele 0
sele * from yourdbf wher type='C' into dbf x2
sele x1
go top
scan
sele x2
go top
if eof()
exit
endi
loca for id=x1.id and se_no=x1.se_no and date=x1.date and item=x1.item and lo=x1.lo
if found()
dele
sele x1
dele
endi
sele x1
ends
----------------------------------------------------
執行後x1好像沒有變
-----------------------------------------------------
TO BX1166
SELECT 的語法我參照YCTSAI可以了
並且PERRY也寫出來跟我一樣可以消掉第一與第二筆

-------------------------------------------
SE='ID+NAME+SE_NO+DATE+ITEM+LO'
SELECT *,&SE AS SEQ FROM X INTO TABLE X1
SELECT ID,NAME,SE_NO,DATE,ITEM,LO,SEQ,SUM(NO) AS NO FROM X1 GROUP BY ID,NAME,SE_NO,DATE,ITEM,LO,SEQ INTO TABLE X2
SELECT * FROM X2 WHERE NO<>0 INTO TABLE X3
QUIT

[perry]

其實 BX1166 考慮的如果資料若不是 id+se_no+date+item+lo 一天一筆,
用 select * grou by 方式會有問題!!
而用 scan 可避免問題發生!!
下面我測有效,不知 sthuang66 為何說無效???

[bx1166]

畢竟我們不是當事人，我見論壇裡面問題形形色色，解決問題就好，
我更喜歡看別人的問答，可以學習到
至於vfp 是否有未來，只要大家繼續用,就有機會𨒂續它的生命
我還見過還堅持dos,clipper的呢。