mysql - MYSQL从三个表中获取数据

Question

我正在尝试查找 2020 年 2 月所有饮酒者的饮酒者和在饮料上花费的总金额。我还需要包括在此期间未订购饮料的饮酒者。

以下是三张表：

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

CREATE TABLE SERVES(    /* Pubs serve drinks */
PUB         VARCHAR(30) NOT NULL,   /* Pub name */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
PRICE       DECIMAL(5,2)    NOT NULL,   /* Drink price  */
    CONSTRAINT SERVES_PKEY PRIMARY KEY(PUB, DRINK),
        CONSTRAINT SERVES_FKEY1 FOREIGN KEY(PUB) 
        REFERENCES LOCATED(PUB),
    CONSTRAINT SERVES_FKEY2 FOREIGN KEY(DRINK)
        REFERENCES ALLDRINKS(DRINK)  );

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 ORDERS.DRINKER, SUM(SERVES.PRICE)
FROM ORDERS
LEFT JOIN SERVES ON ORDERS.DRINKER = SERVES.PRICE
GROUP BY ORDERS.DRINKER;

我对 SQL 很陌生，我知道有很多错误。任何帮助将不胜感激！

标签： mysqlsqljoinleft-joinright-join

Answer 1

您可以使用连接作为

SELECT D.DRINKER , SUM(ifnull(S.PRICE,0))
FROM DRINKERS D
LEFT JOIN ORDERS O ON D.DRINKER  = O.DRINKER    
INNER JOIN  SERVES S ON O.PDB = S.PUB
    AND S.DRINK = O.DRINK
GROUP BY D.DRINKER;

如果你只需要二月

SELECT D.DRINKER , SUM(ifnull(S.PRICE,0))
FROM DRINKERS D
LEFT JOIN ORDERS O ON D.DRINKER  = O.DRINKER    
    AND year(O.ODATE) = 2020 AND month(O.ODATE) = 2
INNER JOIN  SERVES S ON O.PDB = S.PUB
    AND S.DRINK = O.DRINK

GROUP BY D.DRINKER;