你的语句我认为和下面语句等效:select PrpCmain.CompanyName,Sum(PrpCmain.SumPremium) as SumPremium,sum(PrpCmainorigin.SumPremium) as OriginSumPremium
from PrpCmain,PrpCmainorigin
where PrpCmain.PolicyNo=PrpCmainorigin.PolicyNo and PrpCmain.CompanyID is not null and PrpCmain.ComCode='42010500'
and (PrpCmain.StartDate between '2008-1-1' and '2008-12-31' or PrpCmain.EndDate between '2008-1-1' and '2008-12-31')
group by PrpCmain.CompanyName
from PrpCmain,PrpCmainorigin
where PrpCmain.PolicyNo=PrpCmainorigin.PolicyNo and PrpCmain.CompanyID is not null and PrpCmain.ComCode='42010500'
and (PrpCmain.StartDate between '2008-1-1' and '2008-12-31' or PrpCmain.EndDate between '2008-1-1' and '2008-12-31')
group by PrpCmain.CompanyName
select PrpCmain.CompanyName
from PrpCmain
where PrpCmain.CompanyID is not null
and PrpCmain.ComCode='42010500'
and
(PrpCmain.StartDate between '2008-1-1' and '2008-12-31' or PrpCmain.EndDate between '2008-1-1' and '2008-12-31') group bY
PrpCmain.CompanyName
和
select PrpCmain.CompanyName,Sum(PrpCmain.SumPremium) as SumPremium,sum(PrpCmainorigin.SumPremium) as OriginSumPremium
from PrpCmain,PrpCmainorigin
where
PrpCmain.PolicyNo=PrpCmainorigin.PolicyNo
and PrpCmain.CompanyID is not null
and PrpCmain.StartDate between '2008-1-1' and '2008-12-31'
and PrpCmain.ComCode='42010500'
group by PrpCmain.CompanyName ,
可不可以先把结果放到临时表,在左连接速度应该会快多!如:
select PrpCmain.CompanyName
into #tmp_1
from PrpCmain
where
PrpCmain.CompanyID is not null
and PrpCmain.ComCode='42010500'
and
(PrpCmain.StartDate between '2008-1-1' and '2008-12-31' or PrpCmain.EndDate between '2008-1-1' and '2008-12-31')
group bY
PrpCmain.CompanyName select PrpCmain.CompanyName,Sum(PrpCmain.SumPremium) as SumPremium,sum(PrpCmainorigin.SumPremium) as OriginSumPremium into #tmp_2
from PrpCmain,PrpCmainorigin
where
PrpCmain.PolicyNo=PrpCmainorigin.PolicyNo
and PrpCmain.CompanyID is not null
and PrpCmain.StartDate between '2008-1-1' and '2008-12-31'
and PrpCmain.ComCode='42010500'
group by PrpCmain.CompanyName
select h.CompanyName,k.SumPremium,k.OriginSumPremium
from #tmp_1 h left join #tmp_2 k
on k.CompanyName =h.CompanyName
SELECT *
FROM (
SELECT CompanyName
FROM PrpCmain
WHERE PrpCmain.CompanyID IS NOT NULL
AND PrpCmain.ComCode = '42010500'
AND PrpCmain.StartDate BETWEEN '2008-1-1' AND '2008-12-31'
UNION ALL
SELECT PrpCmain.CompanyName
FROM PrpCmain
WHERE PrpCmain.CompanyID IS NOT NULL
AND PrpCmain.ComCode = '42010500'
AND PrpCmain.EndDate BETWEEN '2008-1-1' AND '2008-12-31'
)
aa
LEFT JOIN (
SELECT PrpCmain.CompanyName,
SUM(PrpCmain.SumPremium) AS SumPremium,
SUM(PrpCmainorigin.SumPremium) AS OriginSumPremium
FROM PrpCmain,
PrpCmainorigin
WHERE PrpCmain.PolicyNo = PrpCmainorigin.PolicyNo
AND PrpCmain.ComCode = '42010500'
AND PrpCmain.CompanyID IS NOT NULL
AND PrpCmain.StartDate BETWEEN '2008-1-1' AND '2008-12-31'
GROUP BY
PrpCmain.CompanyName
) bb
ON aa.CompanyName = bb.CompanyName
现在又说这部分的数据也被算查来了?
那你先弄清楚条件吧
select PrpCmain.CompanyName,Sum(PrpCmain.SumPremium) as SumPremium,sum(PrpCmainorigin.SumPremium) as OriginSumPremium
from PrpCmain left join PrpCmainorigin on PrpCmain.PolicyNo=PrpCmainorigin.PolicyNo and PrpCmain.CompanyID is not null
and (PrpCmain.StartDate between '2008-1-1' and '2008-12-31' or PrpCmain.EndDate between '2008-1-1' and '2008-12-31')
and PrpCmain.ComCode='42010500'
group by PrpCmain.CompanyName all