MSSQL2000最近单价不重复行查询取数
MSSQL2000最近单价,不重复的单价,可能有重复的单价的。

当traderid、materialid、work 均相同时
取最大BILLDATE日期后
再取最大单价 (注意顺序)

视图：view_gyszjdj
日期, 供应商,物料资料，单价, 工序
billdate, traderid, materialid,price , work
2012-09-01, 1001 , A , 2 ,'机加'
2012-09-01, 1001 , B , 3 ,'机加'
2012-09-01, 1001 , B , 3 ,'机加'
2012-09-01, 1001 , B , 3.4 ,'机加'
2012-09-01, 1001 , B , 3.4 ,'机加'
2012-09-01, 1001 , A , 2.3 ,'打磨'

2012-09-01, 4008 , B , 3 ,'机加'
2012-09-01, 4008 , B , 3.5 ,'机加'

2012-12-01, 4008 , B , 3.2 ,'机加'

2012-12-01, 4008 , B , 3.1 ,'抛光'


要求利用查询语句把结果转换为视图view

日期, 供应商,物料资料，单价
billdate, traderid, materialid,price, work
2012-09-01, 1001 , A , 2 ,'机加'
2012-09-01, 1001 , A , 2.3 ,'打磨'


2012-09-01, 1001 , B , 3.4 ,'机加'

2012-12-01, 4008 , B , 3.2 ,'机加'
2012-12-01, 4008 , B , 3.1 ,'抛光'



[解决办法]
select * from view_gyszjdj a
where not exists(select 1 from view_gyszjdj
where traderid=a.traderid and materialid=a.materialid and work=a.work
and billdate>a.billdate)
[解决办法]

SELECT  *
FROM    view_gyszjdj a
WHERE   EXISTS ( SELECT 1
                 FROM   ( SELECT    MAX(billdate) billdate ,
                                    traderid ,
                                    materialid ,
                                    WORK ,
                                    MAX(price) price
                          FROM      view_gyszjdj a
                          GROUP BY  traderid , 
 
                                    materialid ,
                                    WORK
                        ) b
                 WHERE  a.billdate = b.billdate
                        AND A.traderid = b.traderid
                        AND a.materialid = b.materialid
                        AND a.WORK = b.WORK
                        AND a.price = b.price )