文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >如何在MYSQL中找到点酒量最多的饮酒者？

问如何在MYSQL中找到点酒量最多的饮酒者？
EN

Stack Overflow用户
提问于 2020-06-10 21:19:00
回答 1查看 112关注 0票数 1

到目前为止，我已经能够找到每个饮酒者一天内的饮酒量。

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

然后，我需要在一个示例数据库中插入一个饮酒者的两个新订单的信息，该饮酒者在一天中订购了最多的饮料。这就是我不确定到底该做什么的地方。这是我到目前为止的尝试：

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

我是MYSQL的新手，所以任何帮助都将不胜感激！

下面是CREATE和INSERT语句：

代码语言：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)   );


INSERT INTO ALLDRINKS VALUES
('BEER'),
('RED WINE'),
('WHITE WINE'),
('CHAMPAGNE'),
('VODKA'),
('PORT'),
('COGNAC'),
('RUM'),
('WHISKY'),
('YABTSOK'),
('SPIRIT ROCKET');

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

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

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

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

INSERT INTO ORDERS VALUES
('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JAN-2020', '%d-%M-%Y'), 'BEER', 2),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('9-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 2),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 3),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('11-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('13-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'RED WINE', 2),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('16-JAN-2020', '%d-%M-%Y'), 'WHISKY', 1),
('JANUSZ', 'GREASY FORK', STR_TO_DATE('17-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 2),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 3),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 4),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 2),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 3),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('01-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'GREASY FORK', STR_TO_DATE('03-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('05-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('06-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'GREASY FORK', STR_TO_DATE('15-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LITTLE PIRATE', STR_TO_DATE('16-FEB-2020', '%d-%M-%Y'), 'CHAMPAGNE', 1),
('JANUSZ', 'LITTLE PIRATE', STR_TO_DATE('17-FEB-2020', '%d-%M-%Y'), 'CHAMPAGNE', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('20-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('22-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-MAR-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-MAR-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('05-MAR-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-APR-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-MAY-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('03-MAY-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-MAY-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-JUN-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-JUN-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JUN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('9-JUN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JUN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('11-JUN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('12-JUL-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('13-JUL-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-AUG-2020', '%d-%M-%Y'), 'BEER', 1),
('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 2),
('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3),
('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 4),
('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 5),
('PETER', 'LONG JOHN', STR_TO_DATE('11-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1),
('PETER', 'LAZY LOBSTER', STR_TO_DATE('09-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('PETER', 'GREASY FORK', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 1),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 2),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 3),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 4),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 5),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 6),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 7),
('MARY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1),
('MARY', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 1),
('MARY', 'CAPTAIN MOORE', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1),
('MARY', 'LONG JOHN', STR_TO_DATE('05-APR-2020', '%d-%M-%Y'), 'RED WINE', 1),
('MARY', 'GREASY FORK', STR_TO_DATE('24-APR-2020', '%d-%M-%Y'), 'BEER', 1),
('JOHN', 'LONG JOHN', STR_TO_DATE('13-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 1),
('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 2),
('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 3),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 2),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 3),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 4),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 5),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 6),
('JOHN', 'LONG JOHN', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('JOHN', 'LONG JOHN', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'RED WINE', 1),
('JOHN', 'CAPTAIN MOORE', STR_TO_DATE('15-APR-2020', '%d-%M-%Y'), 'BEER', 1),
('JAMES', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 1),
('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 2),
('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 3),
('JAMES', 'LONG JOHN', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1),
('JAMES', 'LITTLE PIRATE', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1),
('JAMES', 'SWEET DREAMS', STR_TO_DATE('23-JUN-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 2),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 3),
('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHISKY', 1),
('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHISKY', 2),
('SERGIEY', 'GREASY FORK', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('06-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('23-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'WHISKY', 1),
('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('09-MAR-2020', '%d-%M-%Y'), 'WHISKY', 1),
('SERGIEY', 'LITTLE PIRATE', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'VODKA', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'),'BEER', 2),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 3),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 2),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 4),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'RED WINE', 2),
('CLAUDE', 'GREASY FORK', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'RED WINE', 1),
('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('05-APR-2020', '%d-%M-%Y'), 'RED WINE', 1),
('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'RED WINE', 1),
('CLAUDE', 'GREASY FORK', STR_TO_DATE('20-APR-2020', '%d-%M-%Y'), 'BEER', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('16-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('17-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);
mysql
sql
select
sql-insert
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-06-11 14:12:08

我不太理解你的问题，但我认为你需要一个触发器来插入这个数据：

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

在另一张桌子上。我给你这个触发器，但首先创建一个表，例如test来收集数据：

这是测试表：

代码语言：javascript
复制
DROP TABLE IF EXISTS "test";
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `drinker` varchar(50) NOT NULL,
  `odate` date NOT NULL,
  `total` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

这是触发器

代码语言：javascript
复制
DROP TRIGGER IF EXISTS "test";
DELIMIT $$
CREATE TRIGGER `test` AFTER INSERT ON `orders` FOR EACH ROW BEGIN
    INSERT into test (drinker, odate, total)
    SELECT DRINKER, ODATE, COUNT(DRINK) TOTAL FROM ORDERS;
END
$$
DELIMIT;

我不知道这是否对您有帮助，但我也开始了，有关触发器的更多信息，我给您这个链接：https://sql.sh/cours/create-trigger

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/62304605

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

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

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

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

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