求教sql里用group by分类汇总后再求和的问题

[zk911]

例如用如下sql语句

SELECT MAX(CONVERT(numeric(12, 2), AL_rsdArea.fillfund)) AS 已使用金额
FROM AC_Room INNER JOIN
AC_Fund ON AC_Room.acr_id = AC_Fund.acf_card INNER JOIN
AL_House ON AC_Room.acr_house = AL_House.alh_id INNER JOIN
AL_rsdArea ON AC_Room.acr_rsdarea = AL_rsdArea.alr_id INNER JOIN
RR_List ON AC_Room.acr_id = RR_List.rrl_card
WHERE (AL_rsdArea.r_canton = '1')
GROUP BY AL_rsdArea.alr_id

得到结果
8000
12000
3000
0

请问用什么语句可以把这四个记录相加再得到一个汇总的结果 23000呢？

[pt1314917]

select sum(已使用金额) from
(SELECT MAX(CONVERT(numeric(12, 2), AL_rsdArea.fillfund)) AS 已使用金额 
FROM AC_Room INNER JOIN 
      AC_Fund ON AC_Room.acr_id = AC_Fund.acf_card INNER JOIN 
      AL_House ON AC_Room.acr_house = AL_House.alh_id INNER JOIN 
      AL_rsdArea ON AC_Room.acr_rsdarea = AL_rsdArea.alr_id INNER JOIN 
      RR_List ON AC_Room.acr_id = RR_List.rrl_card 
WHERE (AL_rsdArea.r_canton = '1') 
GROUP BY AL_rsdArea.alr_id)a 

[xxxxxchener]

select sum(总合) from (SELECT MAX(CONVERT(numeric(12, 2), AL_rsdArea.fillfund)) AS 已使用金额 FROM AC_Room INNER JOIN 
      AC_Fund ON AC_Room.acr_id = AC_Fund.acf_card INNER JOIN 
      AL_House ON AC_Room.acr_house = AL_House.alh_id INNER JOIN 
      AL_rsdArea ON AC_Room.acr_rsdarea = AL_rsdArea.alr_id INNER JOIN 
      RR_List ON AC_Room.acr_id = RR_List.rrl_card 
WHERE (AL_rsdArea.r_canton = '1') 
GROUP BY AL_rsdArea.alr_id )

[zk911]

谢谢 正确

请问GROUP BY AL_rsdArea.alr_id)a 这个）a是什么意思？

[zk911]

select sum(已使用金额) from (SELECT MAX(CONVERT(numeric(12, 2), AL_rsdArea.fillfund)) AS 已使用金额 FROM AC_Room INNER JOIN
AC_Fund ON AC_Room.acr_id = AC_Fund.acf_card INNER JOIN
AL_House ON AC_Room.acr_house = AL_House.alh_id INNER JOIN
AL_rsdArea ON AC_Room.acr_rsdarea = AL_rsdArea.alr_id INNER JOIN
RR_List ON AC_Room.acr_id = RR_List.rrl_card
WHERE (AL_rsdArea.r_canton = '1')
GROUP BY AL_rsdArea.alr_id )


把“总合”改为“已使用金额” 就对了

谢了 有学习了一个新的知识

[lsj_zrp]

select sum(已使用金额) as 已使用金额,alr_id
from
(SELECT MAX(CONVERT(numeric(12, 2), AL_rsdArea.fillfund)) AS 已使用金额,AL_rsdArea.alr_id
FROM AC_Room INNER JOIN
AC_Fund ON AC_Room.acr_id = AC_Fund.acf_card INNER JOIN
AL_House ON AC_Room.acr_house = AL_House.alh_id INNER JOIN
AL_rsdArea ON AC_Room.acr_rsdarea = AL_rsdArea.alr_id INNER JOIN
RR_List ON AC_Room.acr_id = RR_List.rrl_card
WHERE (AL_rsdArea.r_canton = '1')
GROUP BY AL_rsdArea.alr_id ) a
group by alr_id with rollup