SQL Server outside connections, internal connections, cross connections

Small series in the Department of the Department to maintain the final finishing work, encountered such a problem, it is necessary to check the qualitative assessment scores, quantitative assessment of the corresponding data to identify and score calculations, additional points isolated, and finally the three pieces of information to be aggregated after the total score, Assuming that one of the scores is not calculated then it is possible to get results.
Outlook Brother Siang their once-logical. After the qualitative and quantitative score calculations are saved through two views. A view is used to connect the three scores together and calculate.
By the leader of the rules can only be written in SQL statements, because this maintenance is very convenient.

I started with cascading queries and calculations, but the results were not ideal. Finally, we learned the connection query, and after repeated experiments, the following results were achieved:

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >--select--Total rankings row_number () over (ORDER by ISNULL (H.additionalresults), 0 + f.totalscores) as Total rankings,--Park Idf.objectid A S directly under Unit ID,--campus name F. Responsibility as Development zone name,--year F. Yeartime as Year,--quantitative division F. Quantifyscores as quantitative total score,--additional sub-isnull ((H.additionalresults), 0) as rewards and punishments total score,--Total Quantitative + qualitative + additional total isnull ((h.additionalresults), 0 + f.totalscores as total score from--summary, through the left link link, query the following information, and the qualitative and quantitative Total score summary (SELECT-Park Idc.objectid,--Park name C. Responsibility,--year E. Yeartime,--Quantitative ISNULL ((C.quantifyscores), 0) as Quantifyscores,--qualitative and quantitative Division ISNULL ((E.calculatescores), 0) + ISNULL ((c. Quantifyscores), 0) as totalscoresfrom--First level query, query to the qualitative total score as the base table (SELECT-Park Idd.developmentid,--assessment Time D. Yeartime,--qualitative must divide D. Calculatescores fromt_developmentscores D) e--and the first layer of queries, query to quantitative information and calculate a quantitative total of left JOIN (SELECT-Park Ida.objectid,-park name A. Responsibility,--calculation and display of the quantitative ISNULL (cast (A.score as Real), 0) *isnull (CAST (b.weight as Real), 0)/as Quantifyscores FRO M T_developmentquantifyscores A,t_developmentquantifYtarget b WHERE A.tragetid = B.tragetid) c on e.developmentid = C.objectid) F--and quantitative qualitative score summary side by side, query by left link to the following additional sub-JOIN (Sele CT--Park Idg.developmentid,--additional points g. Additionalresultsfrom t_developmentadditionalresults g) h on f.objectid = h.developmentid</span>

Tasted the sweetness of the connection in the SQL statement, so the small part in the online search and practice, with the following results:Example IntroductionThe following sections will be tested using the following two tables :External Connectionleft-join (either outer join):

Description: Based on left table, query all eligible information in the right table, non-existent with null supplement

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >--select * FROM dbo. Testtable1 a left JOIN dbo. TestTable2 b on A.hobbyid = b.hobbyid</span>

the results are as follows:Right-join (outer join)

Description: Query all eligible information in the left table based on the right table, non-existent null supplement

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >--select * FROM dbo. Testtable1 a right JOIN dbo. TestTable2 b on A.hobbyid = b.hobbyid</span>

the results are as follows:fully connected (full join or full outer join)

Description: The information in the two tables is detected, there is no corresponding information, with a null supplement

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >--select * FROM dbo. Testtable1 a full JOIN dbo. TestTable2 b on A.hobbyid = b.hobbyid</span>

the results are as follows:Internal connection (inner join)

Description: Only show eligible information, the condition can be (=, <, >, <>, <=, >=,!<,!>), the following only describes the content of the condition =

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >--select * FROM dbo. Testtable1 a inner JOIN dbo. TestTable2 b on A.hobbyid = b.hobbyid</span>

the results are as follows:Cross JoinUnconditional

Description: The product of all the data in the two tables will be found (assuming that there are 3 rows of data in each table, and that the 3*3=9 row data is finally displayed)

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >--select * FROM dbo. Testtable1 Cross JOIN dbo. testtable2--above is equivalent to the following statement select * FROM dbo. Testtable1, dbo. Testtable2</span>

the results are as follows:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center "> conditional

Description: The unconditional results found above are filtered according to the conditions but here only can be used to indicate that you cannot use on

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >--select * FROM dbo. Testtable1 a cross JOIN dbo. TestTable2 b WHERE A.hobbyid = b.hobbyid</span>

the results are as follows:
SummaryThe phrase is given to himself and to all the readers:
In the future, understand a piece of things will be its ins and outs, and to summarize, so that the future road will be more unobstructed!

SQL Server outside connections, internal connections, cross connections