CTE Artikelsuche mit Rekursion umsetzen

[MrM^]

Hallo an alle Entwickler!

Ich suche nach einer Möglichkeit, per allgemeinen Tabellenausdruck mehrere Suchbegriffe an eine Tabelle zu übergeben.

Das Ergebnis soll die Schnittmenge aller Rows zurückgeben, in denen man alle eingegebenen Suchbegriffe finden kann (egal in welcher Tabellenspalte).

Mein derzeitiger Select sieht folgendermaßen aus:
[highlight=sql]
DECLARE @sString as Varchar(50);
SET @sString = '%Testartikel%';
WITH tmpsearch (ItemID, Cycle)
AS
(
SELECT i.ItemID, 0 as Cycle FROM Items as i
UNION ALL
SELECT i.ItemID, Cycle + 1 FROM Items as i
INNER JOIN tmpsearch tmp ON tmp.ItemID = i.ItemID
AND Cycle < 5 AND
AND i.Description LIKE @sString
)
SELECT ItemID FROM tmpsearch where Cycle > 4;
[/highlight]

Um das ganze etwas einfach zu halten, würde ich statt einer TABLE Variable lieber ein Array verwenden. Gibt es Arrays in MSSQL und wenn ja, wie deklariere ich sie? Gibt es zudem noch eine Funktion, die mir den letzten Index dieses Array zurückgibt?

Gruß, MrM^

[MrM^]

Ich habe jetzt erst einmal versucht, das ganze mit einer TABLE Variable zu lösen.
Im Moment sieht der Code so aus:

[highlight=sql]
USE db_test;
GO
-- Tabellenvariable deklarieren, Maximalwert ermitteln
DECLARE @input TABLE(i_index integer, s_string Varchar(50));
DECLARE @Max as Integer;
INSERT INTO @input(i_index, s_string)
(SELECT 1, '%kopierpapier%' UNION SELECT 2, '%A4%');
SELECT @Max = Max(i_index) from @input;
-- Rekursion ausführen, letzten Zyklus selektieren
WITH test(itemID, s_description1, s_description2, cycle) as
(SELECT i.itemID, i.s_description1, i.s_description2, 0 as cycle from item AS i
UNION ALL
SELECT i.itemID, i.s_description1, i.s_description2, cycle + 1 from item AS i
INNER JOIN test t ON i.itemID = t.itemID and cycle <= @Max
AND (i.s_description1 LIKE (SELECT s_string from @input WHERE i_index= cycle)
OR i.s_description2 LIKE (SELECT s_string from @input WHERE i_index= cycle)))
SELECT * from test where cycle > @Max - 1 Order by itemID;
[/highlight]

Leider wird cycle im verschachtelten SELECT auf die Tabellenvariable immer mit dem Wert aus dem Ankerelement gefüllt, daher bekomme ich kein Ergebnis. Gibt es einen anderen Weg, das zu lösen?

[hwoess]

Irgendwie verstehe ich das alles nicht ganz, aber mal ein Versuch:

select * from item where exists
(select 1 from @input where description1 like s_string or description2 like s_string)

... was sagst du dazu

bye,
Helmut

[MrM^]

Hallo Helmut,

Deine Suche ist ein schneller und einfacher Weg, um die Ergebnismenge zu erweitern, ich möchte die Suchergebnisse allerdings einschränken.

am besten ich erläutere erst einmal, welche Aufgabe welche Tabelle hat und welche Felder in ihr zu finden sind.

@input: Diese Tabelle enthält die Suchbegriffe und einen Index, der im fertigem SELECT eindeutig sein wird und auto increment besitzt.

@Max: enthält den höchsten Indexeintrag von @input.

item: Diese Tabelle enthält die zu suchenden Artikel mit zugehörigen werten wie Bezeichnung und Artikelnummer.

test: Allgemeiner Tabellenausdruck. Über den Definierten SELECT ruft sich test so lange selbst auf, bis @Max erreicht ist. 'cycle' ist hier der X te Aufruf.

Im finalen SELECT wird der letzte Auruf vom CTE selektiert.


