MSSQL2000日前单价不重复行查询取数

2013-01-28

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

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 )

热点排行

SQL Server

MSSQL2000日前单价不重复行查询取数