Notizzettel

Aus Ergun's Wiki
Zur Navigation springen Zur Suche springen

Hier kommen Ideen für neue Seiten hin.

Beschreibung

Diese Seite ist in ständigem Fluss, da hier Vorschläge für neue Seiten eingestellt werden.

Themen, für die noch Seiten zu erstellen sind

Notizblock

SQL Joins

<Syntaxhilight> Declare @a table (

   id int primary key identity, 
   name nchar(50),
   nachname nchar(50)
    );

Declare @b table (

   id int primary key identity, 
   fk_A int,
   Speise nchar(50),
   Preis Smallmoney
   );

Insert into @a

   (name, nachname) Values 
   ('Lars', 'Dehnert'), 
   ('Deniz', 'Ergun'),
   ('Ulli', 'Gompf'),
   ('Ulli', 'NoName'),
   ('Rasmus', 'Leißner');

Insert into @b

   (fk_a, Speise, Preis) Values 
   (1, 'Hünersuppe', 1.52),
   (3, 'Brokkoli', 1.01),
   (2, 'Nudeln', 5.40),
   (3, 'Burger', 0.99),
   (5, 'Wrap', 2.50),
   (6, 'Schnecken', 7.99);

SELECT id, name, nachname FROM @a;

SELECT id, Speise, Preis, fk_A FROM @b;


/* Inner JOIN (Alle übereinstimmenden) */

   SELECT 

a.name as IJ_NAME_A, a.nachname as IJ_Nachname_A, a.id AS IJ_ID_A, b.fk_a AS IJ_FK_A, b.Speise AS IJ_Speise_B, b.Preis AS IJ_Preis_B

   FROM @a AS a
   INNER JOIN @b AS b ON b.fk_a = a.id;

/* CROSS JOIN (Jeder mit jedem)

   SELECT 

a.name as CJ_NAME_A, a.nachname as CJ_Nachname_A, a.id AS CJ_ID_A, b.fk_a AS CJ_FK_A, b.Speise AS CJ_Speise_B, b.Preis AS CJ_Preis_B

   FROM @a AS a
   CROSS JOIN @b AS b;
  • /

/* FULL OUTER JOIN (Alle übereinstimmungen und alle nicht übereinstimmenden) */

   SELECT 

a.name as FOJ_NAME_A, a.nachname as FOJ_Nachname_A, a.id AS FOJ_ID_A, b.fk_a AS FOJ_FK_A, b.Speise AS FOJ_Speise_B, b.Preis AS FOJ_Preis_B

   FROM @a AS a
   FULL OUTER JOIN @b AS b ON b.fk_a = a.id;

/* FULL OUTER JOIN (Alle nicht übereinstimmenden) */

   SELECT 

a.name as FOJN_NAME_A, a.nachname as FOJN_Nachname_A, a.id AS FOJN_ID_A, b.fk_a AS FOJN_FK_A, b.Speise AS FOJN_Speise_B, b.Preis AS FOJN_Preis_B

   FROM @a AS a
   FULL OUTER JOIN @b AS b ON b.fk_a = a.id
   WHERE a.id IS NULL OR b.id IS NULL;

/* LEFT JOIN (Alle übereinstimmungen und alle nicht in Tab B übereinstimmenden) */

   SELECT 

a.name as LJ_NAME_A, a.nachname as LJ_Nachname_A, a.id AS LJ_ID_A, b.fk_a AS LJ_FK_A, b.Speise AS LJ_Speise_B, b.Preis AS LJ_Preis_B

   FROM @a AS a
   LEFT JOIN @b AS b ON b.fk_a = a.id;

/* LEFT JOIN (Alle nicht in Tab B übereinstimmenden) */

   SELECT 

a.name as LJN_NAME_A, a.nachname as LJN_Nachname_A, a.id AS LJN_ID_A, b.fk_a AS LJN_FK_A, b.Speise AS LJN_Speise_B, b.Preis AS LJN_Preis_B

   FROM @a AS a
   LEFT JOIN @b AS b ON b.fk_a = a.id
   WHERE b.id IS NULL;

/* RIGHT JOIN (Alle übereinstimmungen und alle nicht in Tab B übereinstimmenden) */

   SELECT 

a.name as RJ_NAME_A, a.nachname as RJ_Nachname_A, a.id AS RJ_ID_A, b.fk_a AS RJ_FK_A, b.Speise AS RJ_Speise_B, b.Preis AS RJ_Preis_B

   FROM @a AS a
   RIGHT JOIN @b AS b ON b.fk_a = a.id;

/* RIGHT JOIN (Alle nicht in Tab B übereinstimmenden) */

   SELECT 

a.name as RJN_NAME_A, a.nachname as RJN_Nachname_A, a.id AS RJN_ID_A, b.fk_a AS RJN_FK_A, b.Speise AS RJN_Speise_B, b.Preis AS RJN_Preis_B

   FROM @a AS a
   RIGHT JOIN @b AS b ON b.fk_a = a.id
   WHERE a.id IS NULL;
   

/* Spielwiese */

   SELECT 

a.name as IJ_NAME_A, a.nachname as IJ_Nachname_A, a.id AS IJ_ID_A, b.fk_a AS IJ_FK_A, b.Speise AS IJ_Speise_B, b.Preis AS IJ_Preis_B

   FROM @a AS a
   INNER JOIN @b AS b ON b.fk_a = a.id
   WHERE a.nachname = 'Gompf'
   Order by b.preis asc

<Syntaxhilight/>

Powershell

Get-EventLog System -newest 100 | Where-Object {$_.EntryType -ne "Information"} |fl * | out-file $env:userprofile\Desktop\Erroevents.txt

Windows Systemabbild erstellen

Manueller Aufruf: sdclt.exe /BLBBACKUPWIZARD

MSG

msg "%username%" "Denke dran"

Einzelnachweise, Weblinks und Anmerkungen