Subquery application and function 20140909

Tag: Use Ar data 2014 sp c time new ef

I. Application of subquery

1. Paging Query
1) 5 data entries are displayed on each page; 2) Top 5 pages (3-1) -- 3rd

Select top 5 * from car where code not in -- Skip ...... Obtain the first few data records.
(
Select top 10 code from car -- number of intermediate values skipped
)

-- Query data on a specified page
2. Retrieve the total number of pages
Select ceiling (count (*)/5.0) from car

PS: Count (*) is an integer type. to calculate the total number of pages, the return value must be of the decimal type. Therefore, 5 must be written to 5.0. After obtaining the decimal type, the ceiling number is the total number of pages.

Ii. Functions

1. Power Function

SQRT -- square root

2. Approximate Functions

1) ceiling

Ceiling (3.2) -- 4

2) Floor

Floor (3.2) -- 3

3) round (the round function T-SQL in the index)

Print round (345.987, 0) -- 346.000

0 indicates the number of digits after the decimal point.

Print round (345.876,-1) -- 350.000

-1 refers to rounding to ten

Print round (345.876,-2) -- 300.000

-2 refers to rounding to hundreds of places

3. symbol Functions

Abs -- absolute value can be used to subtract values of two columns, regardless of whether the column value is positive or negative.

4. Other functions

Rand -- random number between 0 and 1 (with time as the seed), never equal to 1.

5. character conversion functions

1) lower -- convert all to lower case.

2) upper -- convert all to uppercase.

3) str -- convert a value to a string

Select 'average score: '+ STR (AVG (degree) from score -- average score: 80

Or use cast ...... As data type:

Select 'average score: '+ Cast (AVG (degree) as varchar (30) from score-average score: 80.250000

6. Remove space functions

1) ltrim -- compress the space on the left

2) rtrim -- compress spaces on the right

Select 'average score: '+ rtrim (ltrim (STR (AVG (degree) from score -- both compress spaces on the left and compress spaces on the right, the two functions are always used at the same time.

7. Obtain the substring Function

1) left -- Extract from the left

2) Right -- capture from the right

3) substring -- truncates n substring entries at any position.

Select left (title, 10) + 'xxx' + right (title, 2) from news -- xxx can be replaced with the desired character or value.

Select '...' + substring (title, 5, 4) + '...' from news -- 4 words starting with 5th words in the title column.

8. String operation functions

1) charindex -- start position of the substring

2) reverse -- reverse

3) Replace -- replace

Select * from news where charindex ('China', title)> 0 -- charindex (replace value, column name)

 

Subquery application and function 20140909