文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >在MYSQL中查找特定日期的行

问在MYSQL中查找特定日期的行
EN

Stack Overflow用户
提问于 2020-06-10 08:09:48
回答 1查看 38关注 0票数 0

我需要编写一个SQL语句来查找饮酒者、订购日期和同一天内每个饮酒者订购的饮料总数。然后，我需要按饮料总量的降序显示结果，然后按饮酒者的上升顺序显示结果。

我对这个问题要求我做什么感到有点困惑。我对SQL非常陌生，因此任何帮助都将不胜感激。

到目前为止，我的代码如下：

选择：

代码语言：javascript
复制
SELECT DRINKER, ODATE, COUNT(DRINK)
FROM ORDERS 
GROUP BY DRINKER, ODATE, DRINK
ORDER BY DRINKER ASC, DRINK DESC;

表：

代码语言：javascript
复制
CREATE TABLE ALLDRINKS(  /* All legal drinks */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
    CONSTRAINT DRINKNAME_PKEY PRIMARY KEY(DRINK) );

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

CREATE TABLE LOCATED(   /* Pubs have locations */
PUB         VARCHAR(30) NOT NULL,   /* Pub name */
STREET      VARCHAR(30) NOT NULL,   /* Street name  */
BLDG_NO     DECIMAL(4)  NOT NULL,   /* Building number  */
    CONSTRAINT LOCATED_PKEY PRIMARY KEY(PUB) );

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

插入声明：

代码语言：javascript
复制
INSERT INTO ALLDRINKS VALUES('BEER');
INSERT INTO ALLDRINKS VALUES('RED WINE');
INSERT INTO ALLDRINKS VALUES('WHITE WINE');
INSERT INTO ALLDRINKS VALUES('CHAMPAGNE');
INSERT INTO ALLDRINKS VALUES('VODKA');
INSERT INTO ALLDRINKS VALUES('PORT');
INSERT INTO ALLDRINKS VALUES('COGNAC');
INSERT INTO ALLDRINKS VALUES('RUM');
INSERT INTO ALLDRINKS VALUES('WHISKY');
INSERT INTO ALLDRINKS VALUES('YABTSOK');
INSERT INTO ALLDRINKS VALUES('SPIRIT ROCKET');

INSERT INTO DRINKERS VALUES ('JANUSZ');
INSERT INTO DRINKERS VALUES ('PETER');
INSERT INTO DRINKERS VALUES ('MARY');
INSERT INTO DRINKERS VALUES ('JOHN');
INSERT INTO DRINKERS VALUES ('JAMES');
INSERT INTO DRINKERS VALUES ('SERGIEY');
INSERT INTO DRINKERS VALUES ('CLAUDE');
INSERT INTO DRINKERS VALUES ('MIKE');
INSERT INTO DRINKERS VALUES ('TOM');

INSERT INTO LOCATED VALUES('LAZY LOBSTER', 'STATION ST.', 45);
INSERT INTO LOCATED VALUES('GREASY FORK', 'VICTORIA AVE.', 345);
INSERT INTO LOCATED VALUES('CAPTAIN MOORE', 'KING ST.', 45);
INSERT INTO LOCATED VALUES('LONG JOHN', 'STATION ST.', 89);
INSERT INTO LOCATED VALUES('LITTLE PIRATE', 'OXFORD ST.', 345);
INSERT INTO LOCATED VALUES('SWEET DREAMS', 'OXFORD ST.', 267);

INSERT INTO SERVES VALUES('LAZY LOBSTER', 'BEER', 5.00);
INSERT INTO SERVES VALUES('LAZY LOBSTER', 'RED WINE', 7.00);
INSERT INTO SERVES VALUES('LAZY LOBSTER', 'PORT', 8.00);
INSERT INTO SERVES VALUES('LAZY LOBSTER', 'COGNAC', 10.20);
INSERT INTO SERVES VALUES('LAZY LOBSTER', 'WHISKY', 6.90);
INSERT INTO SERVES VALUES('GREASY FORK', 'BEER', 5.20); 
INSERT INTO SERVES VALUES('GREASY FORK', 'RED WINE', 7.40);
INSERT INTO SERVES VALUES('LONG JOHN', 'BEER', 5.90);
INSERT INTO SERVES VALUES('LONG JOHN', 'RED WINE', 8.00);
INSERT INTO SERVES VALUES('LONG JOHN', 'WHITE WINE', 9.00);
INSERT INTO SERVES VALUES('LONG JOHN', 'VODKA', 6.00);
INSERT INTO SERVES VALUES('LONG JOHN', 'PORT', 9.35);
INSERT INTO SERVES VALUES('LONG JOHN', 'COGNAC', 12.90);
INSERT INTO SERVES VALUES('LONG JOHN', 'WHISKY', 7.15);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'BEER', 4.50);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'RED WINE', 7.90);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'WHITE WINE', 5.40);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'CHAMPAGNE', 10.90);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'VODKA', 5.25);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'RUM', 12.00);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'WHISKY', 10.80);
INSERT INTO SERVES VALUES('SWEET DREAMS', 'BEER', 3.00);
INSERT INTO SERVES VALUES('CAPTAIN MOORE', 'BEER', 4.50);
INSERT INTO SERVES VALUES('SWEET DREAMS', 'RUM', 4.50);
INSERT INTO SERVES VALUES('SWEET DREAMS', 'YABTSOK', 4.50);

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

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);
mysql
select
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-06-10 08:22:14

饮料不必出现在总计数中，而组中按和顺序排序的列顺序非常重要。在组中，您希望在日期内饮酒者，并按您希望饮酒者在饮料数量内的顺序(您可以在这里使用别名)。

代码语言：javascript
复制
SELECT DRINKER, ODATE, COUNT(DRINK) obs
FROM ORDERS 
GROUP BY ODATE,DRINKER 
ORDER BY obs desc,DRINKER ASC
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/62298750

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档