如何在SQL的嵌套子查询中找到不同的列? [英] How to find distinct columns in a nested subquery in SQL?

查看：91
本文介绍了如何在SQL的嵌套子查询中找到不同的列?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我需要找到订购"VODKA"和"WHISKY"的不同饮酒者.我只允许使用嵌套查询.不接受其他格式.

I need to find the distinct drinkers that ordered 'VODKA' and 'WHISKY'. I AM ONLY ALLOWED TO USE A NESTED QUERY. No other format is accepted.

我对sql还是很陌生，所以任何帮助将不胜感激！ 这是我要从中检索数据的表:

I am quite new to sql so any help would be greatly appreciated! Here are the tables I am trying to retrieve data from: 

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

CREATE TABLE ORDERS(    /* Drinkers visit pubs and consumes drinks */
DRINKER     VARCHAR(30) NOT NULL,   /* Drinker name */
PUB         VARCHAR(30) NOT NULL,   /* Pub name */
ODATE       DATE        NOT NULL,   /* Order date   */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
DRINK_NO    DECIMAL(2)  NOT NULL,   /* A sequence number of a drink */
    CONSTRAINT ORDERS_PKEY PRIMARY KEY(DRINKER, PUB, ODATE, DRINK, DRINK_NO),
    CONSTRAINT ORDERS_FKEY1 FOREIGN KEY(PUB, DRINK) REFERENCES SERVES(PUB, DRINK),
    CONSTRAINT ORDERS_FKEY2 FOREIGN KEY(DRINKER) REFERENCES DRINKERS(DRINKER)   );

到目前为止，这是我的SELECT语句:

Here is my SELECT statement so far: 

SELECT DISTINCT DRINKER
FROM DRINKERS
WHERE EXISTS (SELECT DRINKER 
              FROM ORDERS
              WHERE DRINKERS.DRINKER = ORDERS.DRINKER
              AND DRINK = 'VODKA' AND 'WHISKY');

插入声明:

INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JAN-2020', '%d-%M-%Y'), 'BEER', 2);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('9-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 2);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 3);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('11-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('13-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'RED WINE', 2);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('16-JAN-2020', '%d-%M-%Y'), 'WHISKY', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'GREASY FORK', STR_TO_DATE('17-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 2);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 3);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 4);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 2);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 3);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('01-FEB-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-FEB-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'GREASY FORK', STR_TO_DATE('03-FEB-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('05-FEB-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('06-FEB-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'GREASY FORK', STR_TO_DATE('15-FEB-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LITTLE PIRATE', STR_TO_DATE('16-FEB-2020', '%d-%M-%Y'), 'CHAMPAGNE', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LITTLE PIRATE', STR_TO_DATE('17-FEB-2020', '%d-%M-%Y'), 'CHAMPAGNE', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-FEB-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('20-FEB-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('22-FEB-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-MAR-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-MAR-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('05-MAR-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-APR-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-MAY-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('03-MAY-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-MAY-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-JUN-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-JUN-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JUN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('9-JUN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JUN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('11-JUN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('12-JUL-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('13-JUL-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-AUG-2020', '%d-%M-%Y'), 'BEER', 1);

INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 2);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 4);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 5);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('11-JAN-2020','%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('PETER', 'LAZY LOBSTER', STR_TO_DATE('09-FEB-2020', '%d-%M-%Y'), 'PORT', 1);
INSERT INTO ORDERS VALUES('PETER', 'GREASY FORK', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 2);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 3);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 4);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 5);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 6);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 7);

INSERT INTO ORDERS VALUES('MARY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('MARY', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('MARY', 'CAPTAIN MOORE', STR_TO_DATE('03-MAR- 2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('MARY', 'LONG JOHN', STR_TO_DATE('05-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('MARY', 'GREASY FORK', STR_TO_DATE('24-APR-2020', '%d-%M-%Y'), 'BEER', 1);

INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('13-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 2);
INSERT INTO ORDERS VALUES('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 3);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 2);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 3);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 4);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 5);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 6);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('JOHN', 'CAPTAIN MOORE', STR_TO_DATE('15-APR-2020', '%d-%M-%Y'), 'BEER', 1);

INSERT INTO ORDERS VALUES('JAMES', 'LONG JOHN', STR_TO_DATE('10-JAN-2020','%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 1);
INSERT INTO ORDERS VALUES('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 2);
INSERT INTO ORDERS VALUES('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 3);
INSERT INTO ORDERS VALUES('JAMES', 'LONG JOHN', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JAMES', 'LITTLE PIRATE', STR_TO_DATE('03- MAR-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JAMES', 'SWEET DREAMS', STR_TO_DATE('23-JUN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 2);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 3);
INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHISKY', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHISKY', 2);
INSERT INTO ORDERS VALUES('SERGIEY', 'GREASY FORK', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('06-FEB-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('23-FEB-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'WHISKY', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('09-MAR-2020', '%d-%M-%Y'), 'WHISKY', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LITTLE PIRATE', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'VODKA', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 2);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 3);

INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 2);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 4);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'RED WINE', 2);
INSERT INTO ORDERS VALUES('CLAUDE', 'GREASY FORK', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('05-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'GREASY FORK', STR_TO_DATE('20-APR-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-APR- 2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('16-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('17-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);

期望的输出:

DRINKER 

SERGIEY

推荐答案

您可以使用DRINKER的hanving计数不同的饮料组

You could use an hanving for count distinct drink group by DRINKER

SELECT DRINKERS.DRINKER
FROM DRINKERS
INNER JOIN ORDERS DRINKERS.DRINKER = ORDERS.DRINKER
WHERE ORDERS.DRINK IN ( 'VODKA' , 'WHISKY')
GROUP BY DRINKER
HAVING COUNT(DISTINCT ORDERS.DRINK ) = 2

或者如果您需要嵌套

    SELECT DISTINCT DRINKER
    FROM DRINKERS
    WHERE EXISTS (SELECT DRINKER 
                  FROM ORDERS
                  WHERE DRINK IN ( 'VODKA' AND 'WHISKY')
                  GROUP BY DRINKER
                  HAVING COUNT(DISTINCT ORDERS.DRINK ) = 2 
                  );

这篇关于如何在SQL的嵌套子查询中找到不同的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