An SQL query method learned by a Q & A (Multiple-to-multiple-link query interview questions)

Q & A location: http://space.cnblogs.com/q/20546/q & A: I have asked more questions about the relationship: 5 [resolved issues] Browse: 52 times 00

Student table T1

Stu_id	Stu_name
1	Zhang San
2	Li Si
3	Wang Wu

Course list T2

Co_id	Co_name
1	Java
2	C #
3	C
4	C ++
5	Javascript

Link Table T3

Stu_id	Co_id
1	1
1	4
2	1
2	3
2	5

I was asked a database question a few days ago during the interview. No answer has been found.

The questions are as follows: there are student tables, curriculum tables, and relationship tables to find out the courses selected by the students.

I wanted to query through the connection. Of course, this will repeat the same student name. For example:

Zhang San	Java
Zhang San	C ++

However, the question requirements are as follows: James Java, C ++

How can I write this SQL statement.

]]]]

After seeing this blog question, I carefully thought about the previous SQL knowledge and found that I had to assign the query result to the variable first and then query it again. However, this question clearly does not mean this, so I sent the question to the flash memory area and expected the experts to answer it. After I started my work today, I checked the answer and found that I already had the answer. So I tried my database and failed to run the experiment because the answer was incomplete. So I carefully read the answer and found two unknown Dongdong stuff () functions and for XML Path. Then I went to the Internet to search for the meaning of the two.

Stuff (str1, start, length, str2) is a simple replacement string function that allows you to set the start position.
Str1 is the original string.
Start is the starting position to be replaced.
Length is the length of the original string.
Str2 is the new content to be replaced.
Example: the result of stuff ('000000', 12345, '000') is '000000', starting with '2', the second digit of '20170901'. The length is two, '23', replace it with '000', and the result is '20180101 '. Of course, there are other simple methods that can achieve the same effect.

For XML Path is more complex. It is used to generate XML data from the query results. Specific usage can go here to see http://www.docin.com/p-73893088.html

In general, I started to try again after I understood the for XML Path.

After several attempts, write 'select', '+ co_name from T2 where co_id = 1 or co_id = 2 for XML Path ('') to query". java. C #"

Then the direction is determined. I need to find the associated co_id through the stu_id in Table T3, and then get the correct answer through the above statement.

The solution is correct. The answer is just a matter of course. After multiple attempts, you can get the statement.

Select distinct stu_id, (select convert (varchar (20), co_id) + ',' from T3 where a. stu_id = stu_id for XML Path ('') from T3

The course Id corresponding to the student ID is successfully identified as required, and then the student ID is changed to the Student name

Select distinct stu_name, (select convert (varchar (20), co_id) + ',' from T3 where. stu_id = stu_id for XML Path ('') from T3 A, t1 B where. stu_id = B. stu_id

Then, replace the course Id with the course name, and use the stuff () function to replace the first ',' with null.

Select distinct stu_name, stuff (select ',' + co_name from T3, T2 where. stu_id = stu_id and t3.co _ id = t2.co _ id for XML Path (''), 1,1,'') from T3 A, t1 B where. stu_id = B. stu_id

This is already the correct answer to the question. Because I write and play on the draft, the code is quite messy, and the multi-Table query and writing are not standard, and I am just a little rookie, you are welcome to write simple and elegant query statements for me to learn.

Thank you!