from (select f.toCorpId from follow f where f.state = 1 and f.corpid = 1323008 group by f.toCorpId) f inner join Corporation c on f.toCorpId = c.id inner join Corpservice s on c.id = s.corporationid and s.servicename = 5 and s.state = 1 inner join corpcategory cg on c.categoryid_l0 = cg.id
left join
(select m.merchant, count(1) PersonCount from abc m group by m.merchant) m on c.customername = m.merchant
left join (select decode(p.corpid,1323008,p.tocorpid,p.corpid) corpid, p.state from couponbroadcast p where p.tocorpid = 1323008 or p.corpid = 1323008 and p.state != 3 and p.state != 4) st on c.id=st.corpid where c.id not in (select c.id from corporation c where c.fatherid = 1323008 and c.state = 1) and st.corpid is null
select c.id,c.corporationName,c.corporationNetName,nvl(m.PersonCount, 0) PersonCount, cg.name corpcategoryName,c.city || ' ' || c.section || ' ' || c.street area,p.state from (select f.toCorpId from follow f where f.state = 1 and f.corpid = 1323008 group by f.toCorpId) f inner join Corporation c on f.toCorpId = c.id inner join couponbroadcast p on f.toCorpId = p.tocorpid inner join Corpservice s on c.id = s.corporationid and s.servicename = 5 and s.state = 1 inner join corpcategory cg on c.categoryid_l0 = cg.id left join (select m.merchant, count(1) PersonCount from abc m group by m.merchant) m on c.customername = m.merchant where c.id not in (select c.id from corporation c where c.fatherid = 1323008 and c.state = 1) and c.id not in (select decode(p.corpid,1323008,p.tocorpid,p.corpid) corpid from couponbroadcast p where p.tocorpid = 1323008 or p.corpid = 1323008 and p.state != 3 and p.state != 4)
只在where中存在呢。
c.corporationName,
c.corporationNetName,
nvl(m.PersonCount, 0) PersonCount,
cg.name corpcategoryName,
c.city || ' ' || c.section || ' ' || c.street area,
(select state from couponbroadcast where ......) -- where条件要保证结果集最多1条记录
from (select f.toCorpId
c.corporationName,
c.corporationNetName,
nvl(m.PersonCount, 0) PersonCount,
cg.name corpcategoryName,
c.city || ' ' || c.section || ' ' || c.street area,
st.state
from (select f.toCorpId
from follow f
where f.state = 1
and f.corpid = 1323008
group by f.toCorpId) f
inner join Corporation c
on f.toCorpId = c.id
inner join Corpservice s
on c.id = s.corporationid
and s.servicename = 5
and s.state = 1
inner join corpcategory cg
on c.categoryid_l0 = cg.id
left join
(select m.merchant, count(1) PersonCount
from abc m
group by m.merchant) m
on c.customername = m.merchant
left join (select decode(p.corpid,1323008,p.tocorpid,p.corpid) corpid, p.state
from couponbroadcast p
where p.tocorpid = 1323008 or p.corpid = 1323008
and p.state != 3
and p.state != 4) st
on c.id=st.corpid
where c.id not in (select c.id
from corporation c
where c.fatherid = 1323008
and c.state = 1)
and st.corpid is null
cg.name corpcategoryName,c.city || ' ' || c.section || ' ' || c.street area,p.state
from (select f.toCorpId
from follow f
where f.state = 1
and f.corpid = 1323008
group by f.toCorpId) f
inner join Corporation c
on f.toCorpId = c.id
inner join couponbroadcast p
on f.toCorpId = p.tocorpid
inner join Corpservice s
on c.id = s.corporationid
and s.servicename = 5
and s.state = 1
inner join corpcategory cg
on c.categoryid_l0 = cg.id
left join
(select m.merchant, count(1) PersonCount
from abc m
group by m.merchant) m
on c.customername = m.merchant
where c.id not in (select c.id
from corporation c
where c.fatherid = 1323008
and c.state = 1)
and c.id not in (select decode(p.corpid,1323008,p.tocorpid,p.corpid) corpid
from couponbroadcast p
where p.tocorpid = 1323008 or p.corpid = 1323008
and p.state != 3
and p.state != 4)