Summary of atypical T_sql

?

Two common uses of------over---

The first grouping, of course, is to be aware that the groupings here are not actual groupings, but temporary groupings based on your business needs.

?

Select

Roomguid,room,

AVG (total) over (partition by Bldguid) as Wendy Average,

AVG (total) over (partition by Projguid) as project average price

From P_room

?

-When a line number is used to generate a line number, over () must order by

Select

Row_number () over (order by Roomguid) as RowNum

From P_room

?

?

------Apply usage is more flexible than cross join

Of course, apply is a kind of table join join usage, but in a more flexible form, he can not connect a complete table, can be a table of a field is OK

Note that you must have an alias for the table at the end, even if the alias is not

Cross Apply (select course, score from #T2 where name =a. Name) b

--Test code:

CREATE TABLE #T (name varchar (10))

INSERT into #T values (' Zhang San ')

INSERT into #T values (' John Doe ')

INSERT into #T values (NULL)

??

??

CREATE TABLE #T2 (name varchar (10), course varchar (10), fractional int)

INSERT into #T2 values (' Zhang San ', ' language ', 74)

INSERT into #T2 values (' Zhang San ', ' math ', 83)

INSERT into #T2 values (' Zhang San ', ' physics ', 93)

INSERT into #T2 values (NULL, ' math ', 50)

?

---Cross apply---Note that you must have an alias for the table at the end

SELECT * FROM #T as a

Cross Apply (select course, score from #T2 where name =a. Name) b

?

---outer apply

SELECT * FROM #T as a

Outer Apply (select course, score from #T2 where name =a. Name) b

?

-----with implementation recursion

With Test

As

(

? Select Userguid from MyUser Group by Userguid ORDER by Userguid

)

SELECT * FROM Test

---above is a very simple example of a with usage, where you can view test as a temporary table and then query the

?

--1, if the expression name of the CTE is the same as a data table or view, the SQL statement immediately following the CTE is still using the CTE, and of course, the subsequent SQL statement uses the datasheet or view.

--2, use with the place to note:

--3, you cannot use the following clauses in CTE_query_definition:

--(1) COMPUTE or COMPUTE by

--(2) ORDER by (unless the TOP clause is specified)

--(3) into

--(4) The following items are not allowed in recursive members with query hints

? ? --select DISTINCT

??--GROUP by

? ? --having

? ? --Scalar aggregation

? ? --top

??--left, right, OUTER join (allows INNER join to appear)

??--sub-query OPTION clause

--(5) for XML

--(6) for BROWSE

---4, but with is generally used to implement recursion, if you want to implement recursion, then you have to use the union all, in fact, the implementation of recursive query with a CTE is more dead things option (maxrecursion 1)-option (Maxrecursion 1) Control the level of recursion

?

With MyClass

As

(----First I'm going to get my root node on condition.

? SELECT Classfullcode,classfullname, 1 AS Level

? From S_class

? WHERE Parentcode = "or Parentcode is null

? UNION All

?---iterate through the CLS based on conditions. Praentcode = My. Classfullcode This is the condition of the traversal.

? SELECT CLS. Classfullcode,cls. Classfullname,level + 1

? From S_class CLS join MyClass my on CLS. Parentcode = My. Classfullcode

)

SELECT Classfullcode,classfullname,level from MyClass

OPTION (maxrecursion 1)--error control the level of recursion

Usage of----pivot

Select [Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]--Here is the third step of pivot (the column of the result set after row-to-column selection) You can use "*" to select all columns, or select only some columns (that is, some days)

--from Week_income--Here is the second step of pivot (prepare the original query result, because pivot is a conversion operation on an original query result set, so query a result set first) here can be a select subquery, but to specify the alias name when the subquery is queried , otherwise syntax error

--pivot

--(

?--SUM (INCOME) for [week] in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])--here is the pivot first step, also the core place, for row to column operation. The aggregate function sum indicates how you want to handle the value of the converted column, whether it is the sum (sum), average (avg), Min,max, and so on. For example, if the Week_income table has two data and its week is "Monday", one of the income is 1000 and the other income is 500, then the sum is used here, and the value of the column "Monday" After row to column is of course 1500. After for [week] in ([Monday],[Tuesday] ...) In for [week] means that the values of the week column are converted to columns, which is "column by value." But the values that need to be converted into columns can be many, and we just want to take a few of them into columns, so how do we take them? is in inside, for example, I just want to see the income of the working day, in inside only write "Monday" to "Friday" (note, in Inside is the original week column value, "column value"). In general, SUM (INCOME) for [week] in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]) The meaning of this sentence is to say: the column [Week] value is "Monday", "Tuesday", " Wednesday "," Thursday "," Friday "," Saturday "," Sunday "are converted into columns respectively, and the values of these columns are taken as the sum of income.

--) tbl--aliases must be written

?

?

Summary of--update Update method

CREATE TABLE Music

(

ID int identity (+),

School varchar (200),

Name varchar (100)

)

?

Insert into music values (' Hubei University ', ' Chenhongye ')

Insert into music values (' Hubei university ', ' Chen Leslie '), (' Hubei University ', ' LV Junniao '), (' Hubei University ', ' Tang Hu Laugh '), (' Hubei University ', ' He ')

Insert into music values (' Wuhan University of Science and Technology ', ' Xu Le '), (' Hubei University ', ' Wang Hong '), (' Hubei University ', ' Chen Lei '), (' Hubei University ', ' Weide ')

?

CREATE TABLE Music3

(

? ID int,

? School varchar (200),

? name varchar (200),

? name2 varchar (200)

)

?

SELECT * FROM Music2

---the first copy statement syntax, select whether you are a multi-table select or a single-table, it does not matter, but to match the previous type is OK

Insert into Music2 (id,school,name) SELECT * FROM music

---the second (a new table is common by default) copy a column to replace name with * To copy the entire table

Select name into MUSIC5 from music

--Custom replication (filter criteria)

SELECT * into MUSIC6 from music where ID > 3

SELECT * from MUSIC5?

?

---SQL delete syntax

DELETE from CB_ADJUSTKHDTL to Cb_adjustkhdtl inner join Cb_costkh T2 on cb_adjustkhdtl.costkhguid= T2. Costkhguid?

?

Summary of atypical T_sql