mysql - 控制连接时列中显示的值

Question

这个问题有点复杂，希望我能说清楚。

我有两个表:

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 |      |
+----+----------------+------+

我有一个查询几乎可以满足我的要求:

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;

结果如下所示:

+-------+-------------+----------------+
| count | remote_host | url            |
+-------+-------------+----------------+
|     3 | 10.0.13.2   | http://foo.com |
|     1 | 10.0.13.2   | http://moo.com |
+-------+-------------+----------------+

为了解释一下，我试图获取浏览量最高的 10 个网站，但网站 URL 是在 test_websites 中定义的。由于 http://foo.com 是 test_websites 中的一个条目，因此所有以 http://foo.com 开头的条目都应计为“一个网站”。因此，连接基于 LIKE 条件，并且它正确地将 http://foo.com 计为 3。在结果中。

所以，问题是我希望remote_host成为以http://foo.com开头的 View 中那些行中出现次数最多的条目。 。在本例中，有两行以 http://foo.com 开头。在 View 表中，remote_host 为 10.0.13.1，因此结果应显示 10.0.13.1 为 remote_host 列，而不是出现在以 http://foo.com 开头的第一个条目中的 Remote_host。 ，正如现在所做的那样。

谢谢。

Answer 1

已更新

请尝试以下更正后的查询:

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;

在这里您可以找到 working SQL Fiddle example 。