重复行只选其中一行,请教!
当某几行GOODSID和VENDOR均相同的时候,取QTY最大的一行.GOODSID BILLCODE BILLDATE QTY VENDOR
13015 ED-2009-07-0620 2009-07-25 00:00:00.000 227.0 647
13015 ED-2009-07-0620 2009-07-25 00:00:00.000 250.0 647
13015 ED-2009-07-0620 2009-07-25 00:00:00.000 899 64713015 ED-2009-07-0620 2009-07-25 00:00:00.000 250.0 65010606 ED-2009-06-0251 2009-06-11 00:00:00.000 100.0 669
10611 ED-2009-06-0012 2009-06-02 00:00:00.000 600.0 653
10611 ED-2009-06-0108 2009-06-04 00:00:00.000 643.0 662结果如下:
GOODSID BILLCODE BILLDATE QTY VENDOR
13015 ED-2009-07-0620 2009-07-25 00:00:00.000 899 64713015 ED-2009-07-0620 2009-07-25 00:00:00.000 250.0 65010606 ED-2009-06-0251 2009-06-11 00:00:00.000 100.0 669
10611 ED-2009-06-0012 2009-06-02 00:00:00.000 600.0 653
10611 ED-2009-06-0108 2009-06-04 00:00:00.000 643.0 662
当某几行GOODSID和VENDOR均相同的时候,取QTY最大的一行.GOODSID BILLCODE BILLDATE QTY VENDOR
13015 ED-2009-07-0620 2009-07-25 00:00:00.000 227.0 647
13015 ED-2009-07-0620 2009-07-25 00:00:00.000 250.0 647
13015 ED-2009-07-0620 2009-07-25 00:00:00.000 899 64713015 ED-2009-07-0620 2009-07-25 00:00:00.000 250.0 65010606 ED-2009-06-0251 2009-06-11 00:00:00.000 100.0 669
10611 ED-2009-06-0012 2009-06-02 00:00:00.000 600.0 653
10611 ED-2009-06-0108 2009-06-04 00:00:00.000 643.0 662结果如下:
GOODSID BILLCODE BILLDATE QTY VENDOR
13015 ED-2009-07-0620 2009-07-25 00:00:00.000 899 64713015 ED-2009-07-0620 2009-07-25 00:00:00.000 250.0 65010606 ED-2009-06-0251 2009-06-11 00:00:00.000 100.0 669
10611 ED-2009-06-0012 2009-06-02 00:00:00.000 600.0 653
10611 ED-2009-06-0108 2009-06-04 00:00:00.000 643.0 662
select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and vendor=t.vendor and qty>t.qty)
from tb t
where not exists(select * from tb where t.GOODSID=GOODSID
and t.BILLCODE =BILLCODE and t. BILLDATE and VENDOR=t.VENDOR and t.QTY<qty
)
还想不到用嵌套查询来写。
平时不大用exsists,not exsists啊。
看来很好用啊。不知道是不是比join效率低啊?
哎!学习了.