Login Login
MORE

WIDGETS

Widgets

Wanted articles
Who is online?
Article tools

MSSQL:Comandi di Query

From Aino Wiki

Jump to: navigation, search

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

SELEZIONE AGGIORNAMENTO

Cancellazione

TRUNCATE TABLE
Rimuove tutte le righe da una tabella. E' simile a DELETE senza clausola WHERE, è tuttavia più rapida e utilizza un numero minore di risorse di sistema e del log delle transazioni.

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}
ALTRO
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'

)
ALTRO
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}

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]

Controllo del flusso

CASE WHEN

SELECT 
   CASE 
      WHEN MIN(valore) <= 0 THEN 0 
      WHEN MAX(1/valore) >= 100 THEN 1 
      ELSE
        -1
   END 
FROM Tabella

Caso in cui una azione è su più condizioni in OR:

CASE WHEN @P1 in ('a', 'd', 'z') THEN 
        1
    WHEN @P1 in ('b', 't') THEN
        2
    ELSE 
       0 
END

Altro

DECLARE @ipotesiDurataSegmento AS INT = 100 -- in minuti
 
SELECT 
	CB.[uid],CB.[CompanyCode],CB.[ShipCode],CB.[AlertId],CB.[Segment1Id],CB.[Port1],CB.[Port2],CB.[on_UTCDate],CB.[off_UTCDate]
	,DATEDIFF(MINUTE, CB.[on_UTCDate],CB.[off_UTCDate]) AS X 
 
	, DATEDIFF(MINUTE, CB.[on_UTCDate],CB.[off_UTCDate]) / CONVERT(DECIMAL(10,2), @ipotesiDurataSegmento)
		 * 100 AS X1
 
	, CASE 
		WHEN CB.severity_type = 1 THEN
			DATEDIFF(MINUTE, CB.[on_UTCDate],CB.[off_UTCDate]) / CONVERT(DECIMAL(10,2), @ipotesiDurataSegmento)
				* 100
		WHEN CB.severity_type = 2 THEN
			(DATEDIFF(MINUTE, CB.[on_UTCDate],CB.[off_UTCDate]) / CONVERT(DECIMAL(10,2), @ipotesiDurataSegmento)
				* 100) + 50
	  ELSE
		-1
	  END	
	 AS X2
 
	,CB.[severity],CB.[severity_type]
	,'|' AS I
	,A.[Id],A.[CompanyCode],A.[ShipCode],A.[ThresholdId],A.[Type],A.[TriggerDateUTC],A.[Segment1Id]
FROM		[Fact_CorridorBreach]	AS CB	(NOLOCK)
INNER JOIN	[Src_Alerts]			AS A	(NOLOCK)	ON CB.[Segment1Id] = A.[Segment1Id]
--INNER JOIN	[Dim_Segments]			AS S	(NOLOCK)	ON CB.[Segment1Id] = S.Id
--WHERE A.[ThresholdId] <> -1
--ORDER BY CB.[on_UTCDate] DESC
ORDER BY CB.Segment1Id

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.

Mappa e Links


MS SQL

Author