Zur eigentlichen Suche: Selektiert werden sollen nur Artikel, die irgendwo in der Bezeichnung die gesuchten Begriffe enthalten. Es wird Spaltenübergreifend gesucht, und alle Begriffe müssen im gefundenen Artikel vorkommen.

[hwoess]

Sorry, dann verstehe ich das Problem nicht. Bräuchte mal ein komplettes (aber komplexes, damit man das Problem auch erkennen kann!) Beispiel, wo du das erwartete Ergebnis einfach darunterschreibst und dazu angibst, warum eine Zeile jetzt im Ergebnis steht bzw. warum sie nicht drinnen steht.
Aber ich kann derzeit weder verstehen, warum die Tabellenvariable @input ein Autoincrement-Feld braucht noch was das bedeutet: ...die irgendwo in der Bezeichnung die gesuchten Begriffe enthalten. Es wird Spaltenübergreifend gesucht... - also was jetzt - in der Bezeichnung suchen oder in allen Spalten des Records (was aber m.E. keinen Sinn macht)?

bye,
Helmut

[MrM^]

Angenommen, in Tabelle 'item' stehen folgende informationen:

itemID itemno   description1     description2
1      ABC1023  Kopierpapier     A4-Format
2      1002CD4  Tabellierpapier  EDV-Tab
3      10A2500  RJ45 Kabel       5 m CAT
4      1BC0267  Mineralwasser    Sprudel
5      10A0E27  Glas             Spezial 300ml
6      100234D  Telefon          EDV Anlage Schwarz
7      34D895G  Computer         Aldi Kiste
8      567E90I  Bildschirm       19 Zoll Monitor

Und jetzt wird ein Suchbegriff eingegeben:

"papier A4"

Soll folgendes Ergebnis ausgegeben werden:

itemID itemno   description1     description2
1      ABC1023  Kopierpapier     A4-Format

Sucht man nach

"papier"

Erhält man

itemID itemno   description1     description2
1      ABC1023  Kopierpapier     A4-Format
2      1002CD4  Tabellierpapier  EDV-Tab

Und wenn man nach 'Computer EDV' sucht, wird nichts gefunden, weil der Computer keinen EDV String in irgendeinem Feld stehen hat.
Der Suchstring wird nach Leerzeichen gesplittet und in eine temporäre tabelle geschrieben, das funktioniert bereits.

Das Szenario mit 'papier A4' sieht also folgendermaßen aus:

[highlight=sql]
USE db_test;
GO
-- Tabellenvariable deklarieren, Maximalwert ermitteln
DECLARE @input TABLE(i_index integer, s_string Varchar(50));
DECLARE @Max AS Integer;
INSERT INTO @input(i_index, s_string)
(SELECT 1, '%papier%' UNION SELECT 2, '%A4%');
SELECT @Max = Max(i_index) FROM @input;
-- Rekursion ausführen, letzten Zyklus selektieren
WITH test(itemID, s_description1, s_description2, cycle) AS
(SELECT i.itemID, i.s_description1, i.s_description2, 0 AS cycle FROM item AS i
UNION ALL
SELECT i.itemID, i.s_description1, i.s_description2, cycle + 1 FROM item AS i
INNER JOIN test t ON i.itemID = t.itemID AND cycle <= @Max
AND (i.s_description1 LIKE (SELECT s_string FROM @input WHERE i_index= cycle)
OR i.s_description2 LIKE (SELECT s_string FROM @input WHERE i_index= cycle)))
SELECT * FROM test WHERE cycle > @Max - 1 ORDER BY itemID;
[/highlight]

Den Index möchte ich zur Unterscheidung verwenden. Somit kann ich steuern, in welchem Zyklus nach welchem Feld gesucht wird. Im obigen Beispiel also nach 'papier' im ersten und nach 'A4' im zweiten Durchlauf. Der zweite Durchlauf enthält hierbei (durch den INNER JOIN) bereits die Ergebnismenge aus Durchlauf 1.
Mit @Max verhindere ich eine Endlosschleife, da die Tabelle auf sich selbst selektiert, daher soll Index auch einzigartig und auto_increment sein.

