MSSQL2000求日前采购日期单价

2012-12-17

MSSQL2000求最近采购日期单价MSSQL2000求最近采购日期单价环境：MSSQL2000数据源表purchasedetail, 可否把

MSSQL2000求最近采购日期单价
MSSQL2000求最近采购日期单价
环境：MSSQL2000
数据源表purchasedetail, 可否把结果转化为视图吗？

billid,itemno,billcode,billdate,materialid,price
1     ,  1   ,pc001,2012-10-10,10001, 2.5
1     ,  2   ,pc001,2012-10-10,10003, 3.0

3     ,  1   ,pc002,2012-10-10,10001, 2.6

4     ,  1   ,pc000,2012-10-19,10001,  2.3

求物料最近采购单价 (如果是当天有多笔业务则最大单价)

  where  billdtate<='2012-10-10'如果如下
billid,itemno,billcode,billdate,materialid,price
1     ,  2   ,pc001,2012-10-10,10003, 3.0
3     ,  1   ,pc002,2012-10-10,10001, 2.6

  where  billdtate<='2012-10-19'如果如下
billid,itemno,billcode,billdate,materialid,price
1     ,  2   ,pc001,2012-10-10,10003, 3.0
4     ,  1   ,pc000,2012-10-19,10001,  2.3
[最优解释]

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2012-10-21 17:58:16
--  blog  : blog.csdn.net/herowang
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (billid INT,itemno INT,billcode VARCHAR(5),billdate DATETIME,materialid INT,price NUMERIC(2,1))
INSERT INTO [tb]
SELECT 1,1,'pc001','2012-10-10',10001,2.5 UNION ALL
SELECT 1,2,'pc001','2012-10-10',10003,3.0 UNION ALL
SELECT 3,1,'pc002','2012-10-10',10001,2.6 UNION ALL
SELECT 4,1,'pc000','2012-10-19',10001,2.3

select * from [tb] t 
where not exists(select 1 from tb where materialid=t.materialid and price>t.price )
      and  billdate<='2012-10-19'

[其他解释]
我奇怪为什么19号之后两条？而且你的表主键是什么？

CREATE TABLE purchasedetail (billid INT,itemno INT,billcode VARCHAR(5),billdate DATETIME,materialid INT,price NUMERIC(2,1))
INSERT INTO purchasedetail
SELECT 1,1,'pc001','2012-10-10',10001,2.5 UNION ALL
SELECT 1,2,'pc001','2012-10-10',10003,3.0 UNION ALL
SELECT 3,1,'pc002','2012-10-10',10001,2.6 UNION ALL
SELECT 4,1,'pc000','2012-10-19',10001,2.3

SELECT  *
FROM    purchasedetail a
WHERE   EXISTS ( SELECT 1
                  FROM   ( SELECT    MAX(price) price ,billid,
                                     billdate
                           FROM      purchasedetail

                           WHERE  billdate<='2012-10-10'
                           GROUP BY  billdate,billid
                         ) b
                  WHERE  a.price = b.price
                         AND a.billdate = b.billdate  )
/*
billid      itemno      billcode billdate                materialid  price
----------- ----------- -------- ----------------------- ----------- ---------------------------------------
1           2           pc001    2012-10-10 00:00:00.000 10003       3.0
3           1           pc002    2012-10-10 00:00:00.000 10001       2.6

(2 行受影响)

*/

SELECT  *
FROM    purchasedetail a
WHERE   EXISTS ( SELECT 1
                  FROM   ( SELECT    MAX(price) price ,billid,
                                     billdate
                           FROM      purchasedetail
                           WHERE  billdate<='2012-10-19'
                           GROUP BY  billdate,billid
                         ) b
                  WHERE  a.price = b.price
                         AND a.billdate = b.billdate  )
/*
billid      itemno      billcode billdate                materialid  pricea
----------- ----------- -------- ----------------------- ----------- ---------------------------------------

1           2           pc001    2012-10-10 00:00:00.000 10003       3.0
3           1           pc002    2012-10-10 00:00:00.000 10001       2.6
4           1           pc000    2012-10-19 00:00:00.000 10001       2.3

(3 行受影响)
*/

[其他解释]
工具--》选项---》结果
默认结果目标，下拉菜单，结果显示为表格

热点排行

SQL Server

MSSQL2000求日前采购日期单价