一天多少次

Question

关于…选择列，如果发生在同一日期…我收到了这个答案

SELECT a.*
FROM LicenseHistory a
JOIN (  SELECT LicenseID, date as date, COUNT(DISTINCT IPAddress,LicenseID)
    FROM LicenseHistory 
    WHERE (LicenseID= 24965)
    GROUP BY LicenseID,Date
    /*HAVING COUNT(DISTINCT IPAddress) > 1*/
     )b
ON a.LicenseID = b.LicenseID
AND a.date = b.date
order by date desc, LicenseID desc

返回这些结果…

Date            UniqueID      Users       IPAddress      Country      Count
2013-05-14         24965       15         70.60.96.98      US          1455
2013-05-14         24965       15         72.252.247.148   US           111
2013-03-29         24965       15         184.39.241.223   US            14

计数是严重错误的，因为一个简单的选择，如…

SELECT COUNT(Distinct IPAddress) AS DistinctIPCount
FROM LicenseHistory WHERE (LicenseID=24965)

显示计数为123。我需要这个SQL做的是计算那个ID在那天从那个IPaddress出现的次数。换句话说，" 24965在2013-05-14使用IP 70.609.6.98出现了多少次"

Answer 1

如果要统计给定ID，日期和IP的行数，则内部查询中存在错误，所有这些列必须在GROUP BY语句中。试试这个查询:

SELECT *
FROM LicenseHistory a
JOIN (  SELECT LicenseID, date as date, IPAddress, COUNT(*)
    FROM LicenseHistory 
    WHERE (LicenseID= 24965)
    GROUP BY LicenseID,Date,IPAddress
     )b
ON a.LicenseID = b.LicenseID
AND a.date = b.date
order by date desc, LicenseID desc