能用查询或视图解决取得最后成本的问题吗
财务工作中遇到这类问题，同样一个原料，如果去年的库存和今年的库存一样，但因为去年的成本和今年的成本不一样，所以，哪怕同样的料同样的数量成本金额是不一样的。以下搞了一个简单的例子：
Month Material Cost
201205 A 4
201206 A 4
201207 A 5
201208 A 5
201209 A 6
201210 A 6
201211 A 6
201212 A 6
201205 B 7.8
201206 B 7.8
201207 B 7.8
201208 B 120
201209 B 120
201210 B 7.8
201211 B 7.8
201212 B 7.8
我想得到每一期的单位成本，重复的就去掉，类似以下的结果：
Month MaterialCost
201205 A 4
201207 A 5
201209 A 6
201205 B 7.8
201208 B 120
201210 B 7.8
假设我的表1的名字叫TBCost，想得到表2的结果，我昨天写了段SQL语句，
select MIN(MONTH) as month,
material,
cost
from TBCost
group by Material, cost
结果A的结果是对了，屁颠屁颠高兴了好一把，show给N个同事看了。但后来一想，不对，B料会出错！！B料的结果变成
MonthMaterial Cost
201205 B 7.8
201208 B 120
正好把最后一期的成本搞错了，而且这种情况很具代表性，比如：应该就是7.8元的成本，但是计算错误变成了120元，后来发现又改回7.8元！所以如何写这段语句呢？
[解决办法]
思路都是一样的。
with tab as
(
select '201205' Month,'A' Material,4 Cost union all
select '201207','A',4 union all
select '201208','A',5 union all
select '201209','A',5 union all
select '201210','A',6 union all
select '201211','A',6 union all
select '201212','A',6 union all
select '201205','B',7.8 union all
select '201206','B',7.8 union all
select '201207','B',7.8 union all
select '201208','B',120 union all
select '201209','B',120 union all
select '201210','B',7.8 union all
select '201211','B',7.8
),tab2 as
(
select *, ROW_NUMBER() over(order by Material,month) as 辅助列一
from tab
),tab3 as
(
select *, 辅助列一 - ROW_NUMBER() over(partition by Material,Cost order by Material,month) as 辅助列二
from tab2
)
select min(month) month,Material,Cost
from tab3
group by Material,辅助列二,Cost
order by Material,month
USE test
GO
-->生成表tb
if object_id('tb') is not null
drop table tb
Go
Create table tb([Month] nvarchar(6),[Material] nvarchar(1),[Cost] nvarchar(3))
Insert into tb
Select N'201205',N'A',N'4'
Union all Select N'201206',N'A',N'4'
Union all Select N'201207',N'A',N'5'
Union all Select N'201208',N'A',N'5'
Union all Select N'201209',N'A',N'6'
Union all Select N'201210',N'A',N'6'
Union all Select N'201211',N'A',N'6'
Union all Select N'201212',N'A',N'6'
Union all Select N'201205',N'B',N'7.8'
Union all Select N'201206',N'B',N'7.8'
Union all Select N'201207',N'B',N'7.8'
Union all Select N'201208',N'B',N'120'
Union all Select N'201209',N'B',N'120'
Union all Select N'201210',N'B',N'7.8'
Union all Select N'201211',N'B',N'7.8'
Union all Select N'201212',N'B',N'7.8'
select * from tb AS a
WHERE NOT EXISTS(SELECT 1 FROM tb AS x
WHERE x.Material=x.Material
AND x.Cost=a.Cost
AND x.Month<a.Month
)
OR EXISTS(SELECT 1 FROM tb AS x
WHERE x.Material=a.Material
AND x.Month=a.Month-1
AND x.Cost<>a.Cost
)
/*
Month Material Cost
------ -------- ----
201205 A 4
201207 A 5
201209 A 6
201205 B 7.8
201208 B 120
201210 B 7.8
*/
