用于返回满足多年条件的聚合结果的SQL语句

oyxsuwqo  于 2022-09-18  发布在  Java
关注(0)|答案(1)|浏览(155)

假设这是我的数据集:

CREATE TABLE orders (
  `email` VARCHAR(11),
  `id` INTEGER,
  `year` INTEGER
);

INSERT INTO orders
  (`email`, `id`, `year`)
VALUES
  ('foo@bar.com', '1001', '2019'),
  ('foo@bar.com', '1002', '2019'),
  ('foo@bar.com', '1003', '2019'),
  ('foo@bar.com', '1004', '2020'),
  ('foo@bar.com', '1005', '2020'),
  ('foo@bar.com', '1006', '2020'),
  ('foo@bar.com', '1007', '2021'),
  ('foo@bar.com', '1008', '2021'),
  ('foo@bar.com', '1009', '2021'),
  ('bar@foo.com', '1111', '2019'),
  ('bar@foo.com', '1112', '2019'),
  ('bar@foo.com', '1113', '2019'),
  ('bar@foo.com', '1114', '2020'),
  ('bar@foo.com', '1115', '2020'),
  ('bar@foo.com', '1116', '2021'),
  ('bar@foo.com', '1117', '2021'),
  ('bar@foo.com', '1118', '2021');

我想知道哪些电子邮件地址在2019年、2020年和2021年全年有3个订单(ID)。

我有这样的疑问:

SELECT email,year,count(*)
FROM (SELECT distinct email,id,year
    FROM orders) A
GROUP BY email,year
HAVING COUNT(*) = 3;

但我想要的只是返回foo@bar.com,因为这是唯一一封满足每年3个订单条件的电子邮件。

ggazkfy8

ggazkfy81#

你需要数2,首先得到所有有3个订单的电子邮件和年份,然后再数有3个订单的年份

MySQL 8

WITH CTE AS
(  SELECT  `email`, `year`
  FROM orders
  WHERE `year` IN(2019,2020,2021)
GROUP BY `email`, `year`
HAVINg COUNT(*) = 3)
SELECT `email` FROM CTE GROUP BY `email` HAVING Count(*) = 3

邮箱地址

Foo@bar.com

MySQL 5.x

SELECT `email` FROM
(  SELECT  `email`, `year`
  FROM orders
  WHERE `year` IN(2019,2020,2021)
GROUP BY `email`, `year`
HAVINg COUNT(*) = 3)  CTE GROUP BY `email` HAVING Count(*) = 3

邮箱地址

Foo@bar.com

fiddle

相关问题