当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Controlling what value appears in a column while doing a join

问题描述:

This one's kind of complicated, so hopefully I can make it clear.

I have two tables:

views:

+---------------------+-------------+------------------+

| time | remote_host | referer |

+---------------------+-------------+------------------+

| 0000-00-00 00:00:00 | 10.0.13.2 | http://foo.com/a |

| 0000-00-00 00:00:00 | 10.0.13.1 | http://foo.com/b |

| 0000-00-00 00:00:00 | 10.0.13.2 | http://moo.com |

| 0000-00-00 00:00:00 | 10.0.13.2 | http://hi.com |

| 0000-00-00 00:00:00 | 10.0.13.1 | http://foo.com/c |

+---------------------+-------------+------------------+

test_websites:

+----+----------------+------+

| id | url | name |

+----+----------------+------+

| 1 | http://foo.com | |

| 2 | http://moo.com | |

+----+----------------+------+

I have a query that very nearly does what I want:

SELECT COUNT(*) as count, remote_host, url FROM test_websites

JOIN views ON referer LIKE CONCAT(url, '%')

GROUP BY test_websites.url

ORDER BY count DESC LIMIT 10;

Results look like this:

+-------+-------------+----------------+

| count | remote_host | url |

+-------+-------------+----------------+

| 3 | 10.0.13.2 | http://foo.com |

| 1 | 10.0.13.2 | http://moo.com |

+-------+-------------+----------------+

To explain, I'm trying to get the top 10 viewed websites, however the website URLs are defined in test_websites. Since http://foo.com is an entry in test_websites, all entries that start with http://foo.com should be counted as "one website." Hence the join is based on a LIKE condition, and it's correctly counting 3 for http://foo.com in the results.

So, the problem is that I want remote_host to be that entry that appears the most for those rows in views that start with http://foo.com. In this case, there are two rows starting with http://foo.com in the views table that have 10.0.13.1 as the remote_host, and so the results should show that 10.0.13.1 the remote_host column, and not the remote_host that appears with the first entry that starts with http://foo.com, as it is doing now.

Thanks.

网友答案:

UPDATED

Please try the following corrected query:

SELECT 
    COUNT(*) as count, 
    (
        SELECT A.remote_host
        FROM views AS A
        WHERE A.referer LIKE CONCAT(test_websites.url, '%')
        GROUP BY A.remote_host
        ORDER BY COUNT(1) DESC
        LIMIT 1
    ) AS max_count_remote_host,
    test_websites.url 
FROM 
    test_websites  
    JOIN views ON views.referer LIKE CONCAT(test_websites.url, '%') 
GROUP BY 
    test_websites.url 
ORDER BY 
    count DESC LIMIT 10;

Here you could find a working SQL Fiddle example.

分享给朋友:
您可能感兴趣的文章:
随机阅读: