"SQL" Oracle generates temporary tables

In everyday SQL queries, we need to process the data that we want to query, and then query it in the pre-processed data. At this point we need to use the temporary table, pre-processing the data into the temporary table, and then in the temporary list according to the conditions we need to query.

Let's say we have an income table: Incoming, the fields are: id,name,amt,cur we require that each person's AMT be converted into $ after and. Due to the difference in currency, we need to convert the Amt currency into renminbi in advance and then calculate the currency.

Another table is the XRT table, which holds the RMB premium for each currency.

There are two methods, one is to use the with as method, to generate a temporary table temp, and then in this temporary list.

With temp as (select Name, amt* (select Cnyrate from XRT x where x.cur=i.cur) from incoming) select name, SUM (AMT) from Inc Oming GROUP BY name

The second method is to use the Select method

Select Name,sum (AMT) from (select name,amt* (selct cnyrate from XRT x where x.cur=i.cur) from incoming) the temp group by name;

　　

"SQL" Oracle generates temporary tables