mysql select插入_如何在MySQL中使用SELECT语句插入数据？

最新推荐文章于 2024-07-21 03:10:20 发布

weixin_39541693

最新推荐文章于 2024-07-21 03:10:20 发布

阅读量825

点赞数

文章标签： mysql select插入

本文链接：https://blog.csdn.net/weixin_39541693/article/details/114758632

版权

我需要找到单日每位乘客的预订总量。例如:+---------+-------------+-------+

| name | departDate | TOTAL |

+---------+-------------+-------+

| Douglas | 2019/05/26 | 2 |

| Mike | 2020-02-12 | 1 |

除道格拉斯当天有2班航班外，其余旅客均返回1班。然后使用该信息，我必须为乘客创建一个新的预订，在同一天有两个航班(道格拉斯)。

现在我收到以下输出:name departDate TOTAL

'Douglas' '2019-05-10 22:05:00', '1'

'Ben', '2019-04-23 07:05:00', '1'

'Jean', '2019-04-23 07:05:00', '1'

'Douglas' '2019-05-26 09:35:00', '1'

'Ben', '2019-05-18 06:00:00', '1'

'Mike', '2019-05-18 06:00:00', '1'

'Josef', '2019-05-05 06:05:00', '1'

'Douglas' '2019-05-26 22:05:00', '1'

'Harry', '2019-04-23 07:10:00', '1'

'Will', '2019-05-10 08:45:00', '1'

'Ben', '2019-05-10 08:45:00', '1'

'Mike', '2019-05-10 08:45:00', '1'

'Alex', '2019-04-24 07:10:00', '1'

'Duke', '2019-04-24 07:10:00', '1'

'Alex', '2019-04-26 13:10:00', '1'

当我试图达到这个输出时:name departDate TOTAL

'Douglas' '2019-05-10', '1'

'Ben', '2019-04-23', '1'

'Jean', '2019-04-23', '1'

'Douglas' '2019-05-26', '2'

'Ben', '2019-05-18', '1'

'Mike', '2019-05-18', '1'

'Josef', '2019-05-05', '1'

'Harry', '2019-04-23', '1'

'Will', '2019-05-10', '1'

'Ben', '2019-05-10', '1'

'Mike', '2019-05-10', '1'

'Alex', '2019-04-24', '1'

'Duke', '2019-04-24', '1'

'Alex', '2019-04-26', '1'

我知道我不能得到2旁边道格拉斯为2019/05/26，因为COUNT()正在读取当天的每个预订作为单独的预订(不是在同一天)，因为时间不同。

我试图弄清楚的是，如何统计每天的总预订量，不包括那些日子的时间。

这是表格:CREATE TABLE BOOKING (

name VARCHAR(30), /* Passenger's name*/

DOB DATE, /* Passenger's DOB */

flightNumber CHAR(6), /* Flight number */

departAirport VARCHAR(50), /* Departure airport */

departTime DATETIME, /* Departure date and time */

class CHAR(10), /* class of the seat */

bookingTime DATETIME NOT NULL, /* Booking time */

CONSTRAINT BOOKING_PK PRIMARY KEY(name, DOB, flightNumber, departAirport, departTime, class, bookingTime),

CONSTRAINT BOOKING_FK1 FOREIGN KEY(name, DOB) REFERENCES PASSENGER(name, DOB),

CONSTRAINT BOOKING_FK2 FOREIGN KEY(flightNumber, departAirport, departTime, class) REFERENCES SEATING(flightNumber, departAirport, departTime, class));

