select biz."SoilUseArea","BuildArea",ba."AcceptID",biz."DoorPlate",biz."RE_UnitNo",biz."RE_SoilSeaCode",br."OrCertNo",biz."LayoutTypeName",br."SoilUseTo",max(ba."AcceptID")
from gdrereg."BizAccept" ba
left join gdrereg."BizHouseRelate" biz on ba."AcceptID"=biz."AcceptID"
left join gdrereg."MrSoilCert" br on ba."AcceptID"=br."AcceptID"
where ba."AcceptID"='65432320170324000006'
group by biz."SoilUseArea","BuildArea",ba."AcceptID",biz."DoorPlate",biz."RE_UnitNo",biz."RE_SoilSeaCode",biz."LayoutTypeName",br."SoilUseTo",br."OrCertNo"gdrereg."BizAccept"中一条数据,
gdrereg."BizHouseRelate"中4条数据,
gdrereg."MrSoilCert"中4条数据,
如果不加br."OrCertNo"这个字段,结果就是4条记录,加这个字段就是16条记录。怎么才能实现添加这个字段,结果是四条记录呢[/img]
from gdrereg."BizAccept" ba
left join gdrereg."BizHouseRelate" biz on ba."AcceptID"=biz."AcceptID"
left join gdrereg."MrSoilCert" br on ba."AcceptID"=br."AcceptID"
where ba."AcceptID"='65432320170324000006'
group by biz."SoilUseArea","BuildArea",ba."AcceptID",biz."DoorPlate",biz."RE_UnitNo",biz."RE_SoilSeaCode",biz."LayoutTypeName",br."SoilUseTo",br."OrCertNo"gdrereg."BizAccept"中一条数据,
gdrereg."BizHouseRelate"中4条数据,
gdrereg."MrSoilCert"中4条数据,
如果不加br."OrCertNo"这个字段,结果就是4条记录,加这个字段就是16条记录。怎么才能实现添加这个字段,结果是四条记录呢[/img]
from (
select biz."SoilUseArea","BuildArea",ba."AcceptID",biz."DoorPlate",biz."RE_UnitNo",biz."RE_SoilSeaCode",biz."LayoutTypeName",ba."AcceptID"
from gdrereg."BizAccept" ba
left join gdrereg."BizHouseRelate" biz on ba."AcceptID"=biz."AcceptID"
where ba."AcceptID"='65432320170324000006'
) t
left join gdrereg."MrSoilCert" br on ba."AcceptID"=br."AcceptID"
--where ba."AcceptID"='65432320170324000006'
group by biz."SoilUseArea","BuildArea",ba."AcceptID",biz."DoorPlate",biz."RE_UnitNo",biz."RE_SoilSeaCode",biz."LayoutTypeName",br."SoilUseTo",br."OrCertNo"
大概就是想把前面2个表做左匹配关联后做成子查询,再关联第三个表
上面说是br."OrCertNo" 现在又说br."SoilUseTo" 懵懵的groupby分组的项目只要有不同的组合就会多出一条结果是当然的,你想怎样?
不加br."OrCertNo"分组时是4条
每一条其实都隐含了4个不一样的"OrCertNo",所以分组中加了."OrCertNo"之后结果就是16条了,
以希望依然只是4条,那么就需要明确针对不同的."OrCertNo"取哪一个,用聚合函数(max之类)去取,而不是加到分组里
那不就16条了,你想OrCertNo1,OrCertNo2,OrCertNo3,OrCertNo4.。。显示在一行上?要有1万个呢
问问题你得先说清楚啊,不知道你要干什么
两个查询结果件数可能不一致的话注意用外连接