mysql - 什么是 SQL 来选择属性和相关属性的最大出现次数？

Question

我有一个这样的表:

Table: p
+----------------+
| id      | w_id |
+---------+------+
| 5       |  8   |
| 5       | 10   |
| 5       |  8   |
| 5       | 10   |
| 5       |  8   |
| 6       |  5   |
| 6       |  8   |
| 6       | 10   |
| 6       | 10   |
| 7       |  8   |
| 7       | 10   |
+----------------+

获得以下结果的最佳 SQL 是什么？ :

+-----------------------------+
| id      | most_used_w_id    |
+---------+-------------------+
|  5      |  8                |
|  6      | 10                |
|  7      |  8                |
+-----------------------------+

换句话说，根据 id 获取最频繁相关的 w_id。请注意，在上面的示例中，id 7 一次与 8 相关，一次与 10 相关。因此，结果是 (7, 8) 或 (7, 10)。如果不可能取一个，则结果集上的(7, 8) 和(7, 10) 都可以。

我想出了类似的东西:

select counters2.p_id as id, counters2.w_id as most_used_w_id
from (
  select p.id as p_id, 
         w_id,
         count(w_id) as count_of_w_ids
  from p
  group by id, w_id
) as counters2

join (
  select p_id, max(count_of_w_ids) as max_counter_for_w_ids
  from (
    select p.id as p_id, 
           w_id,
           count(w_id) as count_of_w_ids
    from p
    group by id, w_id
  ) as counters
  group by p_id
 ) as p_max 

on p_max.p_id = counters2.p_id
   and p_max.max_counter_for_w_ids = counters2.count_of_w_ids
;

但我完全不确定这是否是最好的方法。而且我不得不重复相同的子查询两次。

有更好的解决方案吗？

Answer 1

尝试使用User defined variables

select id,w_id
FROM
( select T.*,
         if(@id<>id,1,0) as row,
         @id:=id FROM
              (
               select id,W_id, Count(*) as cnt  FROM p Group by ID,W_id
              ) as T,(SELECT @id:=0) as T1
    ORDER BY id,cnt DESC
) as T2
WHERE Row=1

SQLFiddle demo