mysql - 在sql server中将行转换为列错误结果

Question

我有 table :

ID  platform
1   IOS
2   Android
3   Windows
4   IOS
5   IOS
6   IOS
7   IOS
8   Windows
9   Windows
10  Android

我想获取platform='Android'和platform='IOS'的出现次数。这是我做的:

select  top 2  platform, max(platformNumber) as count
from(
select ROW_NUMBER() over (partition by platform order by platform ) as platformNumber, B.* from B) temp 
group by platform

我得到的结果:

platform count
Android 2
IOS 5

现在我希望能够将列转换为显示:

IOS Android 
5     2

为此我尝试了这个脚本:

select platform,  'platform' + cast(ROW_NUMBER() over (partition by platform order by platform ) as varchar(10)) columnsqu
from
(
select  top 2  platform, max(platformNumber) as count
from(
select ROW_NUMBER() over (partition by platform order by platform ) as platformNumber, B.* from B) temp 
group by platform) Temp2
PIVOT
(
MAX(count) FOR columnsqu IN (IOS,Android)
)PIV

我收到错误...

Answer 1

我认为您只需要在这里进行数据透视查询:

SELECT
    COUNT(CASE WHEN platform = 'IOS' THEN 1 END) AS IOS,
    COUNT(CASE WHEN platform = 'Android' THEN 1 END) AS Android
FROM B
WHERE
    platform IN ('IOS', 'Android')
GROUP BY
    platform;