今天偶尔看到一题,网址如下:
http://topic.csdn.net/t/20061023/11/5101874.html
感觉两位的回答是正确的,但总感觉不是很爽,花了点时间,整理了下
with cte as
select 1 mo,'收' me,1 nu union all
select 1,'收',2 union all
select 1,'付',2 union all
select 2,'收',1 union all
select 3,'付',1
),tempa as
(
select * ,
case me when '收' then 1 else 2 end row
from (
select mo,me,SUM(nu) nu from cte group by mo,me
) a
)
,ci as
(
select mo,me, nu = isnull((select top 1 nu from tempa c where c.mo = a.mo and c.me = b.me),0) from
(select mo from tempa group by mo ) a,
(select me from tempa group by me ) b
)
select * from ci union
select mo,'存',
sum(case when me = '付' then -nu else + nu end)
from ci group by mo order by mo, me desc