MySQL 计算多列上的不同值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20809639/
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 Counting Distinct Values on Multiple Columns
提问by user3140879
I wrote a script that runs each time a user logs into a computer in our domain. This script makes a record of the user as well as the computer they logged into. Any number of users can log into any number of computers.
我编写了一个脚本，每次用户登录我们域中的计算机时都会运行该脚本。该脚本会记录用户以及他们登录的计算机。任意数量的用户可以登录任意数量的计算机。
I just inherited this IT environment from a consultant who is no longer around, and I'm writing this little query so when I get a call from a user, I can search by that user's name and reasonably predict which computer they are using by the number of times they've logged into any given computer.
我刚刚从一位已不在的顾问那里继承了这个 IT 环境，我正在编写这个小查询，因此当我接到用户的电话时，我可以通过该用户的名称进行搜索，并合理地预测他们正在使用哪台计算机他们登录任何给定计算机的次数。
Here's a sample of the data in the 'login' table:
以下是“登录”表中的数据示例：
COMPUTER USER
ncofp02 lee
ncofp02 lee
ncofp02 andy
ncodc01 andy
ncodc01 andy
ncodc01 lee
What I'm banging my head on is the logic to count distinct values across multiple columns. I'd like to see a result like this:
我正在思考的是在多列中计算不同值的逻辑。我希望看到这样的结果：
COMPUTER USER COUNT
ncofp02 lee (2)
ncofp02 andy (1)
ncodc01 lee (1)
ncodc01 andy (2)
Is there a way to accomplish this with a single query within mysql, or should I start looping some php? (booooo!)
有没有办法用 mysql 中的单个查询来完成这个，或者我应该开始循环一些 php？（嘘！）
回答by Barmar
Just list multiple columns in the GROUP BYclause.
只需在GROUP BY子句中列出多个列。
SELECT computer, user, count(*) AS count
FROM login
GROUP BY computer, user
回答by Saharsh Shah
Try this:
尝试这个：
SELECT l.computer, l.user, COUNT(DISTINCT l.computer, l.user) AS count
FROM login l
GROUP BY l.computer, l.user
回答by Snaver
Easy!
简单！
SELECT
computer, user, COUNT(DISTINCT computer, user) AS count
FROM
login
