Yii之使用mysql临时表

 mysql创建临时表

CREATE TEMPORARY TABLE IF NOT EXISTS table_name

// 创建临时表 用户
$createTable = 'CREATE TEMPORARY TABLE IF NOT EXISTS cache_user_ids(`user_id` INT NOT NULL, KEY `idx_user_id` (`user_id`) USING BTREE); DELETE * from cache_user_ids';
Yii::$app->db->createCommand($createTable)->execute();
$sqlList = '';
foreach($users as $user_id) {
    $sqlList .= "INSERT INTO cache_user_ids(user_id) VALUES({$user_id});";
}
if(!empty($sqlList)) {
    Yii::$app->db->createCommand($sqlList)->execute();
}

// 创建临时表 产品
$createTable = 'CREATE TEMPORARY TABLE IF NOT EXISTS cache_product_ids(`product_id` INT NOT NULL, KEY `idx_product_id` (`product_id`) USING BTREE); DELETE * from cache_product_ids';
Yii::$app->db->createCommand($createTable)->execute();
$sqlList = '';
foreach($product_ids as $product_id) {
    $sqlList .= "INSERT INTO cache_product_ids(product_id) VALUES({$product_id});";
}
if(!empty($sqlList)) {
    Yii::$app->db->createCommand($sqlList)->execute();
}

$query = UserLearnRecordLog::find()->alias('ulr')
        ->select(['ulr.type', 'SUM(if(ulr.view_time > co.timeline, co.timeline, ulr.view_time)) as view_time', 'COUNT(ulr.user_id) as user_count', 'SUM(co.timeline) as timeline'])
        ->join('LEFT JOIN', '{{%product_online}} as co', 'co.product_id = ulr.product_id')
        //->where(['in', 'ulr.user_id', (new yii\db\Query())->select(['user_id'])->from('cache_user_ids')]);
        ->join('LEFT JOIN', 'cache_user_ids as cui', 'cui.user_id = ulr.user_id')
        ->join('LEFT JOIN', 'cache_product_ids as cci', 'cci.product_id = ulr.product_id');

用完之后记得删除

$createTable = 'DROP TEMPORARY TABLE IF EXISTS cache_user_ids;DROP TEMPORARY TABLE IF EXISTS cache_product_ids;';
Yii::$app->db->createCommand($createTable)->execute();