hobby:development:sql:select:query_0002 [MLC Wiki]

Questa pagina è in sola lettura. Puoi visualizzare il sorgente, ma non puoi modificarlo. Contatta l'amministratore se pensi che ci sia un errore.
{{:wiki:icons:t-sql.png?nolink&90 |}} <fs xx-large>Enumerare i risultati di una query</fs>
[[hobby:development:sql|{{ :wiki:icons:left_arrow.png?48|Vai alla mappa}}]]\\
//<fc #5F5F5F><fs large>(Come enumerare i record ottenuti da una select)</fs></fc>//
\\
\\

Serviamoci della seguente tabella di esempio:
<sxh sql>
USE DbTemp
 
IF OBJECT_ID(N'dbo.tblData', 'U') IS NOT NULL
    DROP TABLE dbo.tblData
 
-- Create table [dbo].[tblData]
PRINT (N'Create table [dbo].[tblData]')
 
CREATE TABLE dbo.tblData (
    IdData INT NOT NULL DEFAULT 0
   ,Code VARCHAR(50) NOT NULL DEFAULT ('')
   ,Description VARCHAR(150) NOT NULL DEFAULT ('')
   ,Data DATETIME NOT NULL DEFAULT (GETDATE())
) 
ON [PRIMARY]
</sxh>

Popoliamo la tabella con alcuni dati di esempio:

<sxh sql>
USE DbTemp

DECLARE @Row INT = 0;
DECLARE @Code NVARCHAR(7);

DELETE FROM dbo.tblData

WHILE @Row < 200
	BEGIN
		SET @Row = @Row + 1;
		SET @Code = 'CDC' + RIGHT('0000' + CONVERT(NVARCHAR, @Row * 10),4)
		INSERT INTO dbo.tblData (IdData, Code, Description) VALUES
		(
			 @Row * 10
			,@Code
			,'DESCRIPTION ' + @Code
		)
	END;
</sxh>

Eseguiamo una ''SELECT'' aggiungendo una colonna valorizzata con il numero di riga

<sxh sql>
SELECT
     ROW_NUMBER() OVER (ORDER BY IdData ASC) Row
    ,*
FROM dbo.tblData
</sxh>

Eseguiamo una ''SELECT'' //"paginando"// i risultati con 50 righe

<sxh sql>
DECLARE @From INT = 50;
DECLARE @To INT = 100;
WITH PageTable AS
(
	SELECT
	     ROW_NUMBER() OVER (ORDER BY IdData ASC) Row
	    ,*
	FROM dbo.tblData
)
SELECT * FROM PageTable
WHERE Row BETWEEN @From AND @To;
</sxh>

Il risultato sarà un recordset di 50 record dal numero 50 al 100:

