将二个相同内容的销售表合并，提取其中一个客户所有购买产品的最后(近)记录(如有相同产品,依据"SaleDate"列举最后一笔记录)
表一: SaleTran   表二: SalTranY  内容: CustNO, Item, Description, SaleDate, Qty, Price
假定 CustNO = ABC-CO
我只完成了一半希望熟悉sql语句的朋友来看看, Thanks You

SELECT * FROM salesTran;
          INTO CURSOR a_tran;
          WHERE TRAN(custno) = 'ABC-CO';
UNION;
SELECT * FROM salTranY; 
          WHERE tran(custno) = 'ABC-CO'

[ 本帖最后由 twy628659 于 2010-9-4 09:48 编辑 ]

对不起, 我再详细说明一下
这在商业批发行业中非常有用，例如客户一年内经常购买5种产品，对于销售员有时想了解此客户每一种产品的最后交易(日期、价格、品种)
** 加粗是我要的结果,从复产品如(鸡蛋,苹果,桔子)只保留最后成交日期的那笔交易 - 以便观察客户买过什么产品和最后交易内客
謝謝!!
CustNO        Item    Description    SaleDate      Qty    Price
ABC-CO        1001    鸡蛋          2010-01-02     10    3.80
ABC-CO        1001    鸡蛋          2010-03-06    132    3.90
ABC-CO      1001   鸡蛋         2010-04-02   14   4.10
ABC-CO        1002    苹果          2010-04-02     10    10.50
ABC-CO        1004    桔子          2010-06-02     14    15.00
ABC-CO        1004    桔子          2010-07-31     132   14.00
ABC-CO      1004   桔子         2010-08-15   17    10.50
ABC-CO      1002   苹果         2010-07-02   10    9.00
ABC-CO      1005   蔬菜         2010-08-15    3     10.50
ABC-CO      1006   籽油         2010-07-02    5     9.00

希望学习以下这一段全用SQL语句来表示

程序代码：

SELECT * FROM salesTran;
          INTO CURSOR a_tran;
          WHERE TRAN(custno) = 'ABC-CO';
          ORDER BY item, saledate DESCENDING
UNION;
SELECT * FROM salTranY;
          WHERE tran(custno) = 'ABC-CO'

** Remove same item duplicates record ORDER BY DESCENDING saledate
SELECT a_tran
DO WHILE .T.

 m.item = item
   SKIP
   IF EOF()
    EXIT
   ENDIF
   DO WHILE item = m.item
        DELETE
    SKIP
   ENDDO   
ENDDO

** Remove DELETE record
SELECT * FROM a_tran;
INTO CURSOR answer;
WHERE .not. DELETE()

[ 本帖最后由 twy628659 于 2010-9-4 09:50 编辑 ]

Hi hgfeng1984:  SYNTAX ERROR

受你的MAX()启发虽然我无法实现一行代码，但比起用DO WHILE ....还是强了不少，谢谢！
为了使所有看帖的人都受益，特附送artran98.dbf , arytrn98.dbf 有兴趣的朋友可以自已动手试试，最好可以实现一行代码，大家共同学习

CLOSE ALL
USE C:\temp\artran98.dbf IN 0 SHARED
USE C:\temp\arytrn98.dbf IN 0 SHARED

** Single Customer MAX(invdte) history sales
SELECT * FROM artran98;
          INTO CURSOR a_tran;
          WHERE TRAN(custno) = '65RTON';
UNION;
SELECT * FROM arytrn98;
          WHERE tran(custno) = '65RTON'
** Remove same item duplicates record ORDER BY Invdte
SELECT MAX(INVDTE) AS Invoice_Date,Invno,Custno,Item,Descrip,Cost,Price Qtyord,Qtyshp;
INTO CURSOR answer;
FROM a_tran GROUP BY item;
ORDER BY item
SELECT answer
BROWSE

** All Customer MAX(invdte) history sales
SELECT * FROM artran98;
          INTO CURSOR a_tran2;
UNION;
SELECT * FROM arytrn98
** Remove same item duplicates record ORDER BY Invdte
SELECT MAX(INVDTE) AS Invoice_Date,Invno,Custno,Item,Descrip,Cost,Price Qtyord,Qtyshp;
INTO CURSOR answer2;
FROM a_tran2 GROUP BY custno,item;
ORDER BY custno,item
SELECT answer2
BROWSE

[ 本帖最后由 twy628659 于 2010-9-4 11:22 编辑 ]

a_tran=2表合并后的临时表, SYNTAX ERROR on this: (SELECT CUSTNO,ITEM,MAX(invdte) AS SALEDATE FROM a_tran GROUP BY CUSTNO,ITEM) B;
如果你方便的话，利用我给大家的tables做一个完整也好方便大家学习, 再次感谢你的回复