下面是insert语句:INSERT INTO BOOKING VALUES ('Mike', STR_TO_DATE('10/08/1982', '%d/%m/%Y'), 'QF8764', 'SYD', STR_TO_DATE('10/05/2019 08:45', '%d/%m/%Y %H:%i'), 'ECONOMY', STR_TO_DATE('1/05/2019 10:35', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Mike', STR_TO_DATE('10/08/1982', '%d/%m/%Y'), 'QF322', 'AKL', STR_TO_DATE('18/05/2019 06:00', '%d/%m/%Y %H:%i'), 'ECONOMY', STR_TO_DATE('1/05/2019 10:35', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Jean', STR_TO_DATE('12/05/1988', '%d/%m/%Y'), 'JQ402', 'SYD', STR_TO_DATE('23/04/2019 07:05', '%d/%m/%Y %H:%i'), 'ECONOMY', STR_TO_DATE('1/04/2019 12:05', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Alex', STR_TO_DATE('21/07/1992', '%d/%m/%Y'), 'VA505', 'SYD', STR_TO_DATE('24/04/2019 07:10', '%d/%m/%Y %H:%i'), 'BUSINESS', STR_TO_DATE('10/04/2019 09:35', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Alex', STR_TO_DATE('21/07/1992', '%d/%m/%Y'), 'VA523', 'SYD', STR_TO_DATE('26/04/2019 13:10', '%d/%m/%Y %H:%i'), 'BUSINESS', STR_TO_DATE('10/04/2019 09:35', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Ben', STR_TO_DATE('22/04/1994', '%d/%m/%Y'), 'QF8764', 'SYD', STR_TO_DATE('10/05/2019 08:45', '%d/%m/%Y %H:%i'), 'ECONOMY', STR_TO_DATE('25/04/2019 11:35', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Ben', STR_TO_DATE('22/04/1994', '%d/%m/%Y'), 'QF322', 'AKL', STR_TO_DATE('18/05/2019 06:00', '%d/%m/%Y %H:%i'), 'ECONOMY', STR_TO_DATE('27/04/2019 16:15', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Ben', STR_TO_DATE('22/04/1994', '%d/%m/%Y'), 'JQ402', 'SYD', STR_TO_DATE('23/04/2019 07:05', '%d/%m/%Y %H:%i'), 'ECONOMY', STR_TO_DATE('17/04/2019 14:30', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Duke', STR_TO_DATE('28/03/1974', '%d/%m/%Y'), 'VA505', 'SYD', STR_TO_DATE('24/04/2019 07:10', '%d/%m/%Y %H:%i'), 'BUSINESS', STR_TO_DATE('20/04/2019 13:30', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Josef', STR_TO_DATE('27/11/1982', '%d/%m/%Y'), 'QF401', 'SYD', STR_TO_DATE('05/05/2019 06:05', '%d/%m/%Y %H:%i'), 'ECONOMY', STR_TO_DATE('22/04/2019 10:30', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Harry', STR_TO_DATE('16/07/1990', '%d/%m/%Y'), 'QF860', 'SYD', STR_TO_DATE('23/04/2019 07:10', '%d/%m/%Y %H:%i'), 'FIRST', STR_TO_DATE('21/04/2019 10:30', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Will', STR_TO_DATE('20/10/1977', '%d/%m/%Y'), 'QF8764', 'SYD', STR_TO_DATE('10/05/2019 08:45', '%d/%m/%Y %H:%i'), 'BUSINESS', STR_TO_DATE('25/04/2019 15:30', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Douglas', STR_TO_DATE('20/11/1967', '%d/%m/%Y'), 'QF497', 'SYD', STR_TO_DATE('26/05/2019 22:05', '%d/%m/%Y %H:%i'), 'ECONOMY', STR_TO_DATE('21/04/2019 12:30', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Douglas', STR_TO_DATE('20/11/1967', '%d/%m/%Y'), 'CA2842', 'PEK', STR_TO_DATE('10/05/2019 22:05', '%d/%m/%Y %H:%i'), 'ECONOMY', STR_TO_DATE('21/04/2019 12:30', '%d/%m/%Y %H:%i'));

INSERT INTO BOOKING VALUES ('Douglas', STR_TO_DATE('20/11/1967', '%d/%m/%Y'), 'QF129', 'SYD', STR_TO_DATE('26/05/2019 09:35', '%d/%m/%Y %H:%i'), 'ECONOMY', STR_TO_DATE('01/05/2019 10:30', '%d/%m/%Y %H:%i'));