mysql查询：SELECT DISTINCT column1，GROUP BY column2 [英] mysql query: SELECT DISTINCT column1, GROUP BY column2

问题描述

  SELECT name，COUNT（name），time，price，ip，SUM （价格）
 FROM表名
 WHERE时间> = $昨天
 AND时间<今天$ GROUP BY名称
  

我想要做的是按列'ip'添加一个DISTINCT，即

  SELECT DISTINCT ip FROM tablename 
  

所以我最终的输出将是所有列，从所有时间在今天的行，按名称分组（每个重复名称的名称数），并且没有重复的IP地址。

我的查询应该是什么样子？ （或者，我怎么能添加缺少的过滤器输出与PHP）？

预先感谢。

< hr>

[UPDATE]

为了减少混淆，请考虑以下（简化）db表： b

  |名称| ip | 
 --------------------- 
 | mark | 123 | 
 | mark | 123 | 
 | mark | 456 | 
 |戴夫| 789 | 
 |戴夫| 087 | 
  

我正在寻找的结果是一张HTML表格，如下所示：

  |名称|名字c​​ount | 
 ---------------------------- 
 | mark | 2 | 
 |戴夫| 2 | 
  

目前收到的是：

  |名称|名字c​​ount | 
 ---------------------------- 
 | mark | 3 | 
 |戴夫| 2 | 
  

（即使两次使用相同的ip，它也会计数3次）。

解决方案

您可以使用 COUNT（DISTINCT ip）

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.

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.

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

Thanks in advance.

---

[UPDATE]

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

|   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:

|  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).

解决方案

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

这篇关于mysql查询：SELECT DISTINCT column1，GROUP BY column2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！