sql - 从选择查询中查询插入

Question

我有一个名为 tb_tweetkurator_test 的表，查询是:

SELECT id_user,
        COUNT(id_tweet) as JML_TWEET,
        COUNT(IF(url = 'yes', url, NULL)) as JML_TWEET_URL,
        COUNT(IF(mention = 'yes', mention, NULL)) as JML_TWEET_MENTION,
        COUNT(IF(retweet = 'yes', retweet, NULL)) as JML_TWEET_RETWEET,
        COUNT(IF(tweet_biasa = 'yes', tweet_biasa, NULL)) as JML_TWEET_BIASA
FROM tb_tweetkurator_test
GROUP BY id_user;

结果是

id_user   | JML_TWEET | JML_TWEET_URL| JML_TWEET_MENTION | JML_TWEET_RETWEET | ML_TWEET_BIASA

16912985  | 20        | 6            | 13                | 0                 | 3
23343960  | 10        | 10           | 0                 | 0                 | 0
46651600  | 20        | 9            | 17                |15                 | 2
267164011 | 20        | 14           |10                 |4

让我感到困惑的是，我可以将上面的数据(来自 SELECT 查询)插入到新表中吗？

Answer 1

是的。您可以使用 create-as-select 从这个结果集中创建一个新表:

CREATE TABLE new_table
SELECT id_user,
        COUNT(id_tweet) as JML_TWEET,
        COUNT(IF(url = 'yes', url, NULL)) as JML_TWEET_URL,
        COUNT(IF(mention = 'yes', mention, NULL)) as JML_TWEET_MENTION,
        COUNT(IF(retweet = 'yes', retweet, NULL)) as JML_TWEET_RETWEET,
        COUNT(IF(tweet_biasa = 'yes', tweet_biasa, NULL)) as JML_TWEET_BIASA
FROM tb_tweetkurator_test
GROUP BY id_user;

或者将其插入到现有表中:

INSERT INTO new_table
SELECT id_user,
        COUNT(id_tweet) as JML_TWEET,
        COUNT(IF(url = 'yes', url, NULL)) as JML_TWEET_URL,
        COUNT(IF(mention = 'yes', mention, NULL)) as JML_TWEET_MENTION,
        COUNT(IF(retweet = 'yes', retweet, NULL)) as JML_TWEET_RETWEET,
        COUNT(IF(tweet_biasa = 'yes', tweet_biasa, NULL)) as JML_TWEET_BIASA
FROM tb_tweetkurator_test
GROUP BY id_user;