# 求助求助！！！如何将查询出来的多条记录按照指定的两个字段汇总成一行记录

qq_207100010 2015-11-15 07:48:46

select FYear ,FAccountID,'B','' ,'111','111',
case when FPeriod='1' and FBeginBalance>0 then ABS(convert(money,FBeginBalance)) ELSE 0 end ,
case when FPeriod='1' and FBeginBalance<0 then ABS(convert(money,FBeginBalance)) ELSE 0 end ,
case when FPeriod='1' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='1' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='2' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='2' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='3' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='3' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='4' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='4' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='5' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='5' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='6' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='6' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='7' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='7' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='8' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='8' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='9' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='9' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='10' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='10' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='11' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='11' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='12' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='12' then convert(money,FDebit) ELSE 0 end,
case when FPeriod='13' then convert(money,FCredit) ELSE 0 end,
case when FPeriod='13' then convert(money,FDebit) ELSE 0 end,'0'
from t_Balance where FCurrencyID=1 and FYear='2008'and faccountid='1003'

...全文
47 点赞 收藏 2

2 条回复

Tiger_Zhao 2015-11-16
``````    SELECT FYear ,FAccountID,
SUM(M1) AS M1,
SUM(M2) AS M2,
...
FROM ( -- 原先的查询，给每个字段都指定列名
select FYear ,FAccountID,
case when FPeriod='1' and  FBeginBalance>0 then ABS(convert(money,FBeginBalance)) ELSE 0 end AS M1,
case when FPeriod='1' and  FBeginBalance<0 then ABS(convert(money,FBeginBalance)) ELSE 0 end AS M2,
...
) t
GROUP BY FYear ,FAccountID``````

qq_207100010 2015-11-15

2.1w+

MS-SQL Server 疑难问题

2015-11-15 07:48