[openshinok]

hallo,
durch verknüpfst alle Felder die du durchsuchen möchtest zu einem VARCHAR(MAX) zwischen den Feldern fügst du ein Leerzeichen ein.

Dann trennst du deine Suchwörter. Entweder nach Leerzeichen oder einer anderen Logik.

Dann gehst du mit Charindex pro Suchbegriff und den Verknüpften Feldern im WHERE-Teil auf dein SELECT.

Mehrere Suchwörter verknüpfst du mit AND oder OR

in wannabe-Code:

WHERE
CHARINDEX('Papier',CAST(IsNull(FELD1,'') AS VARCHAR(MAX)) + Space(1) + CAST(IsNull(FELD2,'') AS VARCHAR(MAX)) ) > 0
AND
CHARINDEX('A',CAST(IsNull(FELD1,'') AS VARCHAR(MAX)) + Space(1) + CAST(IsNull(FELD2,'') AS VARCHAR(MAX)) ) > 0
AND
CHARINDEX('4',CAST(IsNull(FELD1,'') AS VARCHAR(MAX)) + Space(1) + CAST(IsNull(FELD2,'') AS VARCHAR(MAX)) ) > 0

[hwoess]

Okay, die Begriffe werden also aufgrund der Leerezeichen getrennt und einzeln in die temp. Tabelle @input geschrieben. Soweit okay. Und jetzt alle Records finden, wo die Begriffe aus @input in description1 und/oder description2 vorkommen:

declare @item table (
itemID        int,
itemno        varchar(8),
description1  varchar(99),
description2  varchar(99)
)
insert into @item values(1,      'ABC1023',  'Kopierpapier',     'A4-Format')
insert into @item values(2,      '1002CD4',  'Tabellierpapier',  'EDV-Tab')
insert into @item values(3,      '10A2500',  'RJ45 Kabel',       '5 m CAT')
insert into @item values(4,      '1BC0267',  'Mineralwasser',    'Sprudel')
insert into @item values(5,      '10A0E27',  'Glas',             'Spezial 300ml')
insert into @item values(6,      '100234D',  'Telefon',          'EDV Anlage Schwarz')
insert into @item values(7,      '34D895G',  'Computer',         'Aldi Kiste')
insert into @item values(8,      '567E90I',  'Bildschirm',       '19 Zoll Monitor')

declare @input table (
code      int,
s_string  varchar(99)
)
insert into @input values(1, '%papier%')
insert into @input values(1, '%A4%')
insert into @input values(2, '%papier%')
insert into @input values(3, '%Computer%')
insert into @input values(3, '%EDV%')

select * from @item where not exists
(select 1 from @input where code = 1 and description1 not like s_string and description2 not like s_string)

select * from @item where not exists
(select 1 from @input where code = 2 and description1 not like s_string and description2 not like s_string)

select * from @item where not exists
(select 1 from @input where code = 3 and description1 not like s_string and description2 not like s_string)

... liefert genau dein gewünschtes Ergebnis - jetzt okay (sonst Beispiel, wo es nicht passt)?

bye,
Helmut

PS: erweitern müsste man noch bezüglich NULL-Felder, habe ich jetzt aber mal weggelassen.

[ebis]

Hier ein Versuch mit rekursiver CTE:
- Tabellen #Items und #input werden hier im Bsp über die CTE
- die Numbers-Table sollte man sich auch persistent als Tabelle hinlegen anstatt sie immer wieder temporär zu generieren)

