mysql - 如何使用 MySQL 查询从集合中获取共同值？

Question

我有一个包含 ID、AID 和年份字段的表。如何找到集合中最新的共同年份(共同的AID组成一个集合)

ID  AID Year
---------
1   1   2001
2   1   2002
3   1   2003
4   1   2004

5   2   2003
6   2   2004
7   2   2005

8   3   2004
9   3   2005
10  3   2006

最近的普通年份 2004

Answer 1

我相信这是最简单的方法。

查询

    SELECT 
      Year
    FROM 
     t
    GROUP BY 
     Year
    HAVING 
     COUNT(*) = (SELECT COUNT(DISTINCT t.AID) FROM t)

结果

    | Year |
    | ---- |
    | 2004 |

参见demo

此查询使其工作原理更加明显

查询

SELECT 
     Year
  ,  COUNT(*) AS count_per_AID
  , (SELECT COUNT(DISTINCT t.AID) FROM t) AS unique_count_per_AID
  , (COUNT(*) = (SELECT COUNT(DISTINCT t.AID) FROM t)) AS most_common_value
FROM 
 t
GROUP BY 
 Year

结果

| Year | count_per_AID  | unique_count_per_AID | most_common_value |
| ---- | -------------- | -------------------- | ----------------- |
| 2001 | 1              | 3                    | 0                 |
| 2002 | 1              | 3                    | 0                 |
| 2003 | 2              | 3                    | 0                 |
| 2004 | 3              | 3                    | 1                 |
| 2005 | 2              | 3                    | 0                 |
| 2006 | 1              | 3                    | 0                 |

This query returns all the common years and not the latest common year.

确实感谢您发表评论。
修复方法是使用查询

SELECT 
  Year
FROM 
 t
GROUP BY 
 Year
HAVING 
 COUNT(*) = (SELECT COUNT(DISTINCT t.AID) FROM t)
ORDER BY 
 t.Year DESC
LIMIT 1
;

参见demo