android - 按多列请求分组，结果在一行上 - Android SQLite

Question

我需要做一些SELECT ... COUNT() ... GROUP BY 2列(月份和类型)请求 Android 上的 SQLite 数据库。我可以满足以下要求:

select Month, Type, Count()
from MyTable
group by Month, Type

我的问题是我想在 Android Listview 中使用光标显示结果:

• 每个月一行
• (固定且已知数量的)类型的 COUNT。

也许是这样的:

select Month, Count(Type = A), Count(Type = B), Count(Type = C)
from MyTable
group by Month

数据库

Month   |   Type
-------- ---------
Feb 15  |   A
Feb 15  |   B
Dec 15  |   A
Dec 15  |   A
Dec 15  |   B
Dec 15  |   C

预期结果

Month   |  'COUNT(A)'  |   'COUNT(B)'  |   'COUNT(C)'
-------- -------------- --------------- -------------
Feb 15  |   1          |   1          |   0
Dec 15  |   2          |   1          |   1

这可能吗？

Answer 1

select 
 month, 
 sum(case when "Type" = 'A' then 1 else 0 end) as "count(a)",
 sum(case when "Type" = 'B' then 1 else 0 end) as "count(b)",
 sum(case when "Type" = 'C' then 1 else 0 end) as "count(c)"
from 
 mytable 
group by 
 month