Estoy tratando de encontrar el bebedor y la cantidad total de dinero gastado en bebidas para todos los bebedores en febrero de 2020. También necesito incluir a los bebedores que no han pedido una bebida en este período.

Aquí están las tres tablas:

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)   );

Aquí está mi declaración hasta ahora:

SELECT ORDERS.DRINKER, SUM(SERVES.PRICE)
FROM ORDERS
LEFT JOIN SERVES ON ORDERS.DRINKER = SERVES.PRICE
GROUP BY ORDERS.DRINKER;

Soy muy nuevo en SQL, sé que hay una serie de errores. Cualquier ayuda sería muy apreciada!

2
Noods 15 may. 2020 a las 16:25

4 respuestas

La mejor respuesta

Podrías usar unir como

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;

Y si solo necesitas febrero

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;
1
scaisEdge 15 may. 2020 a las 14:24

Debe utilizar las funciones simples inner join y left join junto con las funciones month() y year(). Obtendrá resultados para todos los bebedores, independientemente de si pidieron una bebida o no. Entonces tendrá resultados para los no bebedores durante febrero.

select a.drinker,sum(c.price)
from drinkers a left join orders b on a.drinker = b.drinker
join serves c on b.pub = c.pub and b.drink = c.drink
where month(b.odate) = 'February' and year(b.odate) = 2020
group by a.drinker;
0
django-unchained 15 may. 2020 a las 13:55

Hay una variedad de patrones de consulta que devolverán un resultado que satisfaga la especificación. Aquí hay un ejemplo:

SELECT d.drinker               AS drinker
     , IFNULL(SUM(s.price),0)  AS tot_feb_prices
  FROM `DRINKERS` d
    -- 
  LEFT
  JOIN `ORDERS` o
    ON o.drinker  = p.drinker
   AND o.odate   >= '2020-02-01' 
   AND o.odate    < '2020-02-01' + INTERVAL 1 MONTH
    --
  LEFT
  JOIN `SERVES` s
    ON s.pub   = o.pub
   AND s.drink = o.drink
    --
 GROUP
    BY d.drinker
 ORDER
    BY d.drinker

Otra alternativa es utilizar una subconsulta correlacionada en la lista de selección (la subconsulta debe devolver un escalar ... solo una columna y, como máximo, una fila).

SELECT d.drinker
    --
     , ( SELECT IFNULL(SUM(s.price),0)
           FROM `ORDERS` o
           JOIN `SERVES` s
             ON s.pub   = o.pub
            AND s.drink = o.drink
          WHERE o.drinker  = p.drinker
            AND o.odate   >= '2020-02-01' 
            AND o.odate    < '2020-02-01' + INTERVAL 1 MONTH
       ) AS tot_feb_prices
    --
  FROM `DRINKERS` d
 ORDER BY d.drinker
0
spencer7593 15 may. 2020 a las 14:11

Ya que quiere todos DRINKERS, ya sea que lo hayan ordenado o no, querrá comenzar con esa tabla. Luego, LEFT JOIN a la tabla ORDERS, para obtener los pedidos de aquellos que tienen algunos.

Esto es una suposición, pero de la columna DRINK_NO parece que hay una fila en ORDERS por bebida servida. Como solo desea bebidas totales para esta consulta, le sugiero que use una subconsulta de esa tabla, en lugar de unirse a la tabla completa. Como esta tabla incluye los datos de ODATE, puede filtrar sus resultados aquí en la subconsulta. También puede filtrar con una cláusula WHERE en la consulta externa, pero será más eficiente hacerlo aquí.

SELECT
  DRINKER,
  PUB,
  DRINK,
  COUNT(DRINK_NO) AS QUANTITY 
FROM ORDERS
GROUP BY
  DRINKER,
  PUB,
  DRINK
WHERE
  MONTH(ODATE) = 2 AND YEAR(ODATE) = 2020

Finalmente, INNER JOIN su subconsulta a SERVES para obtener la información de precios.

La consulta completamente construida debería estar cerca de esto:

SELECT
  d.DRINKER,
  SUM(o.PRICE * o.QUANTITY) AS TotalSpent
FROM
  DRINKERS AS d
  LEFT JOIN
    (
      SELECT
        DRINKER,
        PUB,
        DRINK,
        COUNT(DRINK_NO) AS QUANTITY
      FROM ORDERS
      GROUP BY
        DRINKER,
        PUB,
        DRINK
      WHERE
        MONTH(ODATE) = 2 AND YEAR(ODATE) = 2020
    ) AS o
    ON o.DRINKER = d.DRINKER
  INNER JOIN SERVES AS s
    ON o.PUB = s.PUB
    AND o.DRINK = s.DRINK
GROUP BY
  d.DRINKER;
0
Eric Brandt 15 may. 2020 a las 16:36