SELECT sum(zcz) as zcz,sum(zyyw) as zyyw,sum(zycb) as zycb,sum(fyhj) as fyhj,sum(lrzz) as lrzz,
sum(yjsds) as yjsds,sum(gcjj) as gcjj,sum(zfbt) as zfbt,sum(jzc) as jzc,
a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd FROM qy_qybb as a join qy_qyjbqk b on a.qyid=b.qyid
where b.ssxq='泉州市' and b.jbid<>'3818' and a.nf='2007' and
(a.jd='1季度'or a.jd='2季度' or a.jd='3季度' or a.jd='4季度') and estate<>'00'
group by a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd请问聚合函数后如何再分组,我只要 qymc相同的组在一起就可以了?望回复~~~
sum(yjsds) as yjsds,sum(gcjj) as gcjj,sum(zfbt) as zfbt,sum(jzc) as jzc,
a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd FROM qy_qybb as a join qy_qyjbqk b on a.qyid=b.qyid
where b.ssxq='泉州市' and b.jbid<>'3818' and a.nf='2007' and
(a.jd='1季度'or a.jd='2季度' or a.jd='3季度' or a.jd='4季度') and estate<>'00'
group by a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd请问聚合函数后如何再分组,我只要 qymc相同的组在一起就可以了?望回复~~~
select qymc , sum(zcz) , 其他的sum... from
(
SELECT sum(zcz) as zcz,sum(zyyw) as zyyw,sum(zycb) as zycb,sum(fyhj) as fyhj,sum(lrzz) as lrzz,
sum(yjsds) as yjsds,sum(gcjj) as gcjj,sum(zfbt) as zfbt,sum(jzc) as jzc,
a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd FROM qy_qybb as a join qy_qyjbqk b on a.qyid=b.qyid
where b.ssxq='泉州市' and b.jbid <> '3818' and a.nf='2007' and
(a.jd='1季度'or a.jd='2季度' or a.jd='3季度' or a.jd='4季度') and estate <> '00'
group by a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd
) t
group by qymc
SELECT sum(zcz) as zcz,sum(zyyw) as zyyw,sum(zycb) as zycb,sum(fyhj) as fyhj,sum(lrzz) as lrzz,
sum(yjsds) as yjsds,sum(gcjj) as gcjj,sum(zfbt) as zfbt,sum(jzc) as jzc, qymc
FROM qy_qybb as a join qy_qyjbqk b on a.qyid=b.qyid
where b.ssxq='泉州市' and b.jbid <> '3818' and a.nf='2007' and
(a.jd='1季度'or a.jd='2季度' or a.jd='3季度' or a.jd='4季度') and estate <> '00'
group by qymc
用第二方法不行,因为前方使用了聚合函数一定要出现在group中。
select qymc , zcz , 其他的项 from
(
SELECT sum(zcz) as zcz,sum(zyyw) as zyyw,sum(zycb) as zycb,sum(fyhj) as fyhj,sum(lrzz) as lrzz,
sum(yjsds) as yjsds,sum(gcjj) as gcjj,sum(zfbt) as zfbt,sum(jzc) as jzc,
a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd FROM qy_qybb as a join qy_qyjbqk b on a.qyid=b.qyid
where b.ssxq='泉州市' and b.jbid <> '3818' and a.nf='2007' and
(a.jd='1季度'or a.jd='2季度' or a.jd='3季度' or a.jd='4季度') and estate <> '00'
group by a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd
) as t
group by qymc这样就对了,老乌龟笔误了吧.或者将子查询放入到临时表中,一样的道理
select qymc ,sum(zcz) as zcz,sum(zyyw) as zyyw,sum(zycb) as zycb,sum(fyhj) as fyhj,sum(lrzz) as lrzz,
sum(yjsds) as yjsds,sum(gcjj) as gcjj,sum(zfbt) as zfbt,sum(jzc) as jzc from
(
SELECT sum(zcz) as zcz,sum(zyyw) as zyyw,sum(zycb) as zycb,sum(fyhj) as fyhj,sum(lrzz) as lrzz,
sum(yjsds) as yjsds,sum(gcjj) as gcjj,sum(zfbt) as zfbt,sum(jzc) as jzc,
a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd FROM qy_qybb as a join qy_qyjbqk b on a.qyid=b.qyid
where b.ssxq='泉州市' and b.jbid <> '3818' and a.nf='2007' and
(a.jd='1季度'or a.jd='2季度' or a.jd='3季度' or a.jd='4季度'
)
and estate <> '00'
group by a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd) t
group by qymc
sum(yjsds) as yjsds,sum(gcjj) as gcjj,sum(zfbt) as zfbt,sum(jzc) as jzc from
(
SELECT sum(zcz) as zcz,sum(zyyw) as zyyw,sum(zycb) as zycb,sum(fyhj) as fyhj,sum(lrzz) as lrzz,
sum(yjsds) as yjsds,sum(gcjj) as gcjj,sum(zfbt) as zfbt,sum(jzc) as jzc,
a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd FROM qy_qybb as a join qy_qyjbqk b on a.qyid=b.qyid
where b.ssxq='泉州市' and b.jbid <> '3818' and a.nf='2007' and
(a.jd='1季度'or a.jd='2季度' or a.jd='3季度' or a.jd='4季度'
)
and estate <> '00'
group by a.qyid,a.bbid,qyno,qymc,ssxq,nf,jd,nf+jd) as t
group by qymc