sql=select dzm,nvl(hpzl,'Z'),zxdm,count(*) from b_zyxmls where zmlm='WXR'and zxdm in ('11','22') group by nvl(hpzl,'Z'),dzm,zxdm;
执行结果为:现在我想把第二条记录第五条记录合并为一条记录
即 DZM HPZL
读书铺 11 J 1 Z 4
执行结果为:现在我想把第二条记录第五条记录合并为一条记录
即 DZM HPZL
读书铺 11 J 1 Z 4
from (
select a.DZM ,a.hpzl,a.count
from (select dzm,nvl(hpzl,'Z'),zxdm,count(*) as count from b_zyxmls where zmlm='WXR'and zxdm in ('11','22') group by nvl(hpzl,'Z'),dzm,zxdm)a
where a.DZM ='读书铺' and a.hpzl='J'
) aa
inner join (
select b.DZM ,b.hpzl,b.count
from (select dzm,nvl(hpzl,'Z'),zxdm,count(*) as count from b_zyxmls where zmlm='WXR'and zxdm in ('11','22') group by nvl(hpzl,'Z'),dzm,zxdm)b
where b.DZM ='读书铺' and b.hpzl='Z'
)bb on aa.DZM =bb.DZM
你试试吧!
联合查询
LZ要求的太复杂也不实用
sql=select dzm,zxdm,
max(case ZJ when 'Z' then S else 0 end) Z,
max(case ZJ when 'J' then S else 0 end) J
from (select dzm,zxdm,nvl(hpzl,'Z')ZJ,count(*)S from b_zyxmls where zmlm='WXR'and zxdm in ('11','22') group by dzm,zxdm,nvl(hpzl,'Z'))
group by dzm,zxdm;