挑选在特定年份有销售经验的推销员

Question

我想找一位在1998、1999和2001年都有销售经验的推销员。

例如：

**year  salesmen**
  1998        a
  1998        a
  1998        b
  1999        a
  1999        b
  1999        c
  2001        a
  2001        b
  2001        c

结果应该是"a“和"b”，因为只有那些销售员在所有年份都有销售。

我不认为它需要复杂的查询，但我无法想出解决方案

Answer 1

聚合是一种选择：

SELECT salesmen
FROM yourTable
WHERE year IN (1998, 1999, 2001)
GROUP BY salesmen
HAVING COUNT(DISTINCT year) = 3;

Answer 2

该查询显示，在所有3年(199819992001年)中至少有一次销售人员，只有1人在表上阅读：

select salesmen
from your_table
group by salesmen having count(case when year = 1998 then 1 end)>0 and
                         count(case when year = 1999 then 1 end)>0 and
                         count(case when year = 2001 then 1 end)>0

DB<>FIDDLE LINK

Answer 3

有许多选项可以得到预期的结果。一种是使用HAVING和COUNT或GROUP BY (已经有答案表明了这一点)。我想显示的选项是，不要查询重新编码的3年，而是这样做：

SELECT salesmen
FROM yourTable
WHERE year IN (SELECT year FROM yourtable)
GROUP BY salesmen
HAVING COUNT(DISTINCT year) = (SELECT COUNT(DISTINCT year) FROM yourtable);

如果这是有意的，这将是选择那些每年出现在你的表格中的销售人员的方式。

回到你的问题，另一种可能是使用IN

SELECT DISTINCT salesmen FROM yourtable 
WHERE salesmen IN
(SELECT salesmen FROM yourtable WHERE year = 1998) AND salesmen IN
(SELECT salesmen FROM yourtable WHERE year = 1999) AND salesmen IN
(SELECT salesmen FROM yourtable WHERE year = 2001);

您也可以使用EXISTS

SELECT DISTINCT salesmen FROM yourtable y 
WHERE 
EXISTS (SELECT 1 FROM yourtable WHERE year = 1998 AND salesmen = y.salesmen) AND 
EXISTS (SELECT 1 FROM yourtable WHERE year = 1999 AND salesmen = y.salesmen) AND
EXISTS (SELECT 1 FROM yourtable WHERE year = 2001 AND salesmen = y.salesmen);

您可以在这里看到不同之处，并尝试一下：db<>fiddle