使用LIKE从表A字段数据统计在表B中出现的次数。。难住了

Question

表 IPStat
        date            ip              pv   url
2008-07-09 61.135.168.12 17 /Storefront/Default.aspx
2008-07-09 61.48.171.20 17 /Storefront/CEBooks.aspx
2008-07-09 61.48.171.20 14 /Storefront/Sell.aspx
2008-07-09 61.48.171.20 6 /Storefront/Buy.aspx
2008-07-09 61.48.171.20 5 /Storefront/Default.aspx
2008-07-09 61.48.171.20 5 /Storefront/Default.aspx
2008-07-09 66.249.72.175 1 /Jobs/ViewJobsDetail.aspx
2008-07-09 66.249.72.175 1 /Jobs/ViewJobsDetail.aspx
2008-07-09 66.249.72.175 1 /Jobs/ViewJobsDetail.aspx
2008-07-09 66.249.72.175 1 /Storefront/default.aspx
2008-07-09 66.249.72.175 1 /Storefront/Jobs.aspx
2008-07-09 61.135.168.12 1 /sellerdetail/c14/080410/10303375000.html
2008-07-09 202.160.180.7 1 /Storefront/Default.aspx
2008-07-09 202.160.178.247 1 /Storefront/Buy.aspx
2008-07-09 66.249.72.175 1 /sellerdetail/c7/080621/10190764062.html
2008-07-09 66.231.189.120 1 /Storefront/default.aspx
2008-07-09 66.249.72.175 1 /sellerdetail/c7/080621/10202670312.html
2008-07-09 66.249.72.175 1 /sellerdetail/c13/080625/16502921875.html
        略.....表 UserInfo
        
        user     fileurl
        12       c14/080410/10303375000.html
        12       c14/080420/103033732s0.html
        3        c4/080420/103033732s0.html
        4        c23/080420/103033732s0.html
        5        c18/080420/103033732s0.html
        4        c14/080420/103033732s0.html
        略.....目的是统计每个会员的网页文件被访问的独立IP数和PageView数，写出的伪SQL语句如下，用LIKE因为url和fileurl不完全相同，url含有fileurl的一部分。
SELECT COUNT(*) AS IP,SUM(pv) AS PV FROM IPStat WHERE url LIKE (SELECT fileurl FROM UserInfo)

Answer 1

不明白，up lz 再说明白一点

Answer 2

select B.user,A.IP,count(*)
from IPStat A
left join UserInfo B on  charindex(B.fileurl,A.url)>0
group by B.user,A.IP

Answer 3

select count(ip),sum(pv)
from IPStat
where url like (select fileurl from UserInfo)
我感觉这个应该可以的。。

Answer 4

select B.user,count(A.IP) IpNUM ,sum(pv) pvSum
from IPStat A 
left join UserInfo B on  charindex(B.fileurl,A.url)>0 
group by B.user,A.IP

Answer 5

通过
select B.user,count(A.IP) IpNUM ,sum(pv) pvSum
from IPStat A 
left join UserInfo B on  charindex(B.fileurl,A.url)>0 
group by B.user,A.IP
得到的结果列表
user IpNUM pvSum
12   1     2
12   2     2
3    1     2
4    1     5
6    2     4
3    2     1我想要每个user的总计。结果应该如下
user IpNUM pvSum
12   3     4
3    3     3
4    1     5
6    2     4