日期:2014-05-17  浏览次数:20636 次

请教一个sql联合查询group by的问题
我用这个联合查询得出下面的结果

SELECT A.PARNUM,A.PARUNIT,b.id,c.cbzj,d.sbmoney,d.id as id2 
FROM ysyfmoney A 
left outer JOIN HT_INDEX B ON(RTRIM(A.PARUNIT)=RTRIM(B.XYYF)) 
full outer join ht_cdzl c on (b.id=c.id) 
full outer join ht_sbcg d on (b.id=d.id) 
where (b.httype ='1' or b.httype ='5') and b.htzt = '2' and datediff ( day , b.xyqxd , GETDATE () ) < 0 

parnum parunit id cbzj sbmoney id2
7072 花琴 57 15000 NULL NULL
7072 花琴 133 50000 NULL NULL
7089 江苏菜根香餐饮有限公司 45 30000 NULL NULL
7089 江苏菜根香餐饮有限公司 121 45000 NULL NULL

现在我想得出下面的结果请问该怎么修改我的查询语句

parnum parunit countid sumcbzj sbmoney countid2
7072 花琴 2 65000 0 0
7089 江苏菜根香餐饮有限公司 2 75000 0 0

------解决方案--------------------
SQL code
select parnum, parunit,count(id) countid,sum(cbzj) sumcbzj,sum(sbmoney) sbmoney,
count(id2) countid2 from (SELECT A.PARNUM,A.PARUNIT,b.id,c.cbzj,d.sbmoney,d.id as id2  
FROM ysyfmoney A  
left outer JOIN HT_INDEX B ON(RTRIM(A.PARUNIT)=RTRIM(B.XYYF))  
full outer join ht_cdzl c on (b.id=c.id)  
full outer join ht_sbcg d on (b.id=d.id)  
where (b.httype ='1' or b.httype ='5') and b.htzt = '2' and datediff ( day , b.xyqxd , GETDATE () ) < 0  )a
group by parnum, parunit