SQL Line train, column career and facet

Description: This example is a running environment with SQL Server 2005.

Ready to work: Create a database named db (Create DB).

One, T-SQL row to column

1. Create the following table

CREATE TABLE [Scores] (

[ID] INT identity (+),--Self-increment logo

[Stuno] INT,--School number

[Subject] NVARCHAR (30),--subjects

[Score] FLOAT--Results

)

GO

INSERT into [Scores]

SELECT 100, ' language ', UNION

SELECT 100, ' math ', UNION

SELECT 100, ' English ', UNION

SELECT 100, ' creature ', UNION

SELECT 101, ' language ', UNION

SELECT 101, ' math ', UNION

SELECT 101, ' English ', UNION

SELECT 101, ' creature ', 85

CREATE TABLE [Student] (

[ID] INT identity (100,1),--Self-increment logo, study number

[Stuname] NVARCHAR (30),--Name

[Sex] NVARCHAR (30),--gender

[Age] CHAR (2)--age

)

GO

INSERT into [Student]

SELECT ' Zhang San ', ' Male ', UNION

SELECT ' John Doe ', ' female ', 75

Two tables of data such as:

2. Through case ... When statement and GROUP by ... Aggregate functions to implement row to column

SELECT

Stuno as ' study number ',

MAX (case Subject "language" then score ELSE 0 END) as ' language ',

MAX (case Subject when ' math ' then score ELSE 0 END) as ' math ',

MAX (case Subject when ' English ' then score ELSE 0 END) as ' English ',

MAX (case Subject when ' creature ' then score ELSE 0 END) as ' creature ',

SUM (score) as ' total score ',

AVG (score) as ' average score '

FROM dbo. [Scores]

GROUP by Stuno

ORDER by Stuno ASC

Results such as:

3. Through JOIN ... On implements two table joins, showing the student's name

SELECT

MAX (Stuno) as ' study number ',

Stuname as ' name ',

MAX (case Subject "language" then score ELSE 0 END) as ' language ',

MAX (case Subject when ' math ' then score ELSE 0 END) as ' math ',

MAX (case Subject when ' English ' then score ELSE 0 END) as ' English ',

MAX (case Subject when ' creature ' then score ELSE 0 END) as ' creature ',

SUM (score) as ' total score ',

AVG (score) as ' average score '

FROM dbo. [Scores] A join [Student] B on (a.stuno=b.id)

GROUP by Stuname

ORDER by Stuname ASC

Results such as:

4. Implement row to column through PIVOT

SELECT

Stuno as ' study number ',

Stuname as ' name ',

AVG (language) as ' language ',

AVG (mathematics) as ' mathematics ',

AVG (English) as ' English ',

AVG (bio) as ' creature '

from [Scores]

PIVOT (

AVG (score) for Subject in

(Chinese, maths, English, biology)

) as Newscores

JOIN [Student] On (newscores.stuno=student.id)

GROUP by Newscores.stuno,stuname

ORDER by Stuname ASC

Results such as:

Second, T-SQL column career

1. Create a data table and insert 4 data

CREATE TABLE [Studentscores] (

[ID] INT identity (+),--Self-increment logo

[Stuno] INT,--School number

[Chinese] NVARCHAR (30),--Chinese

[Mathematics] NVARCHAR (30),--Mathematics

[中文版] NVARCHAR (30),--English

[Biology] NVARCHAR (30)--Biological

)

GO

INSERT into [studentscores]

SELECT--------

SELECT 101,----

SELECT 102,------

SELECT 103, 60, 70, 80, 85

Data such as:

2. Through UNION all ... MAX implements a column change career

SELECT Stuno, ' Chinese ' as Subject,

MAX (Chinese) as ' score '

from [Studentscores]

GROUP by [Stuno]

UNION All

SELECT Stuno, ' mathematics ' as Subject,

MAX (mathematics) as ' score '

from [Studentscores]

GROUP by [Stuno]

UNION All

SELECT Stuno, ' 中文版 ' as Subject,

MAX (中文版) as ' score '

from [Studentscores]

GROUP by [Stuno]

UNION All

SELECT Stuno, ' biology ' as Subject,

MAX (biology) as ' score '

from [Studentscores]

GROUP by [Stuno]

Results such as:

3, using UNPIVOT to achieve a career

SELECT Stuno, Subject, score

from [Studentscores]

UNPIVOT

(

Score for Subject in

([Chinese], [mathematics], [中文版], [biology])

) as Newstudentscores

EXEC sp_dbcmptlevel Db_16, 90;

Third, T-SQL paging

1. Create a database and insert 40,000 data

CREATE TABLE [Pagin] (

[ID] INT identity (+),--Self-increment logo

[NUMBER] INT,---number

[Type] NVARCHAR (30),--type

[Count] INT--Quantity

)

GO

DECLARE @i int

Set @i = 0

while (@i<10000)

Begin

INSERT into [pagin] SELECT [email protected], ' Class A ', [email protected]%5

INSERT into [pagin] SELECT [email protected], ' Class B ', [email protected]%10

INSERT into [pagin] SELECT [email protected], ' Class C ', [email protected]%8

INSERT into [pagin] SELECT [email protected], ' Class d ', [email protected]%3

Set @i = @i + 1

End

2, through the TOP realization of paging

Scenario One: Two-time TOP implementation, prototype as follows

SELECT * FROM (

SELECT TOP 5 * FROM (

SELECT TOP * FROM [Pagin] WHERE id>0 ORDER by ID ASC

) as TEMPTABLE1 ORDER by ID DESC

) as TEMPTABLE2 ORDER by ID ASC

SELECT TOP 5 * FROM (

SELECT TOP * FROM [Pagin] WHERE id>0 ORDER by ID ASC

) as TEMPTABLE1 ORDER by ID DESC

Description: The first top represents the page capacity, and the second top represents the page capacity * The current page number.

Ills: 1, forced sorting, otherwise can not be paged, although the current basically query table to sort.

2. The sort field cannot have a null value or NULL, otherwise the paging result does not match the actual situation.

3, multiple order by speed will be fast, waiting for my further big data volume test.

Scenario Two: Two-time TOP based not in implementation, the prototype is as follows

Select Top 5 * from [Pagin]

where ID not in (select top IDs from [Pagin] ORDER by ID)

ORDER BY ID

Description: The first top represents the page capacity, and the second top represents the page capacity * The current page number.

Ills: 1, forced sequencing.

2, the row sequence must be a unique column, otherwise the paging situation does not match the actual.

3, use not in, slow speed.

Scenario three: two times TOP based on Max or MIN implementation, prototype as follows

Select Top 5 * from [Pagin]

where ID > (select MAX (p.id) from (select Top in [Pagin] ORDER by ID) as P)

ORDER BY ID

Description: The first top represents the page capacity, and the second top represents the page capacity * The current page number.

Ills: 1, forced sequencing.

2, the row sequence must be a unique column, otherwise the paging situation does not match the actual.

Final summary: In SQL Server paging, the second third scenario is basically eliminated, because now basically what table is based on add time to sort, so those two scenarios are not used, really lose the author also dare to publish out, only the first scheme still slightly can use, but still want to complicate the SQL statement, Inconvenient, to be used for all tables a bit of difficulty, like Oracle is very convenient, based on rownum, incoming a SQL query statement, this query statement love how to write, anyway, to ensure that it gets a result set on the line, not like SQL Server is required to be the only health and requirements must be ordered, Prodded a result set, it's not too slow to blame.

The simple stored procedure for scenario one is as follows:

CREATE PROCEDURE Proc_page

@pageIndex INT = 0,--page index

@pageSize INT = 10,--page size

@recordCount INT = 0 OUTPUT,--Returns the total number of records

@pageCount INT = 0 OUTPUT--Returns the total number of pages

As

DECLARE @sql NVARCHAR (1300)--Main SQL statement

--Get the total number of records--

BEGIN

DECLARE @recordTotal INT

SET @sql = N ' SELECT @recordTotal =count (ID) from [Pagin] WHERE id>0 '

EXEC sp_executesql @sql, N ' @recordTotal INT output ', @recordTotal output [email protected] = @recordCount output

SET @recordCount = @recordTotal

END

--Calculate the total number of pages--

if (@recordCount% @pageSize =0)--If the total number of records divided by the page size is zero

SET @pageCount = @recordCount/@pageSize

else--if the total number of records divided by the page size is not zero

SET @pageCount = @recordCount/@pageSize + 1

--performing paging queries based on page index--

if (@pageIndex <=1 or @pageIndex > @pageCount)--if it is the first page, or if the page does not exist, the default is also indexed to 1, which is the first page

BEGIN

SET @pageIndex = 1

SET @sql = ' SELECT TOP ' +str (@pageSize) + ' * from [Pagin] WHERE id>0 ORDER by ID ASC '

EXEC sp_executesql @sql

END

else--if it is not the first page, other pages

BEGIN

SET @sql = ' SELECT * FROM (select top ' +str (@pageSize) + ' * FROM (select top ' +str (@pageSize * @pageIndex) + ' * from [Pagin] W Here id>0 ORDER BY ID ASC) as TB1 ORDER by ID DESC) as TB2 ORDER by ID ASC '

EXEC sp_executesql @sql

END

Print @sql--Printing SQL statements

GO

--Test

DECLARE @recordCount int, @pageCount int

EXEC proc_page 0,5, @recordCount output, @pageCount output

PRINT @recordCount

PRINT @pageCount

Note: This stored procedure is extended, and can be changed to a universal paging stored procedure, just add some parameters, and then change it.

SQL Line train, column career and facet