我编写了一个脚本，每次用户登录到我们域中的计算机时都会运行。该脚本记录了用户以及他们登录的计算机的记录。任何数量的用户都可以登录到任意数量的计算机。
我只是从一个不在身边的顾问身上继承了这个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 leeWhat 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在多列上计算不同的值
发布评论