WITH  items 
   (  itemID 
   ,  itemno 
   ,  desc1  
   ,  desc2 
   )   AS 
   (  SELECT 1, 'ABC1023',  'Kopierpapier'   ,  'A4-Format'          UNION ALL
      SELECT 2, '1002CD4',  'Tabellierpapier',  'Tab'                UNION ALL
      SELECT 3, '10A2500',  'RJ45 Kabel',       '5 m CAT'            UNION ALL
      SELECT 4, '1BC0267',  'Mineralwasser',    'Sprudel'            UNION ALL
      SELECT 5, '10A0E27',  'Glas',             'Spezial 300ml'      UNION ALL
      SELECT 6, '100234D',  'Telefon',          'EDV Anlage Schwarz' UNION ALL
      SELECT 7, '34D895G',  'Computer',         'Aldi Kiste'         UNION ALL
      SELECT 8, '567E90I',  'Bildschirm',       '19 Zoll Monitor' 
   )
   ,  searches AS
   (  SELECT 1 AS SearchId , 'XYZ'  AS Search 
      UNION ALL SELECT 2   , 'Papier' 
      UNION ALL SELECT 3   , 'EDV-Computer' 
--      UNION ALL SELECT 4   , 'EDV'
   ) 
   ,  a0 AS
   (  SELECT 1 AS n UNION ALL SELECT 1
   )
   ,	a1 AS
	(  SELECT 1 AS n FROM a0 AS a, a0 AS b
	)
	,	a2 AS
   (  SELECT 1 AS n FROM a1 AS a, a1 AS b
   )
   ,	a3 AS
	(  SELECT 1 AS n FROM a2 AS a, a2 AS b
	)
	,	numbers AS
	(  SELECT ROW_NUMBER() OVER (ORDER BY n)  AS num 
		FROM   a3
	)
	,  input  AS
   (  SELECT  CHAR(37)+ X.[Value]+ CHAR(37)                                AS String
            , S.SearchID                                                   AS Code
      FROM   Searches AS S
      CROSS APPLY (SELECT  SUBSTRING   (SPACE(1) + S.Search + SPACE(1)
                                       ,Num + 1
                                       ,CHARINDEX   (SPACE(1)
                                                    ,SPACE(1) + S.Search + SPACE(1)
                                                    ,Num + 1
                                                    ) - Num -1)                 AS [Value]
                   FROM    Numbers   
                   WHERE   Num <= LEN (SPACE(1) + S.Search + SPACE(1)) - 1     
                     AND    SUBSTRING (SPACE(1) + S.Search + SPACE(1), Num, 1) = SPACE(1)  
                  )  AS X	([Value])
   )
   ,  MaxCode AS
   (  (SELECT MAX (Code)  AS MC
       FROM input
       )
   )
   ,  Findings 
   (     itemID
      ,  itemno
      ,  desc1
      ,  desc2
      ,  Code 
   )  AS
   (  SELECT  F.itemID,  F.itemno,  F.Desc1,  F.Desc2, 1 AS Code
      FROM    Items AS F
      WHERE   exists
          (  SELECT 1 FROM input I 
             WHERE -- I.code = 1 AND
                   F.Desc1  like I.string 
               or  F.Desc2  like I.string
          )
      
      UNION ALL
      
      SELECT  F.itemID,  F.itemno, F. Desc1,  F.Desc2, F.Code + 1
      FROM    Findings AS F
      JOIN    items    AS I On F.itemID = I.ItemID 
      WHERE  F.Code + 1 <= (SELECT  MC FROM MaxCode) AND
              exists
          (  SELECT 1 FROM input I 
             WHERE -- F.code +1 = I.Code AND
                  F.desc1  like string 
               or F.desc2  like string
          )
 )
    SELECT  DISTINCT
            F.itemID,  F.itemno, F. Desc1,  F.Desc2
    FROM    Findings AS F

[MrM^]

Vielen Dank für die vielen Antworten, damit habe ich nicht gerechnet

Um alle Antworten einmal kurz anzusprechen:

Das CHARINDEX Beispiel von openshinok hat feste Strings eingetragen. Erweitern könnte man ihn um Variablen, allerding beschränkt sich die Suche dann um eine festgelegte Anzahl Suchbegriffe (das ganze soll nachher als Funktion ausführbar sein)

Ebis' code ist sehr umfangreich, leider kann ich exists auf diese Art und Weise nicht verwenden, da mehr Suchbegriffe die Ergebnismenge hier ausweiten.

Helmuts Code werde ich jetzt noch ein wenig testen, der erste Eindruck sagt mir aber, dass das genau das ist was ich brauche. Rekursion ist nicht zwingend notwendig, solange das Ergebnis dasselbe ist.