^  Row  ^  IdData  ^ Code     ^ Description          ^ Data                     ^
|  50   |  500     | CDC0500  | DESCRIPTION CDC0500  | 07/01/2021 10:42:30.153  |
|  51   |  510     | CDC0510  | DESCRIPTION CDC0510  | 07/01/2021 10:42:30.153  |
|  52   |  520     | CDC0520  | DESCRIPTION CDC0520  | 07/01/2021 10:42:30.153  |
|  53   |  530     | CDC0530  | DESCRIPTION CDC0530  | 07/01/2021 10:42:30.153  |
|  54   |  540     | CDC0540  | DESCRIPTION CDC0540  | 07/01/2021 10:42:30.153  |
|  55   |  550     | CDC0550  | DESCRIPTION CDC0550  | 07/01/2021 10:42:30.153  |
|  56   |  560     | CDC0560  | DESCRIPTION CDC0560  | 07/01/2021 10:42:30.153  |
|  57   |  570     | CDC0570  | DESCRIPTION CDC0570  | 07/01/2021 10:42:30.153  |
|  58   |  580     | CDC0580  | DESCRIPTION CDC0580  | 07/01/2021 10:42:30.153  |
|  59   |  590     | CDC0590  | DESCRIPTION CDC0590  | 07/01/2021 10:42:30.153  |
|  60   |  600     | CDC0600  | DESCRIPTION CDC0600  | 07/01/2021 10:42:30.153  |
|  61   |  610     | CDC0610  | DESCRIPTION CDC0610  | 07/01/2021 10:42:30.170  |
|  62   |  620     | CDC0620  | DESCRIPTION CDC0620  | 07/01/2021 10:42:30.170  |
|  63   |  630     | CDC0630  | DESCRIPTION CDC0630  | 07/01/2021 10:42:30.170  |
|  64   |  640     | CDC0640  | DESCRIPTION CDC0640  | 07/01/2021 10:42:30.170  |
|  65   |  650     | CDC0650  | DESCRIPTION CDC0650  | 07/01/2021 10:42:30.170  |
|  66   |  660     | CDC0660  | DESCRIPTION CDC0660  | 07/01/2021 10:42:30.170  |
|  67   |  670     | CDC0670  | DESCRIPTION CDC0670  | 07/01/2021 10:42:30.170  |
|  68   |  680     | CDC0680  | DESCRIPTION CDC0680  | 07/01/2021 10:42:30.170  |
|  69   |  690     | CDC0690  | DESCRIPTION CDC0690  | 07/01/2021 10:42:30.170  |
|  70   |  700     | CDC0700  | DESCRIPTION CDC0700  | 07/01/2021 10:42:30.170  |
|  71   |  710     | CDC0710  | DESCRIPTION CDC0710  | 07/01/2021 10:42:30.170  |
|  72   |  720     | CDC0720  | DESCRIPTION CDC0720  | 07/01/2021 10:42:30.170  |
|  73   |  730     | CDC0730  | DESCRIPTION CDC0730  | 07/01/2021 10:42:30.170  |
|  74   |  740     | CDC0740  | DESCRIPTION CDC0740  | 07/01/2021 10:42:30.170  |
|  75   |  750     | CDC0750  | DESCRIPTION CDC0750  | 07/01/2021 10:42:30.170  |
|  76   |  760     | CDC0760  | DESCRIPTION CDC0760  | 07/01/2021 10:42:30.170  |
|  77   |  770     | CDC0770  | DESCRIPTION CDC0770  | 07/01/2021 10:42:30.170  |
|  78   |  780     | CDC0780  | DESCRIPTION CDC0780  | 07/01/2021 10:42:30.170  |
|  79   |  790     | CDC0790  | DESCRIPTION CDC0790  | 07/01/2021 10:42:30.170  |
|  80   |  800     | CDC0800  | DESCRIPTION CDC0800  | 07/01/2021 10:42:30.170  |
|  81   |  810     | CDC0810  | DESCRIPTION CDC0810  | 07/01/2021 10:42:30.170  |
|  82   |  820     | CDC0820  | DESCRIPTION CDC0820  | 07/01/2021 10:42:30.170  |
|  83   |  830     | CDC0830  | DESCRIPTION CDC0830  | 07/01/2021 10:42:30.170  |
|  84   |  840     | CDC0840  | DESCRIPTION CDC0840  | 07/01/2021 10:42:30.170  |
|  85   |  850     | CDC0850  | DESCRIPTION CDC0850  | 07/01/2021 10:42:30.187  |
|  86   |  860     | CDC0860  | DESCRIPTION CDC0860  | 07/01/2021 10:42:30.187  |
|  87   |  870     | CDC0870  | DESCRIPTION CDC0870  | 07/01/2021 10:42:30.187  |
|  88   |  880     | CDC0880  | DESCRIPTION CDC0880  | 07/01/2021 10:42:30.187  |
|  89   |  890     | CDC0890  | DESCRIPTION CDC0890  | 07/01/2021 10:42:30.187  |
|  90   |  900     | CDC0900  | DESCRIPTION CDC0900  | 07/01/2021 10:42:30.187  |
|  91   |  910     | CDC0910  | DESCRIPTION CDC0910  | 07/01/2021 10:42:30.187  |
|  92   |  920     | CDC0920  | DESCRIPTION CDC0920  | 07/01/2021 10:42:30.187  |
|  93   |  930     | CDC0930  | DESCRIPTION CDC0930  | 07/01/2021 10:42:30.187  |
|  94   |  940     | CDC0940  | DESCRIPTION CDC0940  | 07/01/2021 10:42:30.187  |
|  95   |  950     | CDC0950  | DESCRIPTION CDC0950  | 07/01/2021 10:42:30.187  |
|  96   |  960     | CDC0960  | DESCRIPTION CDC0960  | 07/01/2021 10:42:30.187  |
|  97   |  970     | CDC0970  | DESCRIPTION CDC0970  | 07/01/2021 10:42:30.187  |
|  98   |  980     | CDC0980  | DESCRIPTION CDC0980  | 07/01/2021 10:42:30.187  |
|  99   |  990     | CDC0990  | DESCRIPTION CDC0990  | 07/01/2021 10:42:30.187  |
|  100  |  1000    | CDC1000  | DESCRIPTION CDC1000  | 07/01/2021 10:42:30.187  |