子查询与聚合函数的有关问题
子查询与聚合函数的问题
select
sum(case when VISITORIP in (select ip from IPUnit where left(UnitID,6)='101006') then 1 else 0 end) as vs6,
sum(case when VISITORIP in (select ip from IPUnit where left(UnitID,6)='101007') then 1 else 0 end) as vs7,
from VisitRecord
我想做访问统计,IP地址属于单位101006的统计一个合计数,属于101007的也统计一个合计数....
以上语句报错,提示子查询不能用聚合函数,该怎么写
------解决思路----------------------
------解决思路----------------------
可以试一下下面的sql
------解决思路----------------------
------解决思路----------------------
------解决思路----------------------
------解决思路----------------------
截取字符串,就稍微修改一下。
select
sum(case when VISITORIP in (select ip from IPUnit where left(UnitID,6)='101006') then 1 else 0 end) as vs6,
sum(case when VISITORIP in (select ip from IPUnit where left(UnitID,6)='101007') then 1 else 0 end) as vs7,
from VisitRecord
我想做访问统计,IP地址属于单位101006的统计一个合计数,属于101007的也统计一个合计数....
以上语句报错,提示子查询不能用聚合函数,该怎么写
------解决思路----------------------
--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
------解决思路----------------------
可以试一下下面的sql
select
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
------解决思路----------------------
SELECT SUM(vs6) vs6,SUM(vs7) vs7
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
------解决思路----------------------
select
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
------解决思路----------------------
select
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
------解决思路----------------------
截取字符串,就稍微修改一下。
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