將查詢結果插入數據庫行

我有這張表，用於生成第二個表，結果如下：

INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, merchant_id, terminal_id, status, card_brand, currency)
SELECT
       COUNT(*) count,
       SUM(amount) volume,
       DATE(created_at) date,
       YEAR(created_at) year,
       MONTH(created_at) month,
       WEEK(created_at) week,
       DAY(created_at) day,
       type transaction_type,
       contract_id, merchant_id, terminal_id,
       status, card_brand, currency
       FROM payment_transactions
       WHERE created_at BETWEEN '2018-11-11' AND '2018-11-14'
       GROUP by date, contract_id, merchant_id, terminal_id, transaction_type, status, card_brand, currency

當我只執行 select 語句時，我得到：

count   volume  date    year    month   week    day transaction_type    contract_id merchant_id terminal_id status  card_brand  currency
1   3000    2018-11-11  2018    11  45  11  Sale    1   2   1   approved    NULL    USD

但我無法執行 INSERT。我得到錯誤：

Field 'id' doesn't have a default value

我用於 I 序列：

CREATE TABLE `payment_transactions_daily_facts` (
 `id` int(11) NOT NULL,
 `card_brand` varchar(255) DEFAULT NULL,
 `contract_id` int(11) DEFAULT NULL,
 `count` int(11) DEFAULT NULL,
 `currency` varchar(255) DEFAULT NULL,
 `date` datetime DEFAULT NULL,
 `day` int(11) DEFAULT NULL,
 `merchant_id` int(11) DEFAULT NULL,
 `month` int(11) DEFAULT NULL,
 `status` varchar(255) DEFAULT NULL,
 `terminal_id` int(11) DEFAULT NULL,
 `transaction_type` varchar(255) DEFAULT NULL,
 `volume` int(11) DEFAULT NULL,
 `week` int(11) DEFAULT NULL,
 `year` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我如何使用序列作為id？

您的 id 欄位需要 AUTO_INCRMENT 預設值，因此：

ALTER TABLE `payment_transactions_daily_facts`
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT FIRST;

然後，下次您執行 INSERT 時，系統會自動為您創建一個自動增量 ID。

引用自：https://dba.stackexchange.com/questions/229123