SQL查询一个表格的数据

Question

SQL数据查询

OS	uid	bit
Android	123	1243
iOS	345	1234
iOS	123	1543
iOS	123	998

根据同一个uid查询，OS同时同时包括Android 和iOS的数据，并记录Android和iOS的次数

输出结果

OS	uid	bit
Android	123	1243
iOS	123	1543
iOS	123	998

Android_count = 1
ios_count =2

Answer 1

有用请采纳：

SELECT OS, uid, bit
FROM (
  SELECT OS, uid, bit,
         SUM(CASE WHEN OS = 'Android' THEN 1 ELSE 0 END) OVER (PARTITION BY uid) as Android_count,
         SUM(CASE WHEN OS = 'iOS' THEN 1 ELSE 0 END) OVER (PARTITION BY uid) as iOS_count
  FROM [table_name]
) t
WHERE (OS = 'Android' OR OS = 'iOS') AND Android_count > 0 AND iOS_count > 0
GROUP BY OS, uid, bit

[table_name] 表示表的名称。