一个比较复杂的SQL语句
做了一个招标竞价系统
每个招标项目假设有5家供应商报价，那么将5家供应商最后报的[b][/b]价格进行比较，谁低谁中标。
现在的问题是，我要把每一个招标项目的中标价进行合计，请问，SQL语句该如何写呢？
[解决办法]
--项目报价表tbl_user_pricesif object_id('tbl_user_prices') is not null drop table tbl_user_prices;gocreate 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_pricesselect 1, 1, '供应商1', 4000.10, '2012-02-04 08:00:08' union allselect 1, 1, '供应商1', 8000.60, '2012-02-01 08:00:08' union allselect 1, 1, '供应商1', 5874.10, '2012-02-04 11:00:08' union allselect 1, 1, '供应商1', 6354.10, '2012-02-23 08:00:08' union allselect 1, 2, '供应商2', 3254.55, '2012-02-03 08:00:08' union allselect 1, 2, '供应商2', 4444.55, '2012-02-12 08:00:08' union allselect 1, 2, '供应商2', 6666.55, '2012-02-13 08:25:08' union allselect 1, 2, '供应商2', 5412.55, '2012-02-14 08:30:08' union allselect 1, 2, '供应商2', 6547.55, '2012-02-25 11:00:08' union allselect 1, 2, '供应商2', 5587.55, '2012-02-21 12:00:08' union allselect 1, 2, '供应商2', 4257.55, '2012-02-09 13:00:08' union allselect 1, 3, '供应商3', 7800.23, '2012-03-12 08:00:08' union allselect 1, 3, '供应商3', 7899.23, '2012-04-12 09:00:08' union allselect 1, 3, '供应商3', 7720.23, '2012-02-20 11:00:08' union allselect 1, 3, '供应商3', 7500.23, '2012-02-28 08:00:08' union allselect 1, 4, '供应商4', 300.12, '2012-02-15 08:00:08' union allselect 1, 4, '供应商4', 1245.12, '2012-02-16 08:00:08' union allselect 1, 4, '供应商4', 1333.12, '2012-02-17 08:00:08' union allselect 1, 4, '供应商4', 4000.12, '2012-02-18 08:00:08' union allselect 1, 5, '供应商5', 1225.99, '2012-03-01 08:00:08' union allselect 1, 5, '供应商5', 621.99, '2012-03-02 08:00:08' union allselect 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 allselect 2, 2, '供应商2', 8000.87, '2012-02-08 08:00:08' union allselect 2, 3, '供应商3', 8520.23, '2012-02-12 08:00:08' union allselect 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';gowith 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_pricefrom b;