问题是这样的( 表一 如下 ):
id ip user time mac
1 1.1.1.1 a 2015-6-6 0000-1111-2222-3333
1 1.1.1.1 a 2015-6-6 9999-0000-4444-5555
1 2.2.2.2 a 2015-6-6 7777-1111-2222-66666
1 2.2.2.2 a 2015-6-6 8888-9999-2222-00000
1 7.7.7.7 b 2015-6-6 6666-5555-2222-6666
1 8.8.8.8 b 2015-6-6 33333-99999-2222-2222
表二 数据如下：
id ip time pv
1 1.1.1.1 2015-6-6 123
2 2.2.2.2 2015-6-6 456
通过第一表，第二表，ip去重复后,关联表查询SQL这样写的：
select T1.ip,count(*) as macs,T2.pv from mactj T1 JOIN pvip T2 ON T1.ip=T2.ip and T1.[macdate]=T2.[tjtime] where user='"&user&"' and macdate)='时间' group by T1.ip,T2.pv ORDER BY macs desc
但是现在想调出用户 a 下面所有IP相同日期,在表二的 PV 总和 该如何写呢，麻烦指点 谢谢
------解决思路----------------------
SELECT SUM(T2.pv)
FROM pvip T2
WHERE EXISTS (SELECT *
FROM mactj T1
WHERE T1.ip=T2.ip
AND T1.[macdate]=T2.[tjtime]
AND T1.[user] = 'a')
------解决思路----------------------
select T1.ip,T1.[macdate],count(*) as macs,SUM(T2.pv)pv
from mactj T1 JOIN pvip T2 ON T1.ip=T2.ip and T1.[macdate]=T2.[tjtime]
where user='"&user&"' and macdate)='时间'
group by T1.ip,T1.[macdate]
ORDER BY macs DESC
------解决思路----------------------
你多了一个右括号
select T1.ip,T1.[macdate],count(*) as macs,SUM(T2.pv)pv
from mactj T1 JOIN pvip T2 ON T1.ip=T2.ip and T1.[macdate]=T2.[tjtime]
where user='"&user&"' and macdate='时间'
group by T1.ip,T1.[macdate]
ORDER BY macs DESC
------解决思路----------------------
是不是要根据表1 a用户中IP地址重复的去表2中求pv和
------解决思路----------------------
WITH mactj(id, ip, [user], [time], mac)AS
(
select '1','1.1.1.1','a','2015-6-6','0000-1111-2222-3333' union all
select '1','1.1.1.1','a','2015-6-6','9999-0000-4444-5555' union all
select '1','2.2.2.2','a','2015-6-6','7777-1111-2222-66666' union all
select '1','2.2.2.2','a','2015-6-6','8888-9999-2222-00000' union all
select '1','7.7.7.7','b','2015-6-6','6666-5555-2222-6666' union all
select '1','8.8.8.8','b','2015-6-6','33333-99999-2222-2222'
),
pvip (id,ip,[time],pv) AS
(
select '1','1.1.1.1','2015-6-6','123' union all
select '2','2.2.2.2','2015-6-6','456'
)
--------------------------------------------------------------------------
select T1.ip, T1.[time], count(T1.ip) as macs, SUM(Convert(int,T2.pv)) as pv
from mactj T1 left JOIN pvip T2 ON T1.ip=T2.ip and T1.[time]=T2.[time]
where T1.[user]='a' and T1.[time]='2015-6-6'
group by T1.ip, T1.[time]
ORDER BY count(T1.ip) DESC
------解决思路----------------------
SELECT SUM(T2.pv)
FROM T2 T2
left join T1 T1 on T1.ip=T2.ip
AND T1.[macdate]=T2.[tjtime]
AND T1.[user] = 'a'