计算SQL中多个表的记录

I am trying to count different status of a document from a database table.

我试图从数据库表中计算文档的不同状态。

Assume table one has :

假设表一有：

select docid, year, type, statusid
from table1
where docid in (1, 2) and year = '2016'

This returns:

docid year type statusid
------------------------
1     2016  pdf   231
1     2016  pdf   231
1     2016  pdf   231
1     2016  pdf   232
1     2016  pdf   232
1     2016  pdf   235
1     2016  pdf   235

but I need to return only one record like

但我只需要返回一条记录

docid  year  type   granted  revoked deleted others
----------------------------------------------------
1      2016   pdf    3        2       2        0

whereas status are in a different table

而状态则在不同的表格中

Table2

表2

statusid     status       masterid
----------------------------------
231          granted      51
232          revoked      51
235          deleted      51
236          others       51

What I tried is:

我尝试的是：

select 
    docid, year, type,statusid 
    case 
       when statusid = 231 
          then count(statusid) 
       else 0 
    as granted,
    case 
       when statusid = 232 
          then count(statusid) 
       else 0 as revoked,
    case 
       when statusid = 235 
          then count(statusid) 
       else 0 as deleted,
    case 
       when statusid = 236 
          then count(statusid) 
       else 0 as others
from 
    table1
where 
    docid in (1, 2) and year = '2016'
group by 
    docid, year, type, statusid

But this is returning 3 rows whereas output should be only one row with all status count.

但这会返回3行，而输出应该只有一行，所有状态都计数。

2 个解决方案

#1

You are close. The case needs to be the argument to the aggregation function and you don't want statusid in the group by:

你很亲密该案例需要是聚合函数的参数，并且您不希望组中的statusid：

select docid, year, type,
       sum(case when statusid = 231 then 1 else 0 end) as granted,
       sum(case when statusid = 232 then 1 else 0 end) as revoked,
       sum(case when statusid = 235 then 1 else 0 end) as deleted,
       sum(case when statusid = 236 then 1 else 0 end) as others
from table1
where docid in (1,2) and year='2016'
group by docid, year, type

#2

You can use pivot for the same as below:

你可以使用pivot如下：

Select * from (
    Select docid, [year], [type], [status] from table1 d 
    Join table2 dt on d.statusid = dt.statusid
) a
Pivot (count([status]) for [status] in ([granted],[revoked],[deleted],[others])) p

Output as below:

输出如下：

+-------+------+------+---------+---------+---------+--------+
| docid | year | type | granted | revoked | deleted | others |
+-------+------+------+---------+---------+---------+--------+
|     1 | 2016 | pdf  |       3 |       2 |       2 |      0 |
+-------+------+------+---------+---------+---------+--------+

#1