sql - 需要在 SQL 查询中替换 union

Question

我要写一个sql查询

对于每种产品，确定它在 2010 年被购买了多少次(如果有的话)。结果应该是形式的元组(产品名称，次数)。如果一个产品2010 年根本没有购买，则第二个属性的值应为 0。不要使用 UNION 运算符。

我已经使用 UNION 编写了查询，但我必须在不使用 UNION 运算符的情况下编写它

SELECT p.product_name , COUNT(p.product_id)
  FROM product p JOIN buys b ON p.product_id = b.product_id
  WHERE b.year = 2010
  GROUP BY p.product_name
UNION
 SELECT p.product_name, 0
  FROM product p JOIN buys b ON p.product_id = b.product_id
  WHERE b.product_id NOT IN (SELECT pr.product_id FROM product pr JOIN buys bu ON pr.product_id = bu.product_id WHERE bu.year = 2010)
  GROUP BY p.product_name;

架构

CREATE TABLE product(
  product_id NUMERIC(10) NOT NULL,
  product_name VARCHAR2(30) NOT NULL,
  product_price NUMERIC(10) NOT NULL,
  product_manufacturer VARCHAR2(30) NOT NULL,
  CONSTRAINT pr_pk PRIMARY KEY(product_id)
  );

CREATE TABLE customer(
  customer_id NUMERIC(10) NOT NULL,
  customer_name VARCHAR2(30) NOT NULL,
  customer_age NUMERIC(10) NOT NULL,
  CONSTRAINT cu_pk PRIMARY KEY(customer_id)
  );

CREATE TABLE buys(
  customer_id NUMERIC(10) NOT NULL,
  product_id NUMERIC(10) NOT NULL,
  month NUMERIC(2) NOT NULL,
  day NUMERIC(2) NOT NULL,
  year NUMERIC(4) NOT NULL,
  CONSTRAINT bu_pk PRIMARY KEY(customer_id, product_id),
  CONSTRAINT bu_fk1 FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
  CONSTRAINT bu_fk2 FOREIGN KEY(product_id) REFERENCES product(product_id)
  );

请帮忙。

编辑我刚刚做了查询

SELECT p.product_name , COUNT(b.customer_id)
  FROM product p LEFT OUTER JOIN buys b ON p.product_id = b.product_id AND b.year = 2010
  GROUP BY p.product_name;

Answer 1

我刚刚做了查询

SELECT p.product_name , COUNT(b.customer_id)
  FROM product p LEFT OUTER JOIN buys b ON p.product_id = b.product_id AND b.year = 2010
  GROUP BY p.product_name;