mysql - 加入 SQL 查询 - 无法借助 IP 编写获取国家/地区的查询-6ren

mysql - 加入 SQL 查询 - 无法借助 IP 编写获取国家/地区的查询

转载作者：行者123 更新时间：2023-11-30 01:32:54

我有一个已记录 IP 的访客列表。现在，对于特定报告，我需要查看每个访问者来自哪个国家/地区。

我将此任务分为两部分，首先获取唯一登录的用户的所有 IP，第二部分是搜索包含国家/IP 信息的两个表(来自 ip2nation)并获取提供的国家/地区知识产权。

第一部分 - 获取唯一登录用户的所有 IP

SELECT
     a.uid, a.hostname, a.timestamp, 
 COUNT(*) AS times
FROM
  login_activity a
GROUP BY
  a.hostname
ORDER BY
  times desc

这给了我所有过去登录用户的 IP(主机名)。工作正常。

第二部分 - 通过输入 IP 从两个表(都有数千条记录)中获取国家/地区

SELECT 
    c.country
FROM 
    ip2nationCountries c, ip2nation i
WHERE 
    i.ip < INET_ATON(  "157.191.122.36" ) 
AND 
    c.code = i.country

ORDER BY i.ip DESC 
LIMIT 0 , 1

这也很有效。

现在，解决真正的问题。连接这两个查询，从所有登录用户中获取国家/地区(而不是 IP)。这是我写的:-

        SELECT
         a.uid, a.hostname, a.timestamp, c.country, 
         COUNT(*) AS times
        FROM
          login_activity a, ip2nationCountries c, ip2nation i
        WHERE
           i.ip < INET_ATON(a.hostname)     
           AND c.code = i.country

        GROUP BY
          a.hostname
        ORDER BY
          times desc;

这有两个问题:-

加载需要很长时间。
它提供了错误的数据(每行显示数千次访问)。
基本上，它显示的所有数据都是错误的。

你能帮我编写这个 SQL 吗？

以防万一，表格的结构/数据如下:-

表的结构/数据是:-

ip2nation(有大量数据)

(结构)

CREATE TABLE ip2nation (
  ip int(11) unsigned NOT NULL default '0',
  country char(2) NOT NULL default '',
  KEY ip (ip)
);

(数据)

INSERT INTO ip2nation (ip, country) VALUES(0, 'us');
INSERT INTO ip2nation (ip, country) VALUES(687865856, 'za');
INSERT INTO ip2nation (ip, country) VALUES(689963008, 'eg');
INSERT INTO ip2nation (ip, country) VALUES(691011584, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691617792, 'zw');
INSERT INTO ip2nation (ip, country) VALUES(691621888, 'lr');
INSERT INTO ip2nation (ip, country) VALUES(691625984, 'ke');
INSERT INTO ip2nation (ip, country) VALUES(691630080, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691631104, 'gh');
INSERT INTO ip2nation (ip, country) VALUES(691632128, 'ng');
INSERT INTO ip2nation (ip, country) VALUES(691633152, 'zw');
INSERT INTO ip2nation (ip, country) VALUES(691634176, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691650560, 'gh');
INSERT INTO ip2nation (ip, country) VALUES(691666944, 'ng');
INSERT INTO ip2nation (ip, country) VALUES(691732480, 'tz');
INSERT INTO ip2nation (ip, country) VALUES(691798016, 'zm');
INSERT INTO ip2nation (ip, country) VALUES(691863552, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691994624, 'zm');
INSERT INTO ip2nation (ip, country) VALUES(692011008, 'za');
INSERT INTO ip2nation (ip, country) VALUES(692027392, 'mg');
INSERT INTO ip2nation (ip, country) VALUES(692035584, 'ao');
INSERT INTO ip2nation (ip, country) VALUES(692043776, 'na');
INSERT INTO ip2nation (ip, country) VALUES(692060160, 'eg');
INSERT INTO ip2nation (ip, country) VALUES(692191232, 'ci');
INSERT INTO ip2nation (ip, country) VALUES(692207616, 'za');
INSERT INTO ip2nation (ip, country) VALUES(692240384, 'gh');
INSERT INTO ip2nation (ip, country) VALUES(692256768, 'sd');

ip2nationCountries(有大量数据)

(结构)

