下面是生成所需插入语句的代码

select concat('INSERT INTO t2 VALUES ', substring(tmp, 2, length(tmp) - 2))
from (
         select replaceAll(toString(groupArray(concat('(', toString(date), ',', name, ',', toString(sum), ')'))), '\'', '') as tmp
         from (
                      (select toDate('2023-10-31') as date, 'George' as name, 1261169 as sum)
                      union all
                      (select toDate('2023-11-01') as date, 'Alan' as name, 1261370 as sum)
                      union all
                      (select toDate('2023-11-02') as date, 'Peter' as name, 1261361 as sum)
                  ) as t
         ) as t2

字符串
输出量：

INSERT INTO t2 VALUES (2023-10-31,George,1261169),(2023-11-01,Alan,1261370),(2023-11-02,Peter,1261361)

型
你可以使用clickhouse playground来测试：https://play.clickhouse.com/play?user=play#c2VsZWN0IDE=
不幸的是，由于某些原因，playground中没有arrayFold函数，该函数可以简化代码，但我使用字符串操作进行了特别的解决方案

有一个更优雅的方法。

SELECT date, name, sumIf(events>0) ev
FROM t1
GROUP BY date, name
FORMAT SQLInsert
SETTINGS 
  output_format_sql_insert_max_batch_size = 3,
  output_format_sql_insert_table_name = 't2'

字符串
输出将是这样的：

INSERT INTO t2 VALUES 
  (2023-10-31, 'George', 1261169), 
  (2023-11-01, 'Alan', 1261370), 
  (2023-11-02, 'Peter', 1261361);

型

奖金

您可以在ClickHouse和MySQL示例之间创建链接，并使用MySQL表引擎直接将数据插入MySQL：

CREATE TABLE t2
(
    ...
)
ENGINE = MySQL('localhost:3306', 'test', 'test', 'bayonet', '123');
INSERT INTO t2
SELECT date, name, sumIf(events>0) ev
FROM t1
GROUP BY date, name

型