Scenario 1_ Base Unit revenue re-imputation

Scene: The existing city January transfers revenue archive details, July basic unit adjustment, is required to collect the basic unit of January transfers income details.

An SQL statement equivalent to the VLOOKUP exact match feature in Excel, which corresponds to the record one by one required for each table, based on the key fields common to each table.

SQL Join Syntax Essentials:

① need to use multiple tables in the FROM clause when connecting;

② must use an ON clause when connecting, and write between from and where;

③ the columns in the SELECT clause when using a connection need to be written according to the < table's alias >.< column name > format;

④ connection INNER Join Select a record that matches key fields in two tables;

⑤ left JOIN selects all records in the first table and records in the second table that match the key fields;

⑥ Right-connect to select All records for the second table weight and the first table-critical field to match.

--View "Revenue summary Details _ new"

CREATE VIEW Revenue Summary Details _ New (phone number, transfers month, transfers amount, channel code, channel name, new base unit)

SELECT Revenue summary details. Phone number

, Revenue summary details. Transfers Month

, Revenue summary details. Transfers Amount

, revenue summary details. Channel Coding

, revenue summary details. Channel Name

, income collection. New base Unit

from Revenue summary detail left JOIN revenue imputation

on revenue summary details. CHANNEL encoding = revenue collection. Channel coding

--screening base unit A for transfers customers

SELECT *

from Revenue summary details _ NEW

WHERE new base unit in (' base unit a ')

Reference: http://www.w3school.com.cn/sql/sql_join.asp

Scenario 1_ Base Unit revenue re-imputation