none
Numerare con Row_Number solo record master RRS feed

  • Domanda

  • Salve,

    ho un problema e non so se l'utilizzo di RowNumber può aiutarmi. ho queste tre tabelle ognuna delle quali è di dettaglio della precedente :

    CREATE TABLE [dbo].[TAB_Master](
    	[ID] [uniqueidentifier] NOT NULL,
    	[Nome] [varchar](50) NOT NULL,
     CONSTRAINT [PK_TAB_Master] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    
    CREATE TABLE [dbo].[Tab_Dett1](
    	[ID] [uniqueidentifier] NOT NULL,
    	[ID_Tab_Master] [uniqueidentifier] NOT NULL,
    	[Dettaglio1] [varchar](50) NOT NULL,
     CONSTRAINT [PK_Tab_Dett1] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    
    CREATE TABLE [dbo].[TAB_DETT1_A](
    	[ID] [uniqueidentifier] NOT NULL,
    	[ID_TAB_DETT1] [uniqueidentifier] NOT NULL,
    	[AltroDettaglio] [varchar](50) NOT NULL
    ) ON [PRIMARY]
    

    Pololandola cosi :

    insert into tab_master(ID,nome) values ('295E2194-E517-41F2-A1A2-7000049C70FC','alberto');
    insert into tab_Dett1(ID,ID_TAB_MASTER,Dettaglio1) values ('11C0A042-373C-4DD8-9885-1272D9E27E7A','295E2194-E517-41F2-A1A2-7000049C70FC','Dettaglio1 Alberto');
    insert into tab_Dett1(ID,ID_TAB_MASTER,Dettaglio1) values ('E7457BC0-61EE-4E30-8341-93D83339D651','295E2194-E517-41F2-A1A2-7000049C70FC','Dettaglio2 Alberto');
    insert into tab_Dett1_A(ID,ID_TAB_Dett1,AltroDettaglio) values ('EF09E59D-C546-4600-88AA-7A369BB4764B','11C0A042-373C-4DD8-9885-1272D9E27E7A','Ulteriore Dettaglio Alberto');
    
    
    insert into tab_master(ID,nome) values ('A23E1B02-1691-4AA2-8A39-47F8085A191E','Carlo');
    insert into tab_Dett1(ID,ID_TAB_MASTER,Dettaglio1) values ('F7539A25-3B17-4CA0-912C-40DE8260F401','A23E1B02-1691-4AA2-8A39-47F8085A191E','Dettaglio1 Carlo');
    insert into tab_Dett1(ID,ID_TAB_MASTER,Dettaglio1) values ('F6E30F66-A67B-4C03-86AE-2C127DCF21FA','A23E1B02-1691-4AA2-8A39-47F8085A191E','Dettaglio2 Carlo');
    insert into tab_Dett1_A(ID,ID_TAB_Dett1,AltroDettaglio) values ('1EA8E117-FEDD-4C13-A40A-7028AE7E1E35','F7539A25-3B17-4CA0-912C-40DE8260F401','Ulteriore Dettaglio Carlo');
    

    e utilizzando Row_Number cosi :

    Select ROW_NUMBER() OVER(order by Tab_Master.Nome
    ) AS RowNumber,
    
     TAB_Master.ID, TAB_Master.Nome, Tab_Dett1.ID AS Expr1, Tab_Dett1.Dettaglio1, TAB_DETT1_A.ID AS Expr2, TAB_DETT1_A.AltroDettaglio
    FROM            TAB_DETT1_A RIGHT OUTER JOIN
                             Tab_Dett1 ON TAB_DETT1_A.ID_TAB_DETT1 = Tab_Dett1.ID RIGHT OUTER JOIN
    

    Ottengo questo :

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    RowNumber	ID					Nome	Expr1					Dettaglio1		Expr2					AltroDettaglio
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1		295E2194-E517-41F2-A1A2-7000049C70FC	alberto	11C0A042-373C-4DD8-9885-1272D9E27E7A	Dettaglio1 Alberto	EF09E59D-C546-4600-88AA-7A369BB4764B	Ulteriore Dettaglio Alberto
    2		295E2194-E517-41F2-A1A2-7000049C70FC	alberto	E7457BC0-61EE-4E30-8341-93D83339D651	Dettaglio2 Alberto	NULL					NULL
    3		A23E1B02-1691-4AA2-8A39-47F8085A191E	Carlo	F6E30F66-A67B-4C03-86AE-2C127DCF21FA	Dettaglio2 Carlo	NULL					NULL
    4		A23E1B02-1691-4AA2-8A39-47F8085A191E	Carlo	F7539A25-3B17-4CA0-912C-40DE8260F401	Dettaglio1 Carlo	1EA8E117-FEDD-4C13-A40A-7028AE7E1E35	Ulteriore Dettaglio Carlo
    

    Quello che vorrei ottenere  invece è

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    RowNumber	ID					Nome	Expr1					Dettaglio1		Expr2					AltroDettaglio
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1		295E2194-E517-41F2-A1A2-7000049C70FC	alberto	11C0A042-373C-4DD8-9885-1272D9E27E7A	Dettaglio1 Alberto	EF09E59D-C546-4600-88AA-7A369BB4764B	Ulteriore Dettaglio Alberto
    1		295E2194-E517-41F2-A1A2-7000049C70FC	alberto	E7457BC0-61EE-4E30-8341-93D83339D651	Dettaglio2 Alberto	NULL					NULL
    2		A23E1B02-1691-4AA2-8A39-47F8085A191E	Carlo	F6E30F66-A67B-4C03-86AE-2C127DCF21FA	Dettaglio2 Carlo	NULL					NULL
    2		A23E1B02-1691-4AA2-8A39-47F8085A191E	Carlo	F7539A25-3B17-4CA0-912C-40DE8260F401	Dettaglio1 Carlo	1EA8E117-FEDD-4C13-A40A-7028AE7E1E35	Ulteriore Dettaglio Carlo
    

    in pratica vorrei un contatore del solo record master, questo per poter paginare utillizzando la join.
    Ho provato con partition by ma non sono riuscito a raggiungere il mio scopo.

    Potreste darmi una mano?

    Grazie

     

     

    venerdì 19 giugno 2015 14:09

Risposte

  • Se usi la versione con la CTE, puoi fare una cosa del tipo:

    with cte as (
    select ID, Nome, ROW_NUMBER() OVER (ORDER BY Nome) AS Num
    from tab_master
    )
    select cte.Num, cte.ID, cte.Nome, d.ID, d.Dettaglio1
    FROM cte
    INNER JOIN tab_Dett1 d ON cte.ID = d.ID_Tab_Master
    WHERE cte.Num BETWEEN 1 AND 10
    ORDER BY Num


    Alberto Dallagiacoma
    My Italian Blog: http://blogs.ugidotnet.org/alby
    Twitter: http://twitter.com/albertodall
    DotDotNet - User Group .NET Emilia Romagna: http://www.dotdotnet.org

    mercoledì 24 giugno 2015 11:44
  • Per completezza, ti suggerisco anche un altro modo per eseguire la tua query che consiste nell'uso di una stored procedure in modo da passare anche l'intervallo di righe da estrarre.

    CREATE PROCEDURE SP_TEST
    (
       @START BIGINT = 0
       , @END BIGINT = 0
    )
    AS
    BEGIN
       SELECT 
          T.*
       FROM 
       (
          SELECT
             DENSE_RANK() OVER(ORDER BY TAB_MASTER.NOME) AS DENSE_RANK_ID
             , TAB_MASTER.ID AS ID_MASTER
             , TAB_MASTER.NOME AS NOME_MASTR
             , TAB_DETT1.ID AS ID_DETT1
             , TAB_DETT1.DETTAGLIO1
             , TAB_DETT1_A.ID AS ID_DETT1_A
             , TAB_DETT1_A.ALTRODETTAGLIO
          FROM
             TAB_MASTER
          LEFT JOIN 
             TAB_DETT1
          ON
             TAB_DETT1.ID_TAB_MASTER = TAB_MASTER.ID
          LEFT JOIN
             TAB_DETT1_A
          ON
             TAB_DETT1_A.ID_TAB_DETT1 = TAB_DETT1.ID
       ) AS T
       WHERE
          T.DENSE_RANK_ID BETWEEN @START AND @END
       ORDER BY
          T.DENSE_RANK_ID
    END

    e richiamarla, ad esempio, così:

    EXECUTE SP_TEST 1, 4

    Se puoi, facci sapere se i piani di esecuzione di tutti questi esempi ti suggeriscono qualcosa.


    vibi6


    mercoledì 24 giugno 2015 18:07

Tutte le risposte

  • Ciao G Luca,

    puoi provare con:

    with cte as (
    select ID, Nome, ROW_NUMBER() OVER (order by Nome) AS Num
    from tab_master
    )
    select cte.Num, cte.ID, cte.Nome, d.ID, d.Dettaglio1
    FROM cte
    INNER JOIN tab_Dett1 d ON cte.ID = d.ID_Tab_Master
    ORDER BY Num
    HTH,


    Alberto Dallagiacoma
    My Italian Blog: http://blogs.ugidotnet.org/alby
    Twitter: http://twitter.com/albertodall
    DotDotNet - User Group .NET Emilia Romagna: http://www.dotdotnet.org

    venerdì 19 giugno 2015 16:04
  • Quello che vorrei ottenere  invece è

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    RowNumber	ID					Nome	Expr1					Dettaglio1		Expr2					AltroDettaglio
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1		295E2194-E517-41F2-A1A2-7000049C70FC	alberto	11C0A042-373C-4DD8-9885-1272D9E27E7A	Dettaglio1 Alberto	EF09E59D-C546-4600-88AA-7A369BB4764B	Ulteriore Dettaglio Alberto
    1		295E2194-E517-41F2-A1A2-7000049C70FC	alberto	E7457BC0-61EE-4E30-8341-93D83339D651	Dettaglio2 Alberto	NULL					NULL
    2		A23E1B02-1691-4AA2-8A39-47F8085A191E	Carlo	F6E30F66-A67B-4C03-86AE-2C127DCF21FA	Dettaglio2 Carlo	NULL					NULL
    2		A23E1B02-1691-4AA2-8A39-47F8085A191E	Carlo	F7539A25-3B17-4CA0-912C-40DE8260F401	Dettaglio1 Carlo	1EA8E117-FEDD-4C13-A40A-7028AE7E1E35	Ulteriore Dettaglio Carlo

    in pratica vorrei un contatore del solo record master, questo per poter paginare utillizzando la join.
    Ho provato con partition by ma non sono riuscito a raggiungere il mio scopo.

    Potreste darmi una mano?

    Grazie

    Ti serve DENSE_RANK().

    A esempio:

    SELECT
    	DENSE_RANK() OVER(order by Tab_Master.Nome) AS RowNumber
    	, * 
    FROM
    	tab_master
    INNER JOIN 
    	Tab_Dett1
    ON
    	Tab_Dett1.ID_Tab_Master = tab_master.ID

    produce il risultato:

    RowNumber            ID                                   Nome                                               ID                                   ID_Tab_Master                        Dettaglio1
    -------------------- ------------------------------------ -------------------------------------------------- ------------------------------------ ------------------------------------ --------------------------------------------------
    1                    295E2194-E517-41F2-A1A2-7000049C70FC alberto                                            11C0A042-373C-4DD8-9885-1272D9E27E7A 295E2194-E517-41F2-A1A2-7000049C70FC Dettaglio1 Alberto
    1                    295E2194-E517-41F2-A1A2-7000049C70FC alberto                                            E7457BC0-61EE-4E30-8341-93D83339D651 295E2194-E517-41F2-A1A2-7000049C70FC Dettaglio2 Alberto
    2                    A23E1B02-1691-4AA2-8A39-47F8085A191E Carlo                                              F6E30F66-A67B-4C03-86AE-2C127DCF21FA A23E1B02-1691-4AA2-8A39-47F8085A191E Dettaglio2 Carlo
    2                    A23E1B02-1691-4AA2-8A39-47F8085A191E Carlo                                              F7539A25-3B17-4CA0-912C-40DE8260F401 A23E1B02-1691-4AA2-8A39-47F8085A191E Dettaglio1 Carlo
    
    (Righe interessate: 4)
    Per approfondimenti: Dense_Rank


    vibi6

    sabato 20 giugno 2015 21:49
  • Ciao,

    è esattamente quello che mi serviva, grazie!

    Adesso, per aggiungere una where su rownumber, dovrei usare la query come subquery cosi :

    select * from (
    SELECT    
    DENSE_RANK() OVER(order by Tab_Master.ID) AS RowNumber,
    
        TAB_Master.ID, TAB_Master.Nome, Tab_Dett1.ID AS Expr1, Tab_Dett1.Dettaglio1, TAB_DETT1_A.ID AS Expr2, TAB_DETT1_A.AltroDettaglio
    FROM            TAB_Master LEFT OUTER JOIN
                             Tab_Dett1 ON TAB_Master.ID = Tab_Dett1.ID_Tab_Master   LEFT OUTER JOIN
                             TAB_DETT1_A ON Tab_Dett1.ID = TAB_DETT1_A.ID_TAB_DETT1
    				) as dd
    
    				where dd.RowNumber between 1 and 10		

    però sull'Execution Plan, usa un filter per scartare quindi genera prima tutto e poi seleziona.

    Sai se esiste un modo per evitare di costruire prima tutta la join?

    Ciao e grazie ancora

    martedì 23 giugno 2015 14:46
  • Ciao,

    Cosi ottengo esattamente quello che mi serve, grazie. Tra le due soluzioni(questa e quella sotto) quale è la più efficiente

    Ciao e grazie

    martedì 23 giugno 2015 14:51
  • Che io sappia, no.

    Fino a che la Where non termina DENSE_RANK (ma anche tutte le altre funzioni di rango) non esiste ancora.

    Quindi è una necessità utilizzare la query come subquery se si vuole filtrare in base al row number.


    vibi6

    martedì 23 giugno 2015 16:14
  • Se usi la versione con la CTE, puoi fare una cosa del tipo:

    with cte as (
    select ID, Nome, ROW_NUMBER() OVER (ORDER BY Nome) AS Num
    from tab_master
    )
    select cte.Num, cte.ID, cte.Nome, d.ID, d.Dettaglio1
    FROM cte
    INNER JOIN tab_Dett1 d ON cte.ID = d.ID_Tab_Master
    WHERE cte.Num BETWEEN 1 AND 10
    ORDER BY Num


    Alberto Dallagiacoma
    My Italian Blog: http://blogs.ugidotnet.org/alby
    Twitter: http://twitter.com/albertodall
    DotDotNet - User Group .NET Emilia Romagna: http://www.dotdotnet.org

    mercoledì 24 giugno 2015 11:44
  • Per completezza, ti suggerisco anche un altro modo per eseguire la tua query che consiste nell'uso di una stored procedure in modo da passare anche l'intervallo di righe da estrarre.

    CREATE PROCEDURE SP_TEST
    (
       @START BIGINT = 0
       , @END BIGINT = 0
    )
    AS
    BEGIN
       SELECT 
          T.*
       FROM 
       (
          SELECT
             DENSE_RANK() OVER(ORDER BY TAB_MASTER.NOME) AS DENSE_RANK_ID
             , TAB_MASTER.ID AS ID_MASTER
             , TAB_MASTER.NOME AS NOME_MASTR
             , TAB_DETT1.ID AS ID_DETT1
             , TAB_DETT1.DETTAGLIO1
             , TAB_DETT1_A.ID AS ID_DETT1_A
             , TAB_DETT1_A.ALTRODETTAGLIO
          FROM
             TAB_MASTER
          LEFT JOIN 
             TAB_DETT1
          ON
             TAB_DETT1.ID_TAB_MASTER = TAB_MASTER.ID
          LEFT JOIN
             TAB_DETT1_A
          ON
             TAB_DETT1_A.ID_TAB_DETT1 = TAB_DETT1.ID
       ) AS T
       WHERE
          T.DENSE_RANK_ID BETWEEN @START AND @END
       ORDER BY
          T.DENSE_RANK_ID
    END

    e richiamarla, ad esempio, così:

    EXECUTE SP_TEST 1, 4

    Se puoi, facci sapere se i piani di esecuzione di tutti questi esempi ti suggeriscono qualcosa.


    vibi6


    mercoledì 24 giugno 2015 18:07