Shulou(Shulou.com)05/31 Report--

This article shares with you the content of the sample analysis of self-connection in SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

What is self-connection?

So how do we understand self-connection?

To put it bluntly, self-join is actually two tables with exactly the same table structure and data content. when we do data processing, we usually rename them separately to distinguish them (implication: we have to rename them. Otherwise, the database doesn't know who they are and who they are, and then associate them.

Let's take a look at how they connect themselves.

Sample table content

There is the following table Student. The table structure and data are as follows:

When we do self-connection, we do not add any filter conditions. The details are as follows:

SELECT s1.Sname AS Sname1, s2.Sname AS Sname2 FROM Student s2,Student s1

The result is as follows:

The result looks familiar. I seem to have seen it somewhere. Yes, it's actually our mathematical arrangement.

The general arrangement is purple:

First, Zhang San in name1 is combined with Zhang San, Li Si and Wang Wu in name2 to form the first three records.

Then Li Si in name1 and Zhang San, Li Si and Wang Wu in name2 synthesize the middle three records respectively.

Finally, Wang Wu in name1 was combined with Zhang San, Li Si and Wang Wu in name2 to form the last three records.

In this way, we get the results above.

But most of our common self-connections are conditional. No matter what the conditions are, they are actually filtered on the above results.

For example, if we want to find one-to-one corresponding data, we can write:

SELECT s1.Sname AS Sname1, s2.Sname AS Sname2 FROM Student s2,Student s1 WHERE s1.Sname=s2.Sname

The result is an one-to-one correspondence between two self-joined tables:

What's here is the essence of self-connection. Zhang San himself is associated with himself, so what kind of connection do you think this is?

But in our work, the purpose of using self-join is not to associate ourselves with ourselves, but more often to combine with others in the table, like this:

SELECT s1.Sname AS Sname1, s2.Sname AS Sname2 FROM Student s2,Student s1 WHERE s1.Snames2.Sname

The results are as follows:

In addition, if we want to further exclude duplicate data rows, such as Zhang San, Li Si and Li Si, Zhang San, we default that these two rows are duplicate data (although they are in different order, but in mathematical sets, these two rows can be regarded as the same result set). If you only want to keep one, you can do so:

SELECT s1.Sname AS Sname1, s2.Sname AS Sname2 FROM Student S2 Student S1 WHERE s1.Sname > s2.Sname

The results are as follows:

In this way, we get three rows of "non-repetitive" data, which is the same as the mathematical combination.

Self-connecting actual combat

Above we give an example of self-connection to deal with the continuity problem, and here is another example of using self-connection to remove duplicate data:

Sample table structure

There is a Student table with the following structure and data:

We want to delete duplicate rows of data in the table. How do we write this SQL?

Let's analyze it and find that there is no primary key ID in this table. In order to distinguish between them, I need to add a virtual column primary key to it. What should I do? You can write like this:

SELECT IDENTITY (INT) ID, Sname, Score INTO Student_Tmp FROM Student

Here we use the self-growing function IDENTITY () to generate an ID similar to a self-increasing primary key, and insert the result into Student_Tmp, where the details of Student_Tmp are as follows:

We can then delete duplicates by keeping the maximum or minimum values, as follows:

DELETE FROM Student_Tmp WHERE Student_Tmp.ID < (SELECT Max (s2.ID) FROM Student_Tmp S2 WHERE Student_Tmp.Sname=s2.Sname AND Student_Tmp.Score=s2.Score)

So we can delete the columns with ID 3 and 4, and query the contents of the Student_Tmp as follows:

Note: due to some limitations of SQL Server, we cannot do the above operation on the source table. In order to show you the role of self-join, we have made some adjustments.

If you want to delete duplicate rows in the original table in SQL Server, you can use the following methods:

SELECT DISTINCT * INTO Student_Tmp FROM Student TRUNCATE TABLE Student INSERT INTO Student SELECT * FROM Student_Tmp DROP TABLE Student_Tmp

Through the above method, we delete the duplicate lines in the Student_Tmp using the self-connecting method.

Thank you for reading! This is the end of this article on "sample Analysis of self-connection in SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.