MSSQL:Comandi di Query
From Aino Wiki
Contents
SELEZIONE
Ci sono quattro tipi di SQL Server joins:
Join | Alias |
---|---|
INNER JOIN | JOIN |
LEFT OUTER JOIN | LEFT JOIN |
RIGHT OUTER JOIN | RIGHT JOIN |
FULL OUTER JOIN | FULL JOIN |
INNER JOIN
Quando si selezionano tutte le righe che rispettano l'uguaglianza della relazione tra due tabelle e non si considerano i valori NULL.
LEFT JOIN
Quando si selezionano tutte le righe che rispettano l'uguaglianza della relazione tra due tabelle e in aggiunta si selezionano i record che hanno un valore non NULL nel campo di relazione della prima tabella (di sinistra) e NULL nella seconda.
RIGHT JOIN
Quando si selezionano tutte le righe che rispettano l'uguaglianza della relazione tra due tabelle e in aggiunta si selezionano i record che hanno un valore non NULL nel campo di relazione della seconda tabella (di destra) e NULL nella prima.
CROSS JOIN
Indipendentemente da una relazione di uguaglianza su un campo si selezionano tutte le combinazioni di record tra le due tabelle creando un prodotto cartesiano.
Vedere CROSS APPLY
.
Esempio di come combinare le ore del giorno con una tipologia:
SELECT [hour], [DonationType] FROM @_TB_Hours CROSS JOIN @_TB_DonationTypes ORDER BY [hour], [DonationType]
Per provare usare il seguente script:
DECLARE @_TB_Hours TABLE ( [hour] char(2) ); INSERT INTO @_TB_Hours SELECT '00' UNION SELECT '01' UNION SELECT '02' UNION SELECT '03' UNION SELECT '04' UNION SELECT '05' UNION SELECT '06' UNION SELECT '07' UNION SELECT '08' UNION SELECT '09' UNION SELECT '10' UNION SELECT '11' UNION SELECT '01' UNION SELECT '12' UNION SELECT '13' UNION SELECT '14' UNION SELECT '15' UNION SELECT '16' UNION SELECT '17' UNION SELECT '18' UNION SELECT '19' UNION SELECT '20' UNION SELECT '21' UNION SELECT '22' UNION SELECT '23'; ---------------------------------------------------------------------------------- DECLARE @_TB_DonationTypes TABLE ( [DonationType] varchar(10) ); INSERT INTO @_TB_DonationTypes SELECT 'don' UNION SELECT 'recur'; ----------------------------------------------------------------------------------- SELECT [hour], [DonationType] FROM @_TB_Hours CROSS JOIN @_TB_DonationTypes ORDER BY [hour], [DonationType]
Che produce in output:
Si possono aggiungere tutte le CROSS JOIN che si vuole per avere altre combinazioni
SELECT [AgentCode], [hour], [DonationType] FROM @_TB_AgentCodes CROSS JOIN @_TB_Hours CROSS JOIN @_TB_DonationTypes ORDER BY [AgentCode], [hour], [DonationType]
Selezione con UNION
Serve ad unire due subset di dati omogenei, es per acquisire dati da due tabelle che hanno la stessa struttura.
Ci sono due UNION, una per cui il risultato finale non può avere duplicati (pensa allo stesso record che è contemporaneamente sulle due tabelle della union) ed un altra usando la clausola ALL che invece somma i duplicati.
SELECT ColonnaX FROM table1 UNION SELECT ColonnaY FROM table2 ORDER BY ColonnaComune;
ATTENZIONE ClonnaX e ColonnaY DEVONO essere dello stesso tipo!
Esempio con la UNION ALL qiondi con i duplicati:
SELECT ColonnaX FROM table1 UNION ALL SELECT ColonnaY FROM table2 ORDER BY ColonnaComune;
SELEZIONE AGGIORNAMENTO
Cancellazione
TRUNCATE TABLE
DELETE - SELECT
Esempio realistico, dopo aver cancellato i dettagli ordine cancellerò le testate
DELETE PO_B_PURCHASE_ORDER_HEADER FROM PO_B_PURCHASE_ORDER_HEADER AS POH WHERE NOT EXISTS (SELECT * FROM PO_B_PURCHASE_ORDER_DETAIL AS POD WHERE POD.COMPANY_CODE = POH.COMPANY_CODE AND POD.DIVISION_CODE = POH.DIVISION_CODE AND POD.SHOP_CODE = POH.SHOP_CODE AND POD.ORDER_DATE = POH.ORDER_DATE AND POD.ORDER_NUMBER = POH.ORDER_NUMBER
INSERT - SELECT
INSERT INTO [AlertMailNotifications] ([AlertId] ,[MailRecipientId] ,[SendingState] ,[CreateDateUTC]) SELECT TOP 1 A.[Id] AS AlertId ,MR_AG.MailRecipientId ,{0} ,GETUTCDATE() FROM [Alerts] AS A INNER JOIN [AlerterThresholds] AS AT ON A.[ThresholdId] = AT.Id INNER JOIN [AlertGroups] AS AG ON AT.[AlertGroupId] = AG.Id AND AG.[Enabled] = 1 INNER JOIN [MailRecipients_AlertGroups] AS MR_AG ON AG.[Id] = MR_AG.AlertGroupId WHERE A.[TriggerDateUTC] BETWEEN @Date_TO AND @Date_FROM AND A.[DispatchDateUTC] IS NULL AND A.[DispatchResult] = {1}
DECLARE @USER AS NVARCHAR(50) = 'SPX_TEST_PINO',@UTCDATE AS DATE = GETUTCDATE(), @COMPANY_CODE AS VARCHAR(3) = '071', @DIVISION_CODE AS VARCHAR(3) = '001' PRINT 'Inserting missed "NEW APPOINTMENT" navigation control in UI_S_CONTROL' INSERT INTO [dbo].[UI_S_CONTROL] ([COMPANY_CODE] ,[DIVISION_CODE] ,[MODULE_ID] ,[CONTROL_ID] ,[CONTROLGROUP_CODE] ,[TOPROFILE] ,[DT_INSERT] ,[USERINSERT] ,[DT_UPDATE] ,[USERUPDATE] ,[ROWGUID]) SELECT C.COMPANY_CODE ,@DIVISION_CODE ,'NAVIGATION' ,'LEV2_MYAPPOINTMENT' ,'NAV' ,'Y' ,@UTCDATE ,@USER ,@UTCDATE ,@USER ,NEWID() FROM [dbo].[SY_COMPANY] AS C WHERE C.COUNTRY_CODE = 'NZ' -- Condition needed to exclude records altready inserted AND NOT EXISTS (SELECT CT.[ROWGUID] FROM [dbo].[UI_S_CONTROL] AS CT WHERE 1=1 AND CT.COMPANY_CODE = C.COMPANY_CODE AND CT.DIVISION_CODE = @DIVISION_CODE AND CT.MODULE_ID = 'NAVIGATION' AND CT.CONTROL_ID = 'LEV2_MYAPPOINTMENT' AND CT.CONTROLGROUP_CODE = 'NAV'
)
DECLARE @HistoryRangeMinutes AS INT = -240, -- 3 giorni (3x24x60)=4320@Date_FROM AS DatetimeOffset(7) = GETUTCDATE(), @Date_TO AS DatetimeOffset(7) SET @Date_TO = DATEADD(MINUTE, @HistoryRangeMinutes, @Date_FROM) INSERT INTO [AlertMailNotifications]
([AlertId] ,[MailRecipientId],[SendingState]
,[CreateDateUTC])SELECT TOP 1 A.[Id] AS AlertId ,MR_AG.MailRecipientId ,{0} ,GETUTCDATE() FROM [Alerts] AS A INNER JOIN [AlerterThresholds] AS AT ON A.[ThresholdId] = AT.Id INNER JOIN [AlertGroups] AS AG ON AT.[AlertGroupId] = AG.Id AND AG.[Enabled] = 1 INNER JOIN [MailRecipients_AlertGroups] AS MR_AG ON AG.[Id] = MR_AG.AlertGroupId WHERE A.[TriggerDateUTC] BETWEEN @Date_TO AND @Date_FROM AND A.[DispatchDateUTC] IS NULL
AND A.[DispatchResult] = {1}
COPY Creazione dinamica ed inserimento
Con la seguente si ha il risultato della INSERT, Select ma in più si crea dinamicamente la tabella di inserimento.
In pratica si fa la copia dei dati di una tabella copiandoli in un'altra:
SELECT * --Si può anche selezionare le colonne INTO TabellaDaCreare FROM TabellaDaCuiCopiare WHERE 1=1 --Consente di fare una copia identica! -- Se invece si vuole solo copiare la struttura: SELECT * INTO TabellaDaCreare FROM TabellaDaCuiCopiare WHERE 1=2 --Così crea SOLO struttura'
UPDATE - SELECT
UPDATE Table SET Table.col1 = other_table.col1, Table.col2 = other_table.col2 FROM Table INNER JOIN other_table ON Table.id = other_table.id
ES Con gli alias:
UPDATE CB SET CB.[severity] = T_CB.X50 FROM [Fact_CorridorBreach] AS CB INNER JOIN @tmp_corridorBreach AS T_CB ON CB.[uid] = T_CB.[uid]
UPDATE if EXISTS or INSERT
L'aggiornamento o inserimento condizionale all'esistenza o meno di un record ha diversi approcci efficienti in base alle condizioni\ambito in cui ci si trova. Vedere qui stackoverflow
Opzione 1, ON DUPLICATE KEY
Buona soluzione per records multipli per inserts/updates. Tuttavia usa solo la verifica su una chiave primaria.
INSERT INTO table (id, user, date, points) VALUES (1, 1, '2017-03-03', 25), (2, 1, '2017-03-04', 25), (3, 2, '2017-03-03', 100), (4, 2, '2017-03-04', 150) ON DUPLICATE KEY UPDATE points = VALUES(points)
Opzione 2, ROWCOUNT
Per piccole quantità di dati:
UPDATE mia_tabella SET campo1='Peppino' WHERE id=1234 IF @@ROWCOUNT=0 INSERT INTO mia_tabella(campo1) VALUES ('Peppino');
Opzione 3, MERGE
Bene per grandi query. Per leggere da una tabella e inserting/updating in un'altra di conseguenza.
MERGE dbo.Test WITH (SERIALIZABLE) AS T USING (VALUES (3012, 'john')) AS U (id, name) ON U.id = T.id WHEN MATCHED THEN UPDATE SET T.name = U.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (U.id, U.name);
Raggrupamenti
GROUP BY
Ecco un esempio che verifica anche in intervallo di date mediante la BETWEEN
DECLARE @Dt_Day AS VARCHAR(2) = '16' SELECT MONTH(tms) as mese , DAY(tms) as giorno, COUNT(*) as nrecord FROM [ESB].[scr].[samples] with (nolock) WHERE tms BETWEEN '2016-11-' + @Dt_Day + ' 00:00:00.00' AND '2016-11-' + @Dt_Day + ' 23:59:59.999' --AND VALUE <> 0.000000 AND modbusid < 90000 GROUP BY MONTH(tms),DAY(tms) ORDER BY MONTH(tms),DAY(tms)
Attenzione, indicando delle date stringa nella BETWEEN, avverrà una approssimazione per cui i primi dati di mezzanotte del 17/11/2016 verranno selezionati come appartenenti al giorno prima.
StoredProcedure, Views, Functions
StoredProcedure
Creazione:
ToDo
Viste
Creazione:
CREATE VIEW [dbo].[v_Projects] AS SELECT P.[IDProject] --,PV.[IDVendor] ,P.[IDOwner] ,P.[FullName] ,P.[Description] ,(SELECT MIN(A.DateFrom) FROM .[dbo].[Activities] AS A WHERE P.[IDProject] = A.[IDProject] ) AS [DateFrom] ,(SELECT MAX(A.DateTo) FROM .[dbo].[Activities] AS A WHERE P.[IDProject] = A.[IDProject] ) AS [DateTo] --,P.[ProgressPercentage] ,(SELECT (SUM(A.[ProgressPercentage]) / COUNT(*)) FROM .[dbo].[Activities] AS A WHERE P.[IDProject] = A.[IDProject] ) AS [ProgressPerc] FROM [dbo].[Projects] AS P
Funzioni
Funzioni scalari
Creazione:
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL DROP FUNCTION ufnGetInventoryStock; GO CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int) RETURNS int AS -- Returns the stock level for the product. BEGIN DECLARE @ret int; SELECT @ret = SUM(p.Quantity) FROM Production.ProductInventory p WHERE p.ProductID = @ProductID AND p.LocationID = '6'; IF (@ret IS NULL) SET @ret = 0; RETURN @ret; END;
Funzioni tabellari
Creazione:
/* -- ============================================= -- Author: Giuseppe AINO -- Create date: 01/03/2019 -- Description: Data una stringa CSV restituisce una variabile tabella con tante righe quante sono le porzioni di stringa separate da virgola o altro carattere chiave -- ============================================= Es: DECLARE @txtSQL_View as VARCHAR(MAX) = '' --?? SELECT (CASE WHEN (CHARINDEX(' AS ', Value) > 0) THEN SUBSTRING(Value, 0, CHARINDEX(' AS ', Value) ) ELSE Value END) AS Campo FROM dbo.Split(@txtSQL_View, ',') */ ALTER FUNCTION [dbo].[Split]( @myString nvarchar (max), @Delimiter nvarchar (10)) RETURNS @ValueTable TABLE ([Value] nvarchar(max)) BEGIN DECLARE @NextString nvarchar(4000) , @Pos int , @NextPos int , @CommaCheck nvarchar(1) --Initialize SET @NextString = '' SET @CommaCheck = right(@myString,1) --Check for trailing Comma, if not exists, INSERT --if (@CommaCheck <> @Delimiter ) SET @myString = @myString + @Delimiter --Get position of first Comma SET @Pos = charindex(@Delimiter,@myString) SET @NextPos = 1 --Loop while there is still a comma in the String of levels WHILE (@pos <> 0) BEGIN SET @NextString = LTrim(RTrim( REPLACE( REPLACE( REPLACE(SUBSTRING(@myString,1,@Pos - 1), CHAR(9), ''), -- Tabulazione CHAR(10), ''), -- Inizio riga CHAR(13), '') -- Ritorno a capo )) INSERT INTO @ValueTable ([Value]) Values (@NextString) SET @myString = SUBSTRING(@myString, @pos +1, len(@myString)) SET @NextPos = @Pos SET @pos = charindex(@Delimiter,@myString) END RETURN END
Query distribuite
OPENQUERY
- Link interno Nozioni varie
- Link interno Ottimizzazioni, LinkedServer vs Openquery
Attenzione la sintassi della query è la stessa del SQL del DB sorgente, quindi se ad es si è configurato un linked server ad un DB Oracle la sintassi da usare deve essere secondo PL-SQL e non T-SQL !!!