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.

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)

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

解决方案

Just list multiple columns in the GROUP BY clause.

SELECT computer, user, count(*) AS count

FROM login

GROUP BY computer, user