时间戳

首页 > 解决方案 > SQL query match specific IP address

sql - SQL query match specific IP address

问题描述

I have a table with data like this: 

IP             ACCESS_DATE
----------------------------
192.168.1.5    06/22/2018
192.168.1.5    06/20/2018
192.168.1.5    06/18/2018
192.168.1.7    06/22/2018
192.168.1.7    05/01/2018
192.168.1.212  06/22/2018
10.10.10.1     06/22/2018

This is SQL Server, so no inet possible...

My IP column is defined as varchar(20).

I have a query that lists the most recent date associated to all IP addresses in that table, and I believe it works just fine. However, I can't seem to have it return the result for a specific IP.

This is the query that works: 

SELECT 
    t.IP, t.ACCESSS_DATE 
FROM
    (SELECT 
         IP, MAX(ACCESSS_DATE) AS ACCESSS_DATE
     FROM  
         USER_ACCESS_HISTORY
     GROUP BY
         IP) x 
JOIN 
    USER_ACCESS_HISTORY t ON x.IP = t.ip
                          AND x.ACCESSS_DATE = t.ACCESSS_DATE

but if I do this: 

SELECT 
    t.IP, t.ACCESSS_DATE 
FROM
    (SELECT 
         IP, MAX(ACCESSS_DATE) AS ACCESSS_DATE
     FROM
         USER_ACCESS_HISTORY
     GROUP BY IP) x 
JOIN 
    USER_ACCESS_HISTORY t ON x.IP = '192.168.1.5'
                          AND x.ACCESSS_DATE = t.ACCESSS_DATE

it returns 

192.168.1.5    06/22/2018
192.168.1.7    06/22/2018
192.168.1.212  06/22/2018

Any help would be appreciated!

标签： sqlsql-server

解决方案

改变这个：

ON x.IP =t.ip

对此：

ON x.IP ='192.168.1.5'

破坏了USER_ACCESS_HISTORY表和子查询之间的查询关系，因此不再有任何关联。你要这个：

SELECT t.IP, t.ACCESSS_DATE 
FROM( 
    SELECT IP,MAX(ACCESSS_DATE) AS ACCESSS_DATE
    FROM USER_ACCESS_HISTORY
     GROUP BY IP
) x 
JOIN USER_ACCESS_HISTORY t ON x.IP =t.ip
    AND x.ACCESSS_DATE = t.ACCESSS_DATE
WHERE x.IP ='192.168.1.5'

这会保留关系并将数据过滤到所需的 IP。

即使这看起来比您需要的要复杂得多（只需子查询就足够了），但我假设我们只看到查询的精简版本来说明问题。我确实提出来以防万一您可以像这样减少查询：

SELECT IP, MAX(ACCESSS_DATE) AS ACCESSS_DATE
FROM USER_ACCESS_HISTORY
WHERE IP = '192.168.1.5'
GROUP BY IP

推荐阅读