求一份比价表中,可比部分的上次采购金额

Question

目的:一份比价表中涉及多个不同的物资,首先要算出A表中每份比价表的金额(sum(tableA.sl*tableB.price)...group by bjbbh),再算出本份比价表中有多少是以前采购过的物资,这部分物资叫做"可比物资",求可比物资的采购金额,第三,算出这些可比物资与上次比较的差额,叫做可比降低额,"-"表示升高.
注:bjbbh-比价表编号;wzdm-物资代码;sl-数量;tableAId-表A的id号
table A
id     bjbbh    wzdm    sl    date   
1      bs101    0001    5     2010-01-01
2      bs101    0002    5     2010-01-01
3      bs101    0003    5     2010-01-01
4      bs102    0002    5     2010-02-01
5      bs102    0004    5     2010-02-01
6      bs102    0005    5     2010-02-01
table B
id     tableAId   price  date
1      1          10.00  2010-01-01
2      2          10.00  2010-01-01
3      3          10.00  2010-01-01
4      4          12.00  2010-02-01
5      5          10.00  2010-02-01
6      6          10.00  2010-02-01想要的结果:
比价表编号    金额    可比金额(单价*数量)   可比降低额(上次单价*本次数量-可比金额) 日期
bs101         150.00   0.00                  0.00                                  2010-01-01
bs102         160.00   60.00                 -10.00                                2010-02-01

Answer 1

表A中有6第记录,前三条是在一个比价表中(bs101),后三条是另一个比价表(bs102),我要算出每个比价表的总金额是多少(A表中有数量,B表中有价格)两表的关联字段是tableA.id=tableB.tableAId.
bs101中的三条记录,在此之前没有采购过,所以没有可与之对比的记录,因此可比金额是0,可比降低额也是0,而bs102中有一条记录'0002'前期采购过,价格是10元,而这次采购价格是12元.这条记录就是可比较的记录,因此,bs102的可比金额是12*5=60元,降低额是上次的单价*本次的数量-本次的单价*本次的数量=-10元.
简而言之,就是每新产生一份比价表,都要去与该比价表中所有物资的前期价进行对比,如果没有找到则不计,找到了就对比该记录的总成本是上升还是下降.
不知道这样说,大家能否明白.

Answer 2

CREATE table tableA(id INT,bjbbh VARCHAR(10),wzdm VARCHAR(4),sl INT,date DATETIME)
INSERT INTO tableA
SELECT 1 ,'bs101', '0001', 5 ,'2010-01-01' UNION ALL
SELECT 2 ,'bs101', '0002', 5 ,'2010-01-01' UNION ALL
SELECT 3 ,'bs101', '0003', 5 ,'2010-01-01' UNION ALL
SELECT 4 ,'bs102', '0002', 5 ,'2010-02-01' UNION ALL
SELECT 5 ,'bs102', '0004', 5 ,'2010-02-01' UNION ALL
SELECT 6 ,'bs102', '0005', 5 ,'2010-02-01'
CREATE table tableB(id INT,tableAId INT,price DECIMAL(18,2),date DATETIME)
INSERT INTO tableB
SELECT 1, 1, 10.00, '2010-01-01' UNION ALL
SELECT 2, 2, 10.00, '2010-01-01' UNION ALL
SELECT 3, 3, 10.00, '2010-01-01' UNION ALL
SELECT 4, 4, 12.00, '2010-02-01' UNION ALL
SELECT 5, 5, 10.00, '2010-02-01' UNION ALL
SELECT 6, 6, 10.00, '2010-02-01'
DROP TABLE tableB
想要的结果:
比价表编号 金额 可比金额(单价*数量) 可比降低额(上次单价*本次数量-可比金额) 日期
bs101 150.00 0.00 0.00 2010-01-01
bs102 160.00 60.00 -10.00 2010-02-01
 
