选择列，如果发生在同一天 [英] select columns if happened on same date

问题描述

我正在尝试制作一个可以选择多列（唯一ID，IP地址，日期，国家/地区）的查询，但是我希望查询只能选择某一天在同一个IP地址和唯一ID显示的那些记录。我一直在尝试...

I am trying to craft a query that will select multiple columns (uniqueID, IPaddress, date, country) but I would like the query only to select those records where the same IPaddress and UniqueID showed up on a given day. I have been trying ...

SELECT uniqueID AS uniqueID, 
IPAddress AS IPAddr,
Date AS DDate, 
Location AS Countries,
COUNT(IPAddress) AS IPCount
FROM History WHERE (uniqueID= 20677) 
GROUP BY Date
ORDER BY DDate DESC, IPAddr DESC 

但它确实消除了一天只显示一个Ip / UniqueID的记录。我真的只想在不同IP地址的同一天找到 uniqueID显示的记录。

But it does eliminate records where only one Ip/UniqueID showed up on one day. I really only want to find records where the uniqueID showed up on the same day with different IPaddresses.

推荐答案

p>如果您要查找其中的重复项，您需要按三个字段进行分组：

You need to group by three fields, if you are looking for duplicates amongst them:

SELECT uniqueID, IPAddress, "Date" AS DDate, Location AS Countries,
       COUNT(IPAddress) AS IPCount
FROM History
WHERE uniqueID = 20677
GROUP BY uniqueId, IPAddress, "Date"
having IPCount > 1;

您不需要 uniqueid 在 group by 子句中，因为您只选择一个。但是，我认为这样会使查询的意图更加清晰。

You dn't really need the uniqueid in the group by clause because you are selecting only one. However, I think it makes the intention of the query clearer.

如同写的，这将会产生错误（因为 location 不在聚合子句中）或返回一个随机值位置，如果您使用MySQL）。如果是后者，请尝试：

As written, this will either generate an error (because location is not in the aggregation clause) or return a random value for location, if you are using MySQL). If the latter, try:

SELECT uniqueID, IPAddress, "Date" AS DDate,
       group_concat(Location) AS Countries,
       COUNT(IPAddress) AS IPCount
FROM History
WHERE uniqueID = 20677
GROUP BY uniqueId, IPAddress, "Date"
having IPCount > 1;

另外，数据类型和函数后面的命名列不是一个好主意。在许多数据库中， date 既是数据类型又是函数。一些其他名称，如 DateAdded 避免了这种冲突。

Also, naming columns after data types and functions is not good idea. In many databases, date is both a data type and a function. Some other name, like DateAdded avoids this conflict.

这篇关于选择列，如果发生在同一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！