设报价记录表名为Tab,某项目ID为:BID001Select T1.bid_id,T1.bid_userid,T1.bid_username,T1.Time, Price=(select bid_price From Tab T2 Where T2.bid_id=T1.bid_id and T2.bid_userid=T1.bid_userid and T2.bid_username=T1.bid_username and T2.Time=T1.Time) From (Select bid_id,bid_userid,bid_username,max(bid_price_time) as Time, From Tab Where bid_id='BID001' Group By bid_id,bid_userid,bid_username )T1
忘记排序了elect T1.bid_id,T1.bid_userid,T1.bid_username,T1.Time, Price=(select bid_price From Tab T2 Where T2.bid_id=T1.bid_id and T2.bid_userid=T1.bid_userid and T2.bid_username=T1.bid_username and T2.Time=T1.Time) From (Select bid_id,bid_userid,bid_username,max(bid_price_time) as Time, From Tab Where bid_id='BID001' Group By bid_id,bid_userid,bid_username )T1 Order By (select bid_price From Tab T2 Where T2.bid_id=T1.bid_id and T2.bid_userid=T1.bid_userid and T2.bid_username=T1.bid_username and T2.Time=T1.Time)
--项目报价表tbl_user_prices if 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_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 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 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 ) select u.* 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 order by bid_id, bid_userid, bid_price;
--项目报价表tbl_user_prices if 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_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'; 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_price from b;
sql="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;"Set rs=Server.CreateObject("Adodb.RecordSet") rs.open sql, conn,1,1 access数据库可以用吗,这样使用怎么错误?!!!
报价价格 bid_price报价时间 bid_price_time
我取某一个项目的中标价就是先将每一家供应商针对本项目的最后一次报价提取出来,然后再排序。
Price=(select bid_price From Tab T2
Where T2.bid_id=T1.bid_id
and T2.bid_userid=T1.bid_userid
and T2.bid_username=T1.bid_username
and T2.Time=T1.Time)
From
(Select bid_id,bid_userid,bid_username,max(bid_price_time) as Time,
From Tab
Where bid_id='BID001'
Group By bid_id,bid_userid,bid_username
)T1
Price=(select bid_price From Tab T2
Where T2.bid_id=T1.bid_id
and T2.bid_userid=T1.bid_userid
and T2.bid_username=T1.bid_username
and T2.Time=T1.Time)
From
(Select bid_id,bid_userid,bid_username,max(bid_price_time) as Time,
From Tab
Where bid_id='BID001'
Group By bid_id,bid_userid,bid_username
)T1
Order By
(select bid_price From Tab T2
Where T2.bid_id=T1.bid_id
and T2.bid_userid=T1.bid_userid
and T2.bid_username=T1.bid_username
and T2.Time=T1.Time)
--项目报价表tbl_user_prices
if 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_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
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
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
)
select u.*
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
order by bid_id, bid_userid, bid_price;
我主贴的问题大家可能没看到,我现的问题是:
在比如说有15个招标项目的中标价已经有了,我要求的是中标价之和。
不是项目的中标价,请问如何写SQL语句。
--项目报价表tbl_user_prices
if 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_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';
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_price
from b;
/*
执行结果:
total_price
---------------------
8001.66
*/
(
--计算每个项目每个商家报价的最后时间,用于确定后面的中标价格
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;"Set rs=Server.CreateObject("Adodb.RecordSet")
rs.open sql, conn,1,1
access数据库可以用吗,这样使用怎么错误?!!!