如何在SQL中使用集合代数操作

Question

我需要找到喜欢啤酒或红酒的独特的饮酒者。但是，查询必须使用集合代数操作来实现。

以下是我到目前为止所拥有的：

SELECT DRINKER 
FROM LIKES 
WHERE DRINK = 'BEER' OR 'RED WINE'; 

不过，这不是使用集合代数操作。我对SQL非常陌生，因此对此的任何帮助都将不胜感激。

下面是我从以下表格中获得的数据：

CREATE TABLE LIKES( /* Drinkers like drinks */
DRINKER     VARCHAR(30) NOT NULL,   /* Drinker name */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
RATING      DECIMAL(1)  NOT NULL,   /* Rating of the drink  */
    CONSTRAINT LIKES_PKEY PRIMARY KEY(DRINKER, DRINK),
    CONSTRAINT LIKES_FKEY1 FOREIGN KEY(DRINK) REFERENCES ALLDRINKS(DRINK),
    CONSTRAINT LIKES_DKEY2 FOREIGN KEY(DRINKER) REFERENCES DRINKERS(DRINKER)); 

以及餐桌上的“饮酒者”：

CREATE TABLE DRINKERS ( /* All drinkers */
DRINKER VARCHAR(30) NOT NULL,
    CONSTRAINT DRINKERS_PKEY PRIMARY KEY (DRINKER));

插入声明：

INSERT INTO LIKES VALUES('TOM', 'BEER', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'VODKA', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'RUM', 5);
INSERT INTO LIKES VALUES('JANUSZ', 'BEER', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'CHAMPAGNE', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'RED WINE', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'WHITE WINE', 5);
INSERT INTO LIKES VALUES('JANUSZ', 'PORT', 5);
INSERT INTO LIKES VALUES('PETER', 'CHAMPAGNE', 4);
INSERT INTO LIKES VALUES('PETER', 'COGNAC', 3);
INSERT INTO LIKES VALUES('PETER', 'RUM', 3);
INSERT INTO LIKES VALUES('PETER', 'WHISKY', 6);
INSERT INTO LIKES VALUES('MARY', 'CHAMPAGNE', 5);
INSERT INTO LIKES VALUES('MARY', 'VODKA', 1);
INSERT INTO LIKES VALUES('MARY', 'COGNAC', 4);
INSERT INTO LIKES VALUES('JOHN', 'CHAMPAGNE', 4);
INSERT INTO LIKES VALUES('JOHN', 'VODKA', 2);
INSERT INTO LIKES VALUES('JOHN', 'RUM', 6);
INSERT INTO LIKES VALUES('JOHN', 'WHISKY', 1);
INSERT INTO LIKES VALUES('JOHN', 'BEER', 6);
INSERT INTO LIKES VALUES('JAMES', 'CHAMPAGNE', 6);
INSERT INTO LIKES VALUES('JAMES', 'COGNAC', 5);
INSERT INTO LIKES VALUES('JAMES', 'RUM', 4);
INSERT INTO LIKES VALUES('SERGIEY', 'VODKA', 6);
INSERT INTO LIKES VALUES('SERGIEY', 'RUM', 6);
INSERT INTO LIKES VALUES('SERGIEY', 'CHAMPAGNE', 3);
INSERT INTO LIKES VALUES('CLAUDE', 'CHAMPAGNE', 6);
INSERT INTO LIKES VALUES('CLAUDE', 'WHITE WINE', 5);
INSERT INTO LIKES VALUES('CLAUDE', 'COGNAC', 4);
INSERT INTO LIKES VALUES('CLAUDE', 'WHISKY', 3);
INSERT INTO LIKES VALUES('CLAUDE', 'RED WINE', 6);

期望产出：

DRINKER

JANUSZ
JOHN
TOM

Answer 1

UNION是MySQL以UNION和UNION ALL两种形式支持的集代数运算。前者只从集合返回不同的值，后者则返回所有值(因此，对于示例数据，将返回两次JANUSZ，因为它们喜欢BEER和RED WINE)。因此，查找喜欢BEER或RED WINE的饮酒者的一组代数(如查询)应该是：

SELECT DRINKER
FROM LIKES
WHERE DRINK = 'BEER'
UNION
SELECT DRINKER 
FROM LIKES 
WHERE DRINK = 'RED WINE'

样本数据的输出：

DRINKER
TOM
JANUSZ
JOHN
CLAUDE

(注意克劳德也喜欢红酒)

dbfiddle演示

您可以向ORDER BY添加一个UNION子句，以便对最终结果进行排序。如果没有结果，结果的顺序就无法得到保证。例如，按字母顺序排序：

SELECT DRINKER
FROM LIKES
WHERE DRINK = 'BEER'
UNION
SELECT DRINKER 
FROM LIKES 
WHERE DRINK = 'RED WINE'
ORDER BY DRINKER

输出：

DRINKER
CLAUDE
JANUSZ
JOHN
TOM

dbfiddle演示

Answer 2

在标准SQL中，Set代数操作分别是UNION、INTERSECT和Set。为了你的需要，我认为联合会是最好的选择。

由于您没有指定某个人是否喜欢喝一杯饮料的评分，所以对于这个查询，我们假设<=5表示他们不喜欢它，而>5表示他们喜欢它。

SELECT DISTINCT DRINKER
FROM (SELECT DISTINCT DRINKER
      FROM LIKES
      WHERE DRINK="BEER"
      AND RATING>5
      UNION
      SELECT DISTINCT DRINKER
      FROM LIKES
      WHERE DRINK="RED WINE"
      AND RATING>5);

使用"Likes“是一个评级>5的假设，您将从所选的INSERT状态获得此输出：

CLAUDE
JANUSZ
JOHN
TOM

您可以在这里阅读有关Set操作的更多信息：

(SQL)