Multiple rows of SQL multi-column duplicates

In the SQL query we will encounter the results of the query such as this:

Query the SQL statement for this table:

Select R.role_name,u.userid,u.username,u.truename from Base_userinfor u left joins base_userrole ur on U.userid=ur. User_ID left joins Base_roleinfor R on R.role_id=ur. role_id

Take the money three lines for a role_name not the same as the number of the other columns is the same as we want to result, even if we wanted to do so, but when we want to display a page in the foreground, we will do

So how do we go to query how to set it apart to query of course can also solve, but think about how much we have to query the number of secondary connection database to fewer times.

It would be a lot of trouble to think about it, and it would certainly reduce the execution efficiency of the program. So we should think of another method of how to query once can become this effect.

Think about the result of our query is a table and we'll see what the composition of this table's XML file is.

View the SQL statement of the table's constituent XML file:

Select *  from (Selectfrom leftjoins on u.userid=ur. user_id  Left Join  on r.role_id= for XML Path

The composition of the XML file that finds the table

Think about it, if we could replace the composition of the XML file, would we be able to change the structure of the table?

Let's start by looking at a simple table, how the multiple lines of repetition are merged

SELECT * FROM table1

Query Result:

Look at a simple table like this How do we combine the same columns

The first step: of course, look at the table's XML file to see how the table is composed

SELECT * FROM table1 for XML path;

Results of the query:

<row>  <uid>1</uid>  <rname> admin </rname></row><row>  <uid >1</uid>  <rname> shop owners </rname></row><row>  <uid>1</uid>  <rname> buyers </rname></row><row>  <uid>2</uid>  <rname> shopkeeper </ rname></row><row>  <uid>2</uid>  <rname> shop owners </rname></row>

Based on the results of the query, we can see that the simple structure of the table, perhaps the database itself is defined well or we are automatically generated at the time of the query, whether it is defined or generated by ourselves we will first see if it is possible to change the structure of the table If so, can we change the structure we want?

Step Two: change the structure of the table

SELECT * FROM table1 for XML path;

Or is this sentence since it is possible to query the XML file of a table to make it possible to change the composition of the table's XML file? Try to know (the program is you think more than you hit a few lines of code a try)

SELECT * FROM table1 for XML path (' a ');

Let's write a statement like this to see how the composition of our table's XML file becomes:

<a>  <uid>1</uid>  <rname> admin </rname></a><a>  <uid>1 </uid>  <rname> shop owners </rname></a><a>  <uid>1</uid>  <rname > Buyers </rname></a><a>  <uid>2</uid>  <rname> shopkeeper </rname></a ><a>  <uid>2</uid>  <rname> shop owners </rname></a>

Compare the query results two times to see what happens to our table's XML file:

SELECT * FROM table1 for XML path; XML file:<row>  <uid>1</uid>  <rname> Administrator </rname></row>select * FROM table1 For XML path (' a '); Composition of XML files:<a>  <uid>1</uid>  <rname> administrators </rname></a>

can produce <row> tags into <a>, so what we do with a in path (' a ') will become:

Query statement: SELECT * FROM table1 for XML path ("); query result:<uid>1</uid><rname> Administrator </rname><uid>1 </uid><rname> Shop owners </rname><uid>1</uid><rname> buyers </rname><uid>2< /uid><rname> shopkeeper </rname><uid>2</uid><rname> shopkeeper </rname>

Compare the results of the query two times:

SELECT * FROM table1 for XML path (' a '); query results:<a>  <uid>1</uid>  <rname> admin </rname ></a>select * FROM table1 for XML path ("); query Results:<uid>1</uid><rname> admin </rname>

You can see that <a> is removed, what we want is the role of the user and we'll take a look at the character:

Step Three: remove the extra rows

Select Rname from table1 for XML Path ("), query results:<rname> Administrator </rname><rname> shopkeeper </rname><rname > Buyers </rname><rname> Shopkeepers </rname><rname> shopkeepers </rname>

Of course this is still not what we want in the results we want the data inside, so we need to remove the <rname> tag:

Fourth Step: Remove the Label:

Select Rname+ ', ' from Table1 for XML path ('); Results of the query: Administrator, shopkeeper, buyer, shopkeeper, shopkeeper,

That's what we want, and it turns out we got it, but what's the effect of what we want, first look at what we want.

This is the table we want, but how do we make it?

Take a look at our original table:

Think about how we should deform this table:

The UID is multi-line so let's not look at the rname column, we'll start by grouping:

Fifth step: grouping

Grouping according to the UID

Select UID from table1 GROUP by UID

Results:

Well, the most important step is to:

How do we put the administrator, shopkeeper, buyer, on the corresponding UID 1 line, how to put the shopkeeper, the shopkeeper, placed in the corresponding UID 2 line behind

Sixth step: Merging:

That is, add a column after the table. That's easy.

Select UID, (this column is Rname) from the Table1 T Group by UID;

So let's add a column:

The above query when we query all the columns now we add the condition:

Select Rname+ ', ' from table1 t1 where t1.uid=1 for XML path ('); query result: admin, shopkeeper, buyer,

This is not what we want, just change the conditions for a while, now we are merging is to add a column.

Select UID, (this column is Rname) from the Table1 T Group by UID; add rname This column: Select UID, (select Rname+ ', ' from table1 t1 where T1.uid=t.ui D for XML Path (")) as Rname from table1 T Group by UID;

Let's see what the query results are for this statement.

Select UID, (select Rname+ ', ' from table1 t1 where t1.uid=t.uid for XML path (")) as rolenames from table1 T Group by UID;

Results:

This is the result we want.

Okay, the composition of the simple table is going to be a little complicated.

Continue with the questions we just started:

Try crafting this watch.

Query statement:

Select R.role_name,u.userid,u.username,u.truename from Base_userinfor u left joins base_userrole ur on U.userid=ur. User_ID left joins Base_roleinfor R on R.role_id=ur. role_id

How do we merge this watch? Think about it, actually, the truth is the same. We directly put this table when let is the Table1 table, in the first SQL statement set not on the line

Look at the SQL statement just now:

Select UID, (select Rname+ ', ' from table1 t1 where t1.uid=t.uid for XML path (")) as rolenames from table1 T Group by UID; see  Look where it's used, Table1, let's change it. Select UID, (select Rname+ ', ' from (table1 table) t1 where t1.uid=t.uid for XML path (")) as Rolenames from (table1 table) T GROUP by UID;

Another look at our table is the problem of changing the columns of the query:

Let's change.

Select USERID, (select T.role_name+ ', ' from (table1 table)) T where t.userid=t1. USERID for XML Path (")) as RoleName from (table1 table) T1  GROUP by Userid,truename,username

All right, put our "Table1" in, and we'll take a look at the results.

Select USERID, (select T.role_name+ ', ' from (select R.role_name,u.userid,u.username,u.truename from Base_userinfor u Left joins Base_userrole ur on U.userid=ur. User_ID left joins Base_roleinfor R on R.role_id=ur. role_id) t where t.userid=t1. USERID for XML Path (")) as RoleName from (select R.role_name,u.userid,u.username,u.truename from base_userinfor u left Joi n base_userrole ur on u.userid=ur. User_ID left joins Base_roleinfor R on R.role_id=ur. role_id) T1  GROUP by USERID

Query Result:

But compared to the table above, we have a few columns missing, so we're going to add a few columns:

Select Userid,truename,username, (select T.role_name+ ', ' from (select R.role_name,u.userid,u.username,u.truename from Base_userinfor u left joins base_userrole ur on U.userid=ur. User_ID left joins Base_roleinfor R on R.role_id=ur. role_id) t where t.userid=t1. USERID for XML Path (")) as RoleName from (select R.role_name,u.userid,u.username,u.truename from base_userinfor u left Joi n base_userrole ur on u.userid=ur. User_ID left joins Base_roleinfor R on R.role_id=ur. role_id) T1  GROUP by Userid,truename,username

Look at the results of the query, not what we want.

It is important to pay attention to the following statements in the group by, although we write so many grouping conditions is just the first condition and function we still have to write because only then we can query the desired column.

So when you want to query other columns, you need to add this column to the group by.

Multiple rows of SQL multi-column duplicates