The SQL query to implement is very primitive:
Request from the first table for a second tabular data query, after the Internet query can write the following sql:
Copy Code code as follows:
SELECT * FROM Userino
SELECT * FROM (
SELECT DISTINCT Userpart
From Userino
) A
OUTER APPLY (
SELECT
[Usernames]= Replace (replace (SELECT username as value from Userino N
WHERE Userpart = A.userpart ORDER by n.username ASC for XML AUTO), '/><n value= ', '/'
, ' <n value= ', ', '], '/> ', '
) N
OUTER APPLY (
SELECT
[Username_cns]= Replace (replace (SELECT username_cn as value from Userino M
WHERE Userpart = A.userpart ORDER by m.username ASC for XML AUTO), '/><m value= ', '/'
, ' <m value= ', ', '], '/> ', '
) M
Now the SQL is analyzed:
The total points used are: OUTER apply,for XML AUTO. Because there is no deep research on SQL Server, record
OUTER Apply is a SQL2005 start to support a query method, similar to the connection query, is a concatenation of two of query results, but it is strange that the use of OUTER apply to the query after the application can use the previous query results.
Such as:
Copy Code code as follows:
SELECT * FROM
(SELECT * from Userino) A
Cross Join (select username from Userino
where username = a.username) B
SELECT * FROM
(SELECT * from Userino) A
Join (select username from Userino) B on a.username = B.username
SELECT * FROM
(SELECT * from Userino) A
OUTER APPLY (select username from Userino
where username = a.username) B
The first paragraph of SQL is clearly wrong, there are two reasons: 1. The Cross join is inherently unconditional, 2. SQL Server will burst the following error:
The multi-part identifier "A.username" could not to be bound.
You may say that a conditional join query is not written like this, it should be written as a second SQL, in fact, this write and the third SQL in the use of outer apply implementation of the effect is the same
But Outer apply can also achieve the following effect
Copy Code code as follows:
SELECT * FROM
(SELECT * from Userino) A
OUTER APPLY (select [value] = a.username+ ' test ') B
I'm afraid the direct use of join is a bit of a hassle, the example above may not make sense, in fact SQL2005 the application connection method is mainly to use the results of the query that has been executed in the connection query
In addition to "OUTER apply", SQL Server and cross apply, the difference is mainly in the processing of null values
FOR XML auto is mainly used to return SQL query results directly to the XML statement, for XML in addition to AUTO and raw and explicit, see "Super Simple: Use for XML Auto control XML output"
At the beginning of the article in the SQL, is the use of the above two features, the first use of outer apply to implement similar to the use of Userpart to group the effect, to filter out the user in each Userpart, and then because the filtered results are more than one line, so use for XML to concatenate multiple rows of data into XML, and finally split the XML ....
In summary, feel this implementation is unique, and learn some of the features of SQL Server, and share with you
