create table stock (stock_id int, stock_name varchar(20))insert into stock values (1,'东仓'),(2,'西仓')create table stock_kc (id int, pro_name varchar(20), pro_time char(10), pro_stock int, pro_amount decimal(14,2))insert into stock_kc values (1,'电视','2014-01-01',1,5), (2,'电视','2014-01-01',1,3), (3,'电视','2014-01-02',2,1), (4,'空调','2014-01-01',1,1), (5,'空调','2014-01-05',1,5), (6,'空调','2014-01-05',2,3), (7,'空调','2014-01-05',2,6), (8,'空调','2014-01-02',2,2)select * from stock_kc--先是以时间为准,得到最大时间对应结存量,如果有相同的时间,则取最大时间的最大ID对应的结存数量with ta as ( select distinct pro_name as 品名, (select top 1 pro_amount from stock_kc c where c.pro_stock=b.pro_stock and c.pro_name=b.pro_name and c.pro_stock=1 order by pro_time desc,id desc) as '东仓数量', (select top 1 pro_amount from stock_kc c where c.pro_stock=b.pro_stock and c.pro_name=b.pro_name and c.pro_stock=2 order by pro_time desc,id desc) as '西仓数量' from stock a,stock_kc b where a.stock_id=b.pro_stock) select 品名,max(东仓数量) as '东仓数量',MAX(西仓数量) as '西仓数量' from ta group by 品名 在SQL2000下应该满足。
(stock_id int,
stock_name varchar(20))insert into stock values
(1,'东仓'),(2,'西仓')create table stock_kc
(id int,
pro_name varchar(20),
pro_time char(10),
pro_stock int,
pro_amount decimal(14,2))insert into stock_kc values
(1,'电视','2014-01-01',1,5),
(2,'电视','2014-01-01',1,3),
(3,'电视','2014-01-02',2,1),
(4,'空调','2014-01-01',1,1),
(5,'空调','2014-01-05',1,5),
(6,'空调','2014-01-05',2,3),
(7,'空调','2014-01-05',2,6),
(8,'空调','2014-01-02',2,2)select * from stock_kc--先是以时间为准,得到最大时间对应结存量,如果有相同的时间,则取最大时间的最大ID对应的结存数量with ta as
(
select distinct pro_name as 品名,
(select top 1 pro_amount from stock_kc c
where c.pro_stock=b.pro_stock and c.pro_name=b.pro_name
and c.pro_stock=1 order by pro_time desc,id desc) as '东仓数量',
(select top 1 pro_amount from stock_kc c
where c.pro_stock=b.pro_stock and c.pro_name=b.pro_name
and c.pro_stock=2
order by pro_time desc,id desc) as '西仓数量'
from stock a,stock_kc b
where a.stock_id=b.pro_stock)
select 品名,max(东仓数量) as '东仓数量',MAX(西仓数量) as '西仓数量' from ta
group by 品名
在SQL2000下应该满足。