select code, (select top 1 num1 from t where code=a.code order by id) num1, (select sum(num2) from t where code=a.code) num2, (select top 1 num3 from t where code=a.code order by id desc) num3 from t a
declare @tb table(id int,code varchar(10),num1 decimal(10,2),num2 decimal(10,2),num3 decimal(10,2))insert into @tb select 1 , '001', 12.5 ,14, 26.5 union all select 2 , '001' , 26.5 , 8 , 34.5 union all select 3 , '001' , 34.5 , 7 , 41.5 union all select 4 , '002' , 100.5 ,12 , 88.5 union all select 5 , '002' , 88.5 , 23 , 65.5 union all select 6 , '002' , 65.5 , 7.5 , 58 union all select 7 , '003' , 12.2 , 18 , 30.5 union all select 8 , '003' , 30.5 , -5 , 25.5 union all select 9 , '003' , 25.5 , 0.8 , 26.3 union all select 10, '001' , 41.5, 11 ,52.5--select * from @ select a.code, (select num1 from @tb where id=a.minid) as Num1, a.num2, (select num3 from @tb where id=a.maxid) as num3 from ( select a.code, min(a.id) as minid, max(a.id) as maxid, sum(a.num2) as num2 from @tb a group by a.code )a测试结果 code Num1 num2 num3 ---------- ------------ ---------------------------------------- ------------ 001 12.50 40.00 52.50 002 100.50 42.50 58.00 003 12.20 13.80 26.30(3 row(s) affected)
偶忘了加group by: select code, (select top 1 num1 from t where code=a.code order by id) num1, (select sum(num2) from t where code=a.code) num, (select top 1 num3 from t where code=a.code order by id desc) num3 from t a group by code
取最小id 的num1 的值,
取最大id 的 num3 值,
再取num2 的和
上面的是數據庫改變情況表,
數據庫第更新一次,
都會插入一筆資料進去,
而我要求的是哪一段時間
的期初庫存和期末庫存,
這個表另外還有時間字段,只是沒有寫上去罷了
(select top 1 num1 from t where code=a.code order by id) num1,
(select sum(num2) from t where code=a.code) num2,
(select top 1 num3 from t where code=a.code order by id desc) num3
from t a
1 , '001', 12.5 ,14, 26.5 union all select
2 , '001' , 26.5 , 8 , 34.5 union all select
3 , '001' , 34.5 , 7 , 41.5 union all select
4 , '002' , 100.5 ,12 , 88.5 union all select
5 , '002' , 88.5 , 23 , 65.5 union all select
6 , '002' , 65.5 , 7.5 , 58 union all select
7 , '003' , 12.2 , 18 , 30.5 union all select
8 , '003' , 30.5 , -5 , 25.5 union all select
9 , '003' , 25.5 , 0.8 , 26.3 union all select
10, '001' , 41.5, 11 ,52.5--select * from @
select
a.code,
(select num1 from @tb where id=a.minid) as Num1,
a.num2,
(select num3 from @tb where id=a.maxid) as num3
from
(
select
a.code,
min(a.id) as minid,
max(a.id) as maxid,
sum(a.num2) as num2
from @tb a
group by a.code
)a测试结果
code Num1 num2 num3
---------- ------------ ---------------------------------------- ------------
001 12.50 40.00 52.50
002 100.50 42.50 58.00
003 12.20 13.80 26.30(3 row(s) affected)
select code,
(select top 1 num1 from t where code=a.code order by id) num1,
(select sum(num2) from t where code=a.code) num,
(select top 1 num3 from t where code=a.code order by id desc) num3
from t a
group by code
這個方法很管用的,
LoveSQL(ligg) 的方法也不錯,用臨時表,只是比起風雲的方法稍微麻煩一點
謝謝啦