SQL查询一个表格的数据

SQL数据查询

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

输出结果

Android_count = 1
ios_count =2

有用请采纳：

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] 表示表的名称。

该回答引用ChatGPT
请参考下面的解决方案，如果有帮助，还请点击 “采纳” 感谢支持！
可以使用以下SQL语句实现这个查询：

SELECT OS, uid, bit
FROM your_table
WHERE uid IN (SELECT uid FROM your_table WHERE OS = 'Android' OR OS = 'iOS')
GROUP BY uid, OS, bit;

SELECT
  (CASE WHEN OS = 'Android' THEN 'Android_count' ELSE 'ios_count' END) AS count_type,
  COUNT(*)
FROM your_table
WHERE uid IN (SELECT uid FROM your_table WHERE OS = 'Android' OR OS = 'iOS')
GROUP BY OS;

SELECT OS, uid, bit
FROM table_name
WHERE uid IN (SELECT uid FROM table_name WHERE OS = 'Android' OR OS = 'iOS')
GROUP BY uid, OS
HAVING COUNT(DISTINCT OS) = 2;

SELECT 
  SUM(CASE WHEN OS = 'Android' THEN 1 ELSE 0 END) AS Android_count,
  SUM(CASE WHEN OS = 'iOS' THEN 1 ELSE 0 END) AS ios_count
FROM table_name;

首先，第一个语句选择了所有包含Android和iOS的OS列，并将结果分组。 HAVING语句确保了只有每个uid都在Android和iOS中出现才会被选择。

第二个语句使用了SUM函数和CASE语句，计算了Android和iOS在表中的数量。

• 这篇文章讲的很详细，请看：sql查询慢的解决思路