一个较难的数据库查询(后来在解决的过程中发现有简单解法)

问题如下

整个查询围绕一张表展开，下面是表的结构与数据：


id   type   count


20443 NULL 0
20445 1 6
20448 1 8
20448 2 1
20453 1 1
20454 1 20
20456 1 1
20458 1 1
20459 1 7
20459 2 2
20460 1 4
20468 1 3
20468 2 2
20469 1 2
20474 1 20
20476 1 10
20476 2 2
...


字段说明：
id ：公司 id
type : 1 表示新闻，2 表示研究报告
count ：一个公司相关的 新闻/研究报告 的数目
每一行记录表示：某一个公司的新闻或研究报行的数目是 count
注意：如果 type 是 NULL，则说明该公司没有新闻和研究报告。（因为要保证公司数据的存在，所以即使该公司没有新闻和研究报告也不能删除公司，所以会出现 type = NULL的情况）


现在查询需求为：
使用一条 sql 语句将每一个公司的新闻以及研究报告的数目连同公司 id 返回。
需要注意的是，上面的表是一个中间表，经由多表查询得到，所以在效率的限制下，不能重复从该表查询数据。
返回结果时，新闻的数目字段 as 为 news_count，研究报告数目字段 as 为 r_count ，返回的结果如下：


id news_count r_count
20443 0 0 
20445 6 0
20448 8 1
20453 1 0 
...


各行记录表示：
id为20443的公司新闻数目为0，研究报告数目也为0
id为20445的公司新闻数目为6，研究报告数目也为8
...

这个查询的逻辑比较简单，但实现起来有些复杂。

我给出的解法的思路是，最终在取出的结果里面，必然包含 news_count , r_count 两列，所以，在 select 里面必然要将值分别填充到这两个字段里面，那么，一个公司的 count 有一行或两行记录，怎么将它们归一为一行上的多列记录呢？因此，我想，可以根据 type 的不同，分别将值赋到 news_count 和 r_count 中，然后在外面套一个查询，将各类计数取出。因此，就有了下面的解法：

select 
id,
MAX(news_count) as news_count,
MAX(r_count) as r_count
from

(select id,
case when type = 1 then count else 0  end as news_count,
case when type = 2 then count else 0  end as r_count
from
abc
)tmp2


group by id

最外成的一层查询是因为：里层的一个查询对于一个公司查询出来的结果类似：

id news_count  r_count
20443  0  0 
20445  6  0

20448  0 1
20448  8  0
20453  1  0 

注意对于 id 为 20448 的公司，news_count 和 r_count 都有两个值，但我们只需要取较大的那个值即是所求。所以便有了外层的查询。

再给出一个我们项目经理的解法，

SELECT 
id,
SUM(if(type=1,counter,0)) as news_count,
sum(if(type=2,counter,0)) as r_count 

from abc GROUP BY id

其实这两种解法是一模一样的，实际上，if(1 = type,counter,0)【注：如果1 == type，则返回 counter，否则返回 0】的意思和 case type when 1 then counter 意思是一样的。

而 SUM 在此处其实效果与 MAX 是等同的，而上一种解法的 select MAX group by 与 直接 MAX 字段是一样的。所以第一种解法实际上绕了一点弯路，不过思路是正确的。

后来，经过上网查找，发现这是一类问题，即 行列互转的问题。

上面的问题是根据一行记录的某一个字段的值不同，代表不同的类别，将另一个字段拆分为相应的类，作为不同的列。如下图所示。

列转行问题则反过来转，sql 语句可以这样写：多次从表中取出一个字段，最后使用 union 将多个字段联合起来即可。