查找偏好游戏设置为NES的每个国家的会员数量

Question

SQL偏好游戏设置为NES 的每个国家的成员数

预期输出

COUNT(AUD)  COUNT(EUR)  COUNT(IND)  COUNT(LAO)  COUNT(USA)  COUNT(ZWE)  
3           2           0           0       2           1

这是SQL fiddle

http://sqlfiddle.com/#！9/a15ec9f/3

Answer 1

Wepivot。

select *
from   t
pivot(count(MEMBER_ID) for COUNTRY_ID in('AU' as "count(AU)", 'US' as "count(US)", 'LAO' as "count(LAO)", 'IND' as "count(IND)", 'DE' as "count(DE)", 'ZW' as "count(ZW)")) p  

<2>

	计数
3	7	0	0		4

SELECT Member.MemberID, COUNTRY, GAME_TYPE 
FROM MEMBER_TABLE 
INNER JOIN COUNTRY_TABLE ON Member.COUNTRY_ID= COUNTRY_TABLE.COUNTRY_ID 
WHERE Member.GAME_TYPE= 'NES';