MySQL 计算多列上的不同值

2021-12-19 00:00:00 sql group-by select distinct 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

相关文章