--TRY
select
sum(case when exists(select 1 from IPUnit where left(UnitID,6)='101006' and ip =a.VISITORIP) then 1 else 0 end) as vs6,
sum(case when exists(select 1 from IPUnit where left(UnitID,6)='101007' and ip =a.VISITORIP) then 1 else 0 end) as vs7,
from VisitRecord as a
select
sum(case when exists(select 1 from IPUnit where left(UnitID,6)='101006' and ip =a.VISITORIP) then 1 else 0 end) as vs6,
sum(case when exists(select 1 from IPUnit where left(UnitID,6)='101007' and ip =a.VISITORIP) then 1 else 0 end) as vs7,
from VisitRecord as a
b.UnitID,
count(a.VISITORIP) as vsip
from VisitRecord as a left join IPUnit as b on a.VISITORIP=b.ip
where b.UnitID in ('101006','101007') group by b.UnitID
FROM (
select
case when exists(select 1 from IPUnit where left(UnitID,6)='101006' and ip =a.VISITORIP) then 1 else 0 end as vs6,
case when exists(select 1 from IPUnit where left(UnitID,6)='101007' and ip =a.VISITORIP) then 1 else 0 end as vs7
from VisitRecord as a) T
sum(case when left(b.UnitID,6)='101006' then 1 else 0 end) as vs6,
sum(case when left(b.UnitID,6)='101007' then 1 else 0 end) as vs7,
from
VisitRecord as a left join IPUnit as b on a.VISITORIP =b.ip
sum(case when left(b.UnitID,6)='101006' then 1 else 0 end) as vs6,
sum(case when left(b.UnitID,6)='101007' then 1 else 0 end) as vs7
from
VisitRecord as a left join IPUnit as b on a.VISITORIP =b.ip
这个方法执行速度快,但是不满足要求,因为需要对unitID截取字符串
这个方法执行速度快,但是不满足要求,因为需要对unitID截取字符串
截取字符串,就稍微修改一下。select
b.UnitID,
count(a.VISITORIP) as vsip
from VisitRecord as a left join IPUnit as b on a.VISITORIP=b.ip
where left(b.UnitID,6) in ('101006','101007') group by b.UnitID