mysql 查询：SELECT DISTINCT column1, GROUP BY column2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2483492/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql query: SELECT DISTINCT column1, GROUP BY column2
提问by Adam Tal
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'添加一个DISTINCT，即
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:
为了尽量减少混淆，请考虑这个（简化的）数据库表：
| 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次）。
回答by knittl
you can use COUNT(DISTINCT ip), this will only count distinct values
您可以使用COUNT(DISTINCT ip)，这只会计算不同的值
回答by Mark Byers
Replacing FROM tablenamewith FROM (SELECT DISTINCT * FROM tablename)should give you the result you want (ignoring duplicated rows) for example:
替换FROM tablename为FROM (SELECT DISTINCT * FROM tablename)应该给你你想要的结果（忽略重复的行），例如：
SELECT name, COUNT(*)
FROM (SELECT DISTINCT * FROM Table1) AS T1
GROUP BY name
Result for your test data:
测试数据的结果：
dave 2
mark 2
回答by Andy
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.
您可以只添加DISTINCT(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
回答by lexu
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
回答by user2463590
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
