如何获取与用户相关的类别明智值? [英] How to get the category wise values related to user?

问题描述

我正在使用PHP和MySQL开发日记管理脚本.我陷入一种假设表结构和我的要求之下的情况.

I am developing a Diary Management Script using PHP and MySQL. I am stuck with a situation for which I am presenting below a hypothetical table structure and my requirement.

 
|---------------|--------------|
|  User Name    |   Status     |
|---------------|--------------|
|  John         |   Open       |
|---------------|--------------|
|  John         |   Assigned   |
|---------------|--------------|
|  John         |   Closed     |
|---------------|--------------|
|  Miller       |   Assigned   |
|---------------|--------------|
|  Miller       |   Assigned   |
|---------------|--------------|
|  Smith        |   Closed     |
|---------------|--------------|

我希望最终的网页输出如下所示.

I am expecting an ultimate webpage output like the below.

|---------------|--------------|--------------|--------------|
|  User Name    |   Open       |  Assigned    | Closed       |
|---------------|--------------|--------------|--------------|
|  John         |      1       |       1      |     1        |
|---------------|--------------|--------------|--------------|
|  Miller       |      0       |       2      |     0        |
|---------------|--------------|--------------|--------------|
|  Smith        |      0       |       0      |     1        |
|---------------|--------------|--------------|--------------|

请建议如果使用MySQL查询(使用GROUP BY)本身可以产生这种现成的结果，或者我需要一点点编程逻辑的帮助.如果是这样，我应该怎么编码?我很无奈.我可以在此处提供整个脚本，但我并不是为了避免混淆.

Please suggest if using MySQL query(using GROUP BY) itself can produce this ready made or I have to get the help of little bit of Programming logic. If so what should I code? I am very much helpless. I could provide the entire script here, but I did not do just to avoid confusion.

推荐答案

select `User Name`,
    sum(case when Status = 'Open' then 1 end) as Open,
    sum(case when Status = 'Assigned' then 1 end) as Assigned,
    sum(case when Status = 'Closed' then 1 end) as Closed
from Status
group by `User Name`

这篇关于如何获取与用户相关的类别明智值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！