SQL know-how

Kontakt

Når man sammensætte et udtræk in SQL, der er ofte man kan have brug for at opererer med et set data som der er ikke direkte eksistere i systemet. For eks. En database for en e-handel web applikation vil have standard tabeller så som ’Produkter’, ’Kunder’, ’Ordre’, ’Ordre_Detalje’ m.m., men hvis man vil udtrækker et set særlig data til en rapport som strække sig på tværs af data i ovennævnte tabeller skal man hidtil anvende ’views’. Problemet med ’views’ er at de bliver permanent del af database så snart man opretter dem. SQL 2005 stiller ny funktioner at lave udtræk på, CTE(Common Table Expressions).

Nedenstående eksempel illustrerer brug af ”WITH” funktion i SQL 2005.

For eksemplet. bruger vi ”pubs” databasen, har du ikke den, kan hentes fra følgende adresse:  http://sql.yaha.dk/NorthwindAndPubs/InstallNorthwindAndPubs.html

 

Her er eksemplet:

Først vi laver et udtræk som returnerer et set data med alle de forlægge som har udgivet et eller flere tidsskrifter og bo i 'Boston':

 

SELECT pub_name AS PubName,  publishers.pub_id AS PubID, state, city, title, price

FROM publishers

INNER JOIN titles on titles.pub_id = publishers.pub_id

WHERE city = 'Boston'

 

Nu tag vi 'WITH' funktion i brug ved at indpakke forrige udtræk i:

 

WITH PublishersAndpublishedTitle (pub_name,  pub_id, state, city, title, price) AS

(

SELECT pub_name AS PubName,  publishers.pub_id AS PubID, state, city, title, price

FROM publishers

INNER JOIN titles on titles.pub_id = publishers.pub_id

WHERE city = 'Boston'

)

 

SELECT *

FROM PublishersAndpublishedTitle

WHERE price < 8

ORDER BY pub_name ASC, pub_id ASC, title ASC

 

PublishersAndpublishedTitle er en midlertidig objekt, den eksistere kun i det øjeblik vi brugere den og så snart udtrækket er laveret, forsvinder den øjeblikkeligt.

 

Man kan anvende funktionen til rekursiv datamanipulationen(splitter en streng i en tabel):

Her er eksemplet:

 

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))

RETURNS table

AS

RETURN (

    WITH Pieces(pn, start, stop) AS (

      SELECT 1, 1, CHARINDEX(@sep, @s)

      UNION ALL

      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)

      FROM Pieces

      WHERE stop > 0

    )

    SELECT pn,

      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s

    FROM Pieces

  )

 

Start