MySQL在多列上计算不同的值 [英] MySQL Counting Distinct Values on Multiple Columns

问题描述

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

我只是从一个不在身边的顾问身上继承了这个IT环境，而且我正在写这个小小的查询，所以当我接到用户的电话时，我可以按照用户的姓名进行搜索，并根据登录到任何给定计算机的次数合理地预测他们使用的是哪台计算机。

以下是'login'表：

 电脑用户
 ncofp02 lee 
 ncofp02 lee 
 ncofp02 andy 
 ncodc01 andy 
 ncodc01 andy 
 ncodc01 lee 
  

我是什么敲我的头是逻辑来计算跨多列不同的值。我希望看到如下结果：

 计算机用户计数
 ncofp02 lee（2）$ b $ （1）
 ncodc01 lee（1）
 ncodc01 andy（2）
  

有没有办法在mysql中使用单个查询来完成这项工作，或者我应该开始循环一些php？ （booooo！）

解决方案

只需在 GROUP BY

  SELECT计算机，用户，计数（*）AS计数
 FROM登录
 GROUP BY计算机，用户
  

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

这篇关于MySQL在多列上计算不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！