MYSQL 使用WITH做临时表

最新推荐文章于 2024-09-12 06:46:38 发布

吕小布的貂蝉i

最新推荐文章于 2024-09-12 06:46:38 发布

阅读量3.2k

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/XJJ_XGB/article/details/127775201

版权

MYSQL 使用WITH做临时表

使用with做临时表以便查询需要使用表
如果需要做多个临时表（用，隔开即可）

使用with做临时表以便查询需要使用表

WITH temp AS ( SELECT * FROM honghu_ads_log ) SELECT
DATE_FORMAT( `create_date`, '%Y-%m-%d' ) AS xaxis,
( SELECT count( id ) FROM temp ) AS qv,
( WITH uTemp AS ( SELECT * FROM temp GROUP BY ip ) SELECT count( id ) FROM uTemp ) AS uv,
( SELECT count( id ) FROM temp WHERE op_type = 'landing_click' ) AS landing_qv,
( WITH uTemp AS ( SELECT * FROM temp WHERE op_type = 'landing_click' GROUP BY ip ) SELECT count( id ) FROM uTemp ) AS landing_uv 
FROM
	temp 
GROUP BY
	xaxis

如果需要做多个临时表（用，隔开即可）

with a as (select 1 as id from dual union all select 2 as id from dual) , 
     b as (select 1 as id,'Adam' as name from dual union all select 3 as id,'Tom' as name from dual)
select a.id,b.name from a left join b on a.id=b.id