CREATE TABLE ip2nationCountries (
  code varchar(4) NOT NULL default '',
  iso_code_2 varchar(2) NOT NULL default '',
  iso_code_3 varchar(3) default '',
  iso_country varchar(255) NOT NULL default '',
  country varchar(255) NOT NULL default '',
  lat float NOT NULL default '0',
  lon float NOT NULL default '0',  
  PRIMARY KEY  (code),
  KEY code (code)
);

(数据)

INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ad', 'AN', 'AND', 'Andorra', 'Andorra', 42.3, 1.3);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ae', 'AR', 'ARE', 'United Arab Emirates', 'United Arab Emirates', 24, 54);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('af', 'AF', 'AFG', 'Afghanistan', 'Afghanistan', 33, 65);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ag', 'AT', 'ATG', 'Antigua and Barbuda', 'Antigua and Barbuda', 17.03, -61.48);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ai', 'AI', 'AIA', 'Anguilla', 'Anguilla', 18.15, -63.1);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('al', 'AL', 'ALB', 'Albania', 'Albania', 41, 20);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('am', 'AR', 'ARM', 'Armenia', 'Armenia', 40, 45);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('an', 'AN', 'ANT', 'Netherlands Antilles', 'Netherlands Antilles', 12.15, -68.45);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ao', 'AG', 'AGO', 'Angola', 'Angola', -12.3, 18.3);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('aq', 'AT', 'ATA', 'Antarctica', 'Antarctica', -90, 0);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ar', 'AR', 'ARG', 'Argentina', 'Argentina', -34, -64);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('as', 'AS', 'ASM', 'American Samoa', 'American Samoa', -14.2, -170);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('at', 'AU', 'AUT', 'Austria', 'Austria', 47.2, 13.2);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('au', 'AU', 'AUS', 'Australia', 'Australia', -27, 133);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('aw', 'AB', 'ABW', 'Aruba', 'Aruba', 12.3, -69.58);

login_activity

(结构)

CREATE TABLE IF NOT EXISTS `mslop_login_activity` (
  `aid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier for an activity (session).',
  `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The mslop_users.uid corresponding to a session, or 0 for anonymous user.',
  `host_user_agent` varchar(256) NOT NULL DEFAULT '' COMMENT '$_SERVER["HOST_USER_AGENT"] string. This can be used with get_browser() in PHP.',
  `hostname` varchar(128) NOT NULL DEFAULT '' COMMENT 'The IP address that was used for this session.',
  `timestamp` int(11) NOT NULL DEFAULT '0' COMMENT 'The UNIX timestamp when the session was started.',
  PRIMARY KEY (`aid`),
  KEY `aid` (`aid`),
  KEY `uid` (`uid`),
  KEY `timestamp` (`timestamp`)
);

(数据)

