mysql查询:选择不同的column1，根据column2进行分组

Right now I have the following query:

现在我有以下查询:

SELECT name, COUNT(name), time, price, ip, SUM(price) 
  FROM tablename 
 WHERE time >= $yesterday 
   AND time <$today GROUP BY name

And what I'd like to do is add a DISTINCT by column 'ip', i.e.

我想做的是添加一个不同的列'ip'，也就是。

SELECT DISTINCT ip FROM tablename

So my final output would be all the columns, from all the rows that where time is today, grouped by name (with name count for each repeating name) and no duplicate ip addresses.

因此，我的最终输出将是所有列，来自今天时间所在的所有行，按名称分组(每个重复的名称都有名称计数)，没有重复的ip地址。

What should my query look like? (or alternatively, how can I add the missing filter to the output with php)?

我的查询应该是什么样子?(或者，如何使用php将缺失的过滤器添加到输出中)?

Thanks in advance.

提前谢谢。

[UPDATE]

(更新)

To minimize confusion, consider this (simplified) db table:

为了减少混乱，考虑这个(简化的)db表:

|   name   |   ip   |
---------------------
|  mark    |  123   |
|  mark    |  123   |
|  mark    |  456   |
|  dave    |  789   |
|  dave    |  087   |

The result I'm looking for would be an HTML table looking like this:

我想要的结果是一个这样的HTML表格:

|  name    |  name count   |
----------------------------
|  mark    |      2        |
|  dave    |      2        |

What I'm currently getting is:

我现在得到的是:

|  name    |  name count   |
----------------------------
|  mark    |      3        |
|  dave    |      2        |

(it counts mark 3 times, even though two times are with the same ip).

(即使有两次使用相同的ip，马克也会被计数3次)。

5 个解决方案

#1

you can use COUNT(DISTINCT ip), this will only count distinct values

您可以使用COUNT(不同的ip)，这将只计算不同的值

#2

Replacing FROM tablename with FROM (SELECT DISTINCT * FROM tablename) should give you the result you want (ignoring duplicated rows) for example:

用FROM(从tablename中选择DISTINCT *)替换tablename应该会得到您想要的结果(忽略重复的行)，例如:

SELECT name, COUNT(*)
FROM (SELECT DISTINCT * FROM Table1) AS T1
GROUP BY name

Result for your test data:

测试数据结果:

dave 2
mark 2

#3

You can just add the DISTINCT(ip), but it has to come at the start of the query. Be sure to escape PHP variables that go into the SQL string.

您可以只添加不同的(ip)，但它必须出现在查询的开头。一定要转义进入SQL字符串的PHP变量。

SELECT DISTINCT(ip), name, COUNT(name) nameCnt, 
time, price, SUM(price) priceSum
FROM tablename 
WHERE time >= $yesterday AND time <$today 
GROUP BY ip, name

#4

Somehow your requirement sounds a bit contradictory ..

不知何故，你的要求听起来有点矛盾。

group by name (which is basically a distinct on name plus readiness to aggregate) and then a distinct on IP

按名称分组(基本上是不同的名称加上聚合准备)，然后是不同的IP

What do you think should happen if two people (names) worked from the same IP within the time period specified?

如果两个人(姓名)在规定的时间内使用同一个IP，你认为会发生什么?

Did you try this?

你试试这个吗?

SELECT name, COUNT(name), time, price, ip, SUM(price) 
  FROM tablename 
 WHERE time >= $yesterday AND time <$today 
GROUP BY name,ip

#5

Try the following:

试试以下:

SELECT DISTINCT(ip), name, COUNT(name) nameCnt, 
time, price, SUM(price) priceSum
FROM tablename 
WHERE time >= $yesterday AND time <$today 
GROUP BY ip, name

#1