我如何找到那些每天登录游戏mroe超过1次的id

Question

假设我们有一个类似的表格

day     | username |  game    |  
------------+----------+----------+
2016-08-06 |    1     | game_A   | 
2016-08-06 |    1     | game_B   | 
2016-08-08 |    1     | game_A   | 
2016-08-10 |    1     | game_C   |
2016-08-18 |    2     | game_C   | 
2016-09-18 |    2     | game_A   | 
2016-09-18 |    2     | game_C   | 
2016-10-18 |    2     | game_B   | 
2016-09-20 |    3     | game_C   | 
2016-09-22 |    3     | game_B   | 

你可以把它想象成Steam，就像Steam里有很多游戏一样，对吧？。我们想跟踪用户是否在Steam中玩了1个以上的游戏。

结果表应为

day     | username |  game    |  
------------+----------+----------+
2016-08-06 |    1     | game_A   | 
2016-08-06 |    1     | game_B   | 
2016-09-18 |    2     | game_A   | 
2016-09-18 |    2     | game_C   | 

它输出此表的原因是，您可以清楚地看到用户名1在同一天玩了game_A和game_B。与用户2相同我写的代码

select day, username, v
from
(
select day, username, count(*) as v
from table
group by day, username
) as visit
where v > 1
order by day, login
The result:
day     | username |    v     |  
------------+----------+----------+
2016-08-06 |    1     |    2     |  
2016-09-18 |    2     |    2     | 

我可以选择日期和用户名，但我如何准确地将它们分开，以便它也打印游戏？

Answer 1

使用窗口函数：

select *
from (
select t.*, count(*) over(partition by day, username) cnt
from mytable t
) t
where cnt > 1

Answer 2

您可以使用exists:

select t.*
from t
where exists (select 1
from t t2
where t2.day = t.day and t2.username = t.username and
t2.game <> t.game
);

注意：这解释了"；多于一个游戏"；作为同一用户在同一天的CCD_ 2的至少两个不同值。