--项目报价表tbl_user_prices
if object_id('tbl_user_prices') is not null
   drop table tbl_user_prices;
go

create table tbl_user_prices
(
   bid_id int not null,
   bid_userid int not null,
   bid_username nvarchar(30) not null,
   bid_price money not null,
   bid_price_time datetime not null,
   constraint PK_USER_PRICE primary key(bid_id, bid_userid, bid_price_time)
);
go

--插入测试数据
insert into tbl_user_prices
select 1, 1, '供应商1', 4000.10, '2012-02-04 08:00:08' union all
select 1, 1, '供应商1', 8000.60, '2012-02-01 08:00:08' union all
select 1, 1, '供应商1', 5874.10, '2012-02-04 11:00:08' union all
select 1, 1, '供应商1', 6354.10, '2012-02-23 08:00:08' union all
select 1, 2, '供应商2', 3254.55, '2012-02-03 08:00:08' union all
select 1, 2, '供应商2', 4444.55, '2012-02-12 08:00:08' union all
select 1, 2, '供应商2', 6666.55, '2012-02-13 08:25:08' union all
select 1, 2, '供应商2', 5412.55, '2012-02-14 08:30:08' union all
select 1, 2, '供应商2', 6547.55, '2012-02-25 11:00:08' union all
select 1, 2, '供应商2', 5587.55, '2012-02-21 12:00:08' union all
select 1, 2, '供应商2', 4257.55, '2012-02-09 13:00:08' union all
select 1, 3, '供应商3', 7800.23, '2012-03-12 08:00:08' union all
select 1, 3, '供应商3', 7899.23, '2012-04-12 09:00:08' union all
select 1, 3, '供应商3', 7720.23, '2012-02-20 11:00:08' union all
select 1, 3, '供应商3', 7500.23, '2012-02-28 08:00:08' union all
select 1, 4, '供应商4', 300.12, '2012-02-15 08:00:08' union all
select 1, 4, '供应商4', 1245.12, '2012-02-16 08:00:08' union all
select 1, 4, '供应商4', 1333.12, '2012-02-17 08:00:08' union all
select 1, 4, '供应商4', 4000.12, '2012-02-18 08:00:08' union all
select 1, 5, '供应商5', 1225.99, '2012-03-01 08:00:08' union all
select 1, 5, '供应商5', 621.99, '2012-03-02 08:00:08' union all
select 1, 5, '供应商5', 2000.99, '2012-03-03 08:00:08' union all    --项目1的中标价
select 2, 1, '供应商1', 10000.45, '2012-02-08 08:00:08' union all
select 2, 2, '供应商2', 8000.87, '2012-02-08 08:00:08' union all
select 2, 3, '供应商3', 8520.23, '2012-02-12 08:00:08' union all
select 2, 4, '供应商4', 6000.67, '2012-02-04 08:00:08' union all    --项目2的中标价
select 2, 5, '供应商5', 7541.34, '2012-03-09 08:00:08';
go

with a as
(
    --计算每个项目每个商家报价的最后时间，用于确定后面的中标价格
    select bid_id, bid_userid, max(bid_price_time) as bid_price_time
    from tbl_user_prices
    group by bid_id, bid_userid
),
--连接原始表得到每个项目中标价格
b as
(
    select u.bid_id, min(u.bid_price) as final_program_price
    from tbl_user_prices u
        inner join a
            on u.bid_id = a.bid_id and 
               u.bid_userid = a.bid_userid and 
               u.bid_price_time = a.bid_price_time
    group by u.bid_id
)
--合计所有项目的中标价
select sum(final_program_price) as total_price
from b;