用于显示查询字段的sql

Question

我编写了一个sql来获取数据库中每个用户的票证信息。

select u.realname,count(t.id) as ct,t.Status
from Tickets t,Users u
where u.id=t.Owner and (t.status='New' or t.status='Stalled' or t.status='Open')
group by u.realname,t.status;

它提供这样的输出

| Enoch Root           |     1 | new     |
| Enoch Root           |     1 | stalled |
| xyz   Chan           |     1 | open    |
| Jim                  |    22 | stalled |

但是我要找的是用户对照状态。

喜欢

       open   stalled  new
 root    1       10      14

请让我知道

Answer 1

select u.realname, 
  sum( if( t.status = 'New', 1, 0 ) ) as new, 
  sum( if( t.status = 'Stalled', 1, 0 ) ) as stalled, 
  sum( if( t.status = 'Open', 1, 0 ) ) as open
from Tickets t, Users u
where 
  u.id = t.Owner and 
  t.status in ( 'New', 'Stalled', 'Open' )
group by u.realname

Answer 2

SELECT
  u.realname,
  SUM(t.status='Open') AS `Open`,
  SUM(t.status='Stalled') AS `Stalled`,
  SUM(t.status='New') AS `New`
FROM Tickets t INNER JOIN Users u
     ON u.id=t.Owner
WHERE (t.status='New' OR t.status='Stalled' OR t.status='Open')
GROUP BY
  u.id, u.realname