Shulou(Shulou.com)06/02 Report--

This article mainly explains "the use of SQL sentence with". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the use of SQL sentence with".

With-SQL statement uses the

With is not supported for use in Mysql, but it can be used in hive,odps

There is something about the with statement:

The with statement is equivalent to creating a temporary virtual table, but it will not be physically created

With statements, you can couple the business, each with statement, a separate sub-module, and finally use the underlying table to concatenate them

Example-pseudo code:

With tmp_table_trade as (

-- order data

Select

From order_table

)

, tmp_table_track as (

-- Traffic utrack data

Select

From utrack_table

)

-- result set

Select

From tmp_table_trade

Join tmp_table_track

On (key-value pair)-create a table first

Drop table if exists tmp_table_with

CREATE TABLE IF NOT EXISTS tmp_table_with (

User_id STRING

Create_time STRING)

-- with statement

With tmp_sample (

Select user_id

, create_time

From t_user

Where-condition)

The results are stored in the temporary table

Insert into table tmp_sample

Select user_id,create_time

From tmp_sample

The sample code is as follows:

With result statements can also be saved in a table

Effect: business data can be abstracted, independent into a module, convenient sub-module data maintenance, and data verification

At this point, I believe you have a deeper understanding of "the use of the SQL statement with". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.