select long_name, count(xingming) from
danweimingcheng left join yonghubiao
on short_name = danwei
where begda < '20090702'
group by long_name
select sum(case when JiFenXiangMu = '专利技术' then JiFenFenZhi else 0 end) as ZhuanLiJiShu,
sum(case when JiFenXiangMu = '技术改进建议' then JiFenFenZhi else 0 end) as JiShuGaiJinJianYi,
sum(case when JiFenXiangMu = '技术课题攻关' then JiFenFenZhi else 0 end) as JiShuKeTiGongGuan,
sum(case when JiFenXiangMu = '管理改进建议' then JiFenFenZhi else 0 end) as GuanLiGaiJinJianYi,
sum(case when JiFenXiangMu = '技术标准' then JiFenFenZhi else 0 end) as JiShuBiaoZhun,
sum(case when JiFenXiangMu = '管理(工作)标准' then JiFenFenZhi else 0 end) as GuanLiGongZuoBiaoZhun,
sum(case when JiFenXiangMu = '技术方案' then JiFenFenZhi else 0 end) as JiShuFangAn,
sum(case when JiFenXiangMu = '标准作业指导书' then JiFenFenZhi else 0 end) as BiaoZhunZuoYeZhiDaoShu,
sum(case when JiFenXiangMu = '适用法律法规' then JiFenFenZhi else 0 end) as ShiYongFaLvFaGui,
sum(case when JiFenXiangMu = '技术论文' then JiFenFenZhi else 0 end) as JiShuLunWen,
sum(case when JiFenXiangMu = '管理论文' then JiFenFenZhi else 0 end) as GuanLiLunWen,
sum(JiFenFenZhi) as NianDuZongJiFen,
sum(JiFenFenZhi)/count(xingming) as RenJunJiFen
from
danweimingcheng left join JiShuChuangXinJiFen_Master
on long_name = TianXieRenDW
group by long_name
我想把它们做成一条查询
其中第二个select 里的sum(JiFenFenZhi)/count(xingming) as RenJunJiFen,这里的count(xingming)实际上是取自第一条select里的count(xingming)。
非常感谢!
分数不多,包涵!
danweimingcheng left join yonghubiao
on short_name = danwei
where begda < '20090702'
group by long_name
select sum(case when JiFenXiangMu = '专利技术' then JiFenFenZhi else 0 end) as ZhuanLiJiShu,
sum(case when JiFenXiangMu = '技术改进建议' then JiFenFenZhi else 0 end) as JiShuGaiJinJianYi,
sum(case when JiFenXiangMu = '技术课题攻关' then JiFenFenZhi else 0 end) as JiShuKeTiGongGuan,
sum(case when JiFenXiangMu = '管理改进建议' then JiFenFenZhi else 0 end) as GuanLiGaiJinJianYi,
sum(case when JiFenXiangMu = '技术标准' then JiFenFenZhi else 0 end) as JiShuBiaoZhun,
sum(case when JiFenXiangMu = '管理(工作)标准' then JiFenFenZhi else 0 end) as GuanLiGongZuoBiaoZhun,
sum(case when JiFenXiangMu = '技术方案' then JiFenFenZhi else 0 end) as JiShuFangAn,
sum(case when JiFenXiangMu = '标准作业指导书' then JiFenFenZhi else 0 end) as BiaoZhunZuoYeZhiDaoShu,
sum(case when JiFenXiangMu = '适用法律法规' then JiFenFenZhi else 0 end) as ShiYongFaLvFaGui,
sum(case when JiFenXiangMu = '技术论文' then JiFenFenZhi else 0 end) as JiShuLunWen,
sum(case when JiFenXiangMu = '管理论文' then JiFenFenZhi else 0 end) as GuanLiLunWen,
sum(JiFenFenZhi) as NianDuZongJiFen,
sum(JiFenFenZhi)/count(xingming) as RenJunJiFen
from
danweimingcheng left join JiShuChuangXinJiFen_Master
on long_name = TianXieRenDW
group by long_name
我想把它们做成一条查询
其中第二个select 里的sum(JiFenFenZhi)/count(xingming) as RenJunJiFen,这里的count(xingming)实际上是取自第一条select里的count(xingming)。
非常感谢!
分数不多,包涵!
现在字段数不一样,建议用临时表分步进行
sum(case when JiFenXiangMu = '技术改进建议' then JiFenFenZhi else 0 end) as JiShuGaiJinJianYi,
sum(case when JiFenXiangMu = '技术课题攻关' then JiFenFenZhi else 0 end) as JiShuKeTiGongGuan,
sum(case when JiFenXiangMu = '管理改进建议' then JiFenFenZhi else 0 end) as GuanLiGaiJinJianYi,
sum(case when JiFenXiangMu = '技术标准' then JiFenFenZhi else 0 end) as JiShuBiaoZhun,
sum(case when JiFenXiangMu = '管理(工作)标准' then JiFenFenZhi else 0 end) as GuanLiGongZuoBiaoZhun,
sum(case when JiFenXiangMu = '技术方案' then JiFenFenZhi else 0 end) as JiShuFangAn,
sum(case when JiFenXiangMu = '标准作业指导书' then JiFenFenZhi else 0 end) as BiaoZhunZuoYeZhiDaoShu,
sum(case when JiFenXiangMu = '适用法律法规' then JiFenFenZhi else 0 end) as ShiYongFaLvFaGui,
sum(case when JiFenXiangMu = '技术论文' then JiFenFenZhi else 0 end) as JiShuLunWen,
sum(case when JiFenXiangMu = '管理论文' then JiFenFenZhi else 0 end) as GuanLiLunWen,
sum(JiFenFenZhi) as NianDuZongJiFen,
sum(JiFenFenZhi)/(select count(xingming) from yonghubiao where long_name = t.long_name and short_name = t.danwei where begda < '20090702') as RenJunJiFen
from
danweimingcheng t left join JiShuChuangXinJiFen_Master
on t.long_name = TianXieRenDW
group by t.long_name