SELECT MAX(bjbbh) AS bjbbh,SUM(summ) AS he,SUM(aa) cz,MAX(date) AS date FROM 
(
SELECT a.bjbbh,
SUM(a.sl*b.price) summ,
CASE WHEN a.id IN (SELECT MAX(a.id) FROM tableA a,tableA c WHERE a.id<>c.id 
AND a.bjbbh<>c.bjbbh
AND a.date<>c.date
AND a.wzdm=c.wzdm)  
 THEN a.sl*b.price ELSE 0.00 END aa,
/*CASE WHEN a.id IN (SELECT MAX(a.id) FROM tableA a,tableA c WHERE a.id<>c.id 
AND a.bjbbh<>c.bjbbh
AND a.date<>c.date
AND a.wzdm=c.wzdm) 
THEN 
*/ 
b.date
FROM tableA a, tableB b,tableA c
WHERE a.id=b.tableAId
AND c.id=b.tableAId
GROUP BY a.bjbbh,b.date,a.id,c.id,a.date,c.date,a.sl,b.price
) bb
GROUP BY bjbbh--结果
bjbbh   he      cz        date
bs101 150.00 0.00 2010-01-01 00:00:00.000
bs102 160.00 60.00 2010-02-01 00:00:00.000
先做这么一部分，注释部分明天有空研究~

Answer 3

-- 物资采购总费用表
Select a.bjbbh, sum(a.sl*b.price) as SumCost
From [Table A] a inner Join [Table B] b on b.TableAId = a.Id
group by a.bjbbh
-- 物资采购全表
Select a.id, a.bjbbh, a.wzdm, a.sl, b.price, a.date 
From [Table A] a inner Join [Table B] b on b.TableAId = a.Id
-- 物资采购比较表
-- 从物资采购全表中查找每一笔在先前曾经被使用过的物资采购计划，并返回其前一条计划的id编号
Select a.id, a.bjbbh, a.wzdm, a.sl, a.price, a.date,max(b.id) as PrevId 
from [物资采购全表] a inner Join [Table A] b on b.wzdm=a.wzdm and b.date<a.date 
group by a.id, a.bjbbh, a.wzdm, a.sl, a.price, a.date
-- 物资采购单价比较表
Select a.Id, a.bjbbh, a.wzdm, a.sl, a.price, a.date, a.PrevId, b.price as PrevPrice 
From [物资采购比较表] a innerJoin [Table B] b on b.TableAId = a.PrevId
--比价表
Select a.bjbbh as [比价表编号], b.SumCost as [金额], 
Sum(a.sl*a.price) as [可比金额(单价*数量)],
Sum((a.PrevPrice-a.Price)*a.sl) as [可比降低额(上次单价*本次数量-可比金额)],
a.date as [日期]
From [物资采购单价比较表] a
inner Join [物资采购总费用表] b on b.bjbbh = a.bjbbh
group by a.bjbbh, b.SumCost, a.date
从前往后依次用查询语句替换红色的虚表即可

Answer 4

其实就是一层一层的嵌套查询,只是为了清楚起见才拆分成几个小的查询的，合并起来大致是--比价表
Select a.bjbbh as [比价表编号], b.SumCost as [金额], 
Sum(a.sl*a.price) as [可比金额(单价*数量)],
Sum((a.PrevPrice-a.Price)*a.sl) as [可比降低额(上次单价*本次数量-可比金额)],
a.date as [日期]
From 
--物资采购单价比较表
( 
Select a.Id, a.bjbbh, a.wzdm, a.sl, a.price, a.date, a.PrevId, b.price as PrevPrice 
From 
  --物资采购比较表
  (
    -- 从物资采购全表中查找每一笔在先前曾经被使用过的物资采购计划，并返回其前一条计划的id编号
    Select a.id, a.bjbbh, a.wzdm, a.sl, a.price, a.date,max(b.id) as PrevId 
    from 
    -- 物资采购全表
    (
    Select a.id, a.bjbbh, a.wzdm, a.sl, b.price, a.date 
    From [Table A] a inner Join [Table B] b on b.TableAId = a.Id
    ) a 
    -- 物资采购全表 end
    inner Join [Table A] b on b.wzdm=a.wzdm and b.date<a.date 
    group by a.id, a.bjbbh, a.wzdm, a.sl, a.price, a.date
  ) a 
  --物资采购比较表 end
innerJoin [Table B] b on b.TableAId = a.PrevId
)a
--物资采购单价比较表 end
inner Join 
-- 物资采购总费用表
(
Select a.bjbbh, sum(a.sl*b.price) as SumCost
From [Table A] a inner Join [Table B] b on b.TableAId = a.Id
group by a.bjbbh
) b 
-- 物资采购总费用表 end
on b.bjbbh = a.bjbbh
group by a.bjbbh, b.SumCost, a.date
没有测试过，可能会有语法错误，不过大致的思路就是如此

Answer 5

10楼，我仔细看了一下你提供的语句，正是我要的大概意思，可有一个问题没解决，因此结果也不正确，如果有一条物资是第一次比价，则不会有前期PREVID及PREVPRICE,这个问题怎么处理？