MySQL 计算多列上的不同值

本文介绍了MySQL 计算多列上的不同值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我编写了一个脚本，每次用户登录我们域中的计算机时都会运行该脚本.该脚本会记录用户以及他们登录的计算机.任意数量的用户可以登录任意数量的计算机.

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.

我刚刚从一位已不在的顾问那里继承了这个 IT 环境，我正在编写这个小查询，因此当我接到用户的电话时，我可以通过该用户的名称进行搜索并合理地预测他们是哪台计算机按他们登录任何给定计算机的次数使用.

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.

以下是登录"表中的数据示例:

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)

有没有办法用 mysql 中的单个查询来完成这个，或者我应该开始循环一些 php?(嘘！)

Is there a way to accomplish this with a single query within mysql, or should I start looping some php? (booooo!)

推荐答案

只需在 GROUP BY 子句中列出多个列.

Just list multiple columns in the GROUP BY clause.

SELECT computer, user, count(*) AS count FROM login GROUP BY computer, user