Some of the more interesting SQL that I've written in my life.

Recursive

With Myrecursion as (

SELECT * from recursion where id=1

UNION ALL select R.* from Myrecursion m,recursion R where M.id=r.pid

)

SELECT * FROM Myrecursion

Ps:union all does not seek to re-set

Recursion is used in many places, such as the model bindings in ASP . NET, such as the tree-like menu

Ranking

The following table is a Sales Performance table, I do a ranking of sales performance, showing the ranking results

SELECT A1. Name, A1. Sales, COUNT (a2.sales) Sales_rank

From ranking a1, ranking A2

WHERE A1. Sales < A2. Sales or (A1. Sales=a2. Sales and A1. Name = A2. Name)

GROUP by A1. Name, A1. Sales

ORDER by A1. Sales DESC, A1. Name DESC;

Results:

The point is to compare yourself with yourself and find out if A1 sales is smaller than A2 's sales data or Name and the Sales are equal to the data ( All the data in A1 to compare every data in A2)

Results that are not grouped:

SELECT A1. Name, A1. Sales, A2.sales Sales_rank

From ranking a1, ranking A2

WHERE A1. Sales < A2. Sales or (A1. Sales=a2. Sales and A1. Name = A2. Name)

As a result, the results are straightforward. As long as the group Count is ranked.

In fact, there is a problem is to have a side-by-side rankings, for example, there is a tie 3 , the fourth place does not exist.

These can be adjusted according to the specific rules of the program, hehe

Go heavy

Sometimes we encounter some duplicate data in the table,

First, remove all duplicate data except the ID

Declare @t1 table (ID int,name nchar (TEN), Text nchar (10))

Insert into @t1 (name,text) (select distinct name,text from Mydistinct1)

Delete from Mydistinct1

Insert into Mydistinct1 (name,text) (select Name,text from @t1)

Ps:@t defines a virtual table, inserts data from the virtual tables with distinct , empties the original table, and then inserts the data from the virtual table into the original.

The second is to remove duplicate data for the specified column.

Delete from mydistinct where ID not in (the Select MIN (ID) from MYDISTINCT Group by name)

Ps:sql is very simple, after grouping a ID in the group, take the smallest one, delete the other ID

Row to Column

Select name as name,

Max (case course when ' language ' then score else 0 end) language,

Max (case course when ' math ' then fraction else 0 end) Math,

Max (case course when ' physical ' then fraction else 0 end) physical

From TB

Group BY name

Results

Ps: not much to explain at a glance

all data for the specified column after the FOR XML Path reality group

Data sheet:

The FOR XML path results are as follows:

SELECT * from Forxmlpath for XML path (")

Grouped results:

Select Name,min (text) from Forxmlpath GROUP by name

After grouping, except for this grouping column, the other columns are to be displayed using an aggregate function, only one or the quantity or total in the result will be displayed

We can use the attributes of the for XML path to display all the data in one column and specify the display format

Comma interval:

Select Text+ ', ' from Forxmlpath for XML Path (')

Comma interval shows grouped non-grouped columns:

Select Name, (select Text+ ', ' from Forxmlpath where a.name=name for XML path ('))

From Forxmlpath a group by name

Remove the trailing comma:

Use the left function to intercept

Select Name,left (Text,len (text)-1) text

From (select Name, (select Text+ ', ' from Forxmlpath where a.name=name for XML path (')) text

From Forxmlpath a group by name) T

Some of the more interesting SQL that I've written in my life.