INSERT INTO `mslop_login_activity` (`aid`, `uid`, `host_user_agent`, `hostname`, `timestamp`) VALUES
(1, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363038356),
(2, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363038374),
(3, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363193841),
(4, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363194789),
(5, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363197889),
(6, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '172.24.1.143', 1363207361),
(7, 35, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363301612),
(8, 35, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363301751),
(9, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363364574),
(10, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363374517),
(11, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363377701),
(12, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363714792),
(13, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363714911),
(14, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363714929),
(15, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363715946),
(16, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_3) AppleWebKit/536.28.10 (KHTML, like Gecko) Version/6.0.3 Safari/536.28.10', '172.24.1.161', 1363791080),
(17, 4, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_3) AppleWebKit/536.28.10 (KHTML, like Gecko) Version/6.0.3 Safari/536.28.10', '172.24.1.161', 1363791124),
(18, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_3) AppleWebKit/536.28.10 (KHTML, like Gecko) Version/6.0.3 Safari/536.28.10', '172.24.1.161', 1363791144),
(19, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.152 Safari/537.22', '172.24.1.143', 1363791365),
(20, 64, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.152 Safari/537.22', '172.24.1.143', 1363791650);

<小时/>

我按如下方式更改了查询，但它仍然显示错误的结果......

你们能看一下吗:-

SELECT l.uid,
    l.hostname,
    l.timestamp,
    c.country,
    l.times
FROM ip2nationCountries c
JOIN ip2nation i ON c.code = i.country
JOIN ( SELECT
        a.uid,
        a.hostname,
        MAX(a.timestamp) AS timestamp,
        COUNT(*) AS times
    FROM mslop_login_activity a
    WHERE a.uid = 3
    AND a.hostname = "157.191.122.36"
    GROUP BY a.hostname) AS l ON i.ip < INET_ATON( l.hostname )

最佳答案

您缺少加入。如果您使用正确的连接语法，这一点会更加明显，因此请将此作为一个教训，并在将来始终使用 join 和 on 子句。

您想要的查询:

    SELECT a.uid, a.hostname, a.timestamp, c.country, COUNT(*) AS times
    FROM login_activity a left outer join
         ip2nationCountries c
         on a.hostname = c.ip left outer join
         ip2nation i
         on i.ip < INET_ATON(a.hostname) AND c.code = i.country
    GROUP BY a.hostname
    ORDER BY times desc;

两条评论。我制作了这些左外连接。如果存在不匹配的主机名，那么它们仍然会出现在输出中(如果您想过滤掉这些主机名，请更改为inner join)。其次，如果 login_activity 表确实很大，您可能需要在加入其他表之前对其进行预聚合。

关于mysql - 加入 SQL 查询 - 无法借助 IP 编写获取国家/地区的查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/17243790/

文章推荐： PHP 多重查询问题

文章推荐： python - 插入时转换数据有问题吗？

文章推荐： c# - 空闲时间不活动检测

文章推荐： c++ - 将指针转换为 shared_ptr

Mysql 查询 JOIN 查询
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。我的查询需要显示值和选项的名称，而
查询
在mysql中，如何计算一行中的非空单元格？我只想计算某些列之间的单元格，比如第 3-10 列之间的单元格。不是所有的列...同样，仅在该行中。最佳答案如果你想这样做，只能在 sql 中使用名称而
sql - 查询、 native 查询、命名查询和类型化查询之间的区别
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
elasticsearch - 在Elasticsearch查询中没有为[查询]注册的[查询]
我正在为版本7.6进行Elasticsearch查询我的查询是这样的: { "query": { "bool": { "should": [ {
sql - 查询、 native 查询、命名查询和类型化查询之间的区别
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
php - Mysql WHERE NOT EXISTS(查询)OR(查询)
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确？ SELECT * FROM employees e WHERE NOT EXISTS (
javascript - 查询。为表中的每一行发送 ajax 查询
我找到了很多关于我的问题的答案，但问题没有解决我有表格，有数据，例如: Data 1 Data 2 Data 3
salesforce - SOQL 查询 - 如何通过将字段设为小写并进行比较来编写 SOQL 查询？
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
salesforce - SOQL 查询 - 如何通过将字段设为小写并进行比较来编写 SOQL 查询？
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
Android SQLite 查询(我想解析一般的 SQL 查询)
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
mysql 查询 - 为一个巨大的表优化现有的 MAX-MIN 查询
我有一个或多或少有效的查询(关于结果)，但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
SQL 查询 - 将 NULL 结果添加到 SELECT 查询
这是我第一次使用 Stack Overflow，所以我希望我以正确的方式提出这个问题。我有 2 个 SQL 查询，我正在尝试比较和识别缺失值，尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
sql - 什么是动态 SQL 查询？何时需要使用动态 SQL 查询？
什么是动态 SQL 查询？何时需要使用动态 SQL 查询？我使用的是 SQL Server 2005。最佳答案这里有几篇文章: Introduction to Dynamic SQL Dynami
php - 在另一个 mysql 查询 while 循环中调用 mysql 查询
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
java - MySQL 查询 "select top 5"查询
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
c# - Entity Framework 查询 ToString 不会产生 SQL 查询
通过我的研究，我发现至少从 EF 4.1 开始，EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上，这对我来说非常有用，使用 Entity Framework 5 和 6。但
MySQL 查询(或 Doctrine 1.2 查询)- 从连接表和过滤器中获取最新项目
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人，仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人表格结构如下: 一个联系人可
php - 如何在执行另一个 SQL 查询 x 分钟后执行一个 SQL 查询？
如何让我输入数据库的信息在输入数据 5 分钟后自行更新？假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
database - 如何在 N1QL 查询(Couchbase 查询)中使用 LENGTH() 字符串函数
我正在尝试搜索正好是 4 位数字的 ID，我知道我需要使用 LENGTH() 字符串函数，但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
php - 将 SQL 查询 (+JOIN) 转换为 Symfony Propel 查询
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城