Avatar billede hojgaard Nybegynder
22. december 2009 - 10:08 Der er 7 kommentarer og
1 løsning

Delimited string

Jeg har følgende tabel:

PRODUCTS
- ProductID
- RelatedProducts

RelatedProducts gemmes sådan her: 12345|4321|334455
Hvis der er flere related products, adskilles de af en pipe '|'

Jeg vil gerne kunne finde alle produkter, som har en relation til et bestemt produkt.
Eksempelvis vil jeg gerne kunne hente alle produkter, som har related product '4321'.

Det jeg er ude efter er noget alla denne her:
SELECT * FROM Product WHERE RelatedProducts LIKE '%4321%'

Problemet er bare denne query vil også returnere produkter med related products som f.eks. '432'

Nogle gode ideer?

Kører MS SQL 2005
Avatar billede hrc Mester
22. december 2009 - 10:42 #1
Du skriver ikke om dit layout kan ændres. Det bør det hvis du kan; en meget dårlig måde at gruppere på - og slet ikke i den relationelle databases ånd. Da det ikke er det, så kan man kun meget svært pløje sådan en streng igennem i TSQL.

Hvis du skal gøre noget uden at ændre disse data så må du lave en function (scalar-valued) der opretter en temporær tabel (en med # i navnet) som delstrengene flyttes over i. Denne tabel laver du så en søgning på.

Din Select ovenfor vil i øvrigt ikke returnere '432', men den vil fange '54321' og '33432132' osv
Avatar billede hojgaard Nybegynder
22. december 2009 - 10:47 #2
Tak for dit svar hrc.
Du har helt ret, den vil selvfølgelig ikke returnere 432.
Det gik lidt hurtigt at få skrevet et eks. ned :)

Jo, designet kan stadig rettes, men ville se om der ikke var en anden løsning først.
22. december 2009 - 18:52 #3
Den bedste, enkleste, og mest fleksible loesning og som er nemmest at vedligeholde for sig selv om et par aar eller andre der overtager projected maa da altid vaere at (1) analysere problemet og (2) bygge en relationel struktur for loesningen.

I det foreliggende tilfaelde maa det naturligvis klarlaegges hvad det vil sige at et produkt er "related" til et andet.  Hvis det er saaledes at hvis produkt 5544 er related til produkterne 12345, 4321, og 334455 saa er product 12345 altid relateret til produkterne 5544, 4321, og 334455 o.s.v. paa samme maade som for eksempel alle messing produkter er related til hinanden og alle staal produkter er related til hinanden saa kan du definere relations-grupper og stille tabellen op for eksempel saaledes:

CREATE TABLE hojgaard(product VARCHAR(10), relationgruppe VARCHAR(10));
INSERT INTO hojgaard VALUES('prod5544','relgrp1');
INSERT INTO hojgaard VALUES('prod12345','relgrp1');
INSERT INTO hojgaard VALUES('prod4321','relgrp1');
INSERT INTO hojgaard VALUES('prod334455','relgrp1');
INSERT INTO hojgaard VALUES('prod25','relgrp2');

Hvis du saa vil have alle produkter der er related til prod4321 (foruden prod4321 selv) saa bliver din query denne:

SELECT product FROM hojgaard
WHERE product != 'prod4321'
AND relationgruppe =
  (SELECT relationgruppe FROM hojgaard WHERE product = 'prod4321')

hvilket giver dette resultat:

product 
prod5544
prod12345
prod334455

Hvis der ikke kan stilles nogle regler op for produktgrupper, hvis det for eksempel er et spoergsmaal om smag og behag (saasom "de der koebte dette produkt koebte ogsaa ...." saa laver du en selvstaendig tabel hvor du for hvert produkt laver en record for hver produkt der er related til det.  For eksempel:

CREATE TABLE hojgaard2(product1 VARCHAR(10), product2 VARCHAR(10));
INSERT INTO hojgaard2 VALUES('prod5544', 'prod4321');
INSERT INTO hojgaard2 VALUES('prod12345', 'prod4321');
INSERT INTO hojgaard2 VALUES('prod334455', 'prod4321');
INSERT INTO hojgaard2 VALUES('prod4321', '25');
INSERT INTO hojgaard2 VALUES('prod334455', '25');

Hvis du saa vil have alle produkter der er related til prod4321 bliver din query denne:

SELECT h1.product FROM hojgaard h1
JOIN hojgaard2 h2 ON h1.product = h2.product1
WHERE h2.product2 = 'prod4321'

hvilket igen giver dette:

product 
prod5544
prod12345
prod334455
Avatar billede hrc Mester
23. december 2009 - 00:15 #4
Jeg kunne godt tænke mig en anden løsning uden redundans (data der bruges igen og igen, eks. prod4321) og uden nøgler der er tekststrenge.

Hvis jeg har to tabeller model og bilmaerke og model peger over i bilmaerke:

create table bilmaerke
(
  ref int identity(1,1),
  navn nvarchar(50)
  constraint [pk_bilmaerke] primary key(ref)
)

create table model
(
  ref int identity(1,1),
  bilmaerke_ref int not null,
  navn nvarchar(50),
  aktiv bit not null constraint [df_model_aktiv] default(1),
  constraint [pk_model] primary key(ref),
  constraint [fk_model_bilmaerke] foreign key(bilmaerke_ref) references bilmaerke(ref)
)

I tabellerne er "ref" altid primærnøgle (altså den værdi der unikt identificerer recorden). En fremmednøgle er det felt indeholder en anden tabels primærnøgle. Det felt kaldes altid tabellens navn efterfulgt af _ref, eksempelvis bilmaerke_ref. På den måde kan jeg altid se hvilken tabel der refereres til.

Den anden ting er de constraints der defineres. pk_<tabelnavn> for Primary Key, fk_<tabelnavn>_<fr.tabelnavn> for Foreign Key og df_<tabelnavn>_<feltnavn> for Default. Med de simple regler har jeg fuldt styr på mine tabeller og deres relationer.

Fordi der er oprettet constraints kan du heller ikke slette en bilmaerke-record hvis der er model-records der bruger den (men så kan du bruge kaskade sletning, men lad være med det til at begynde med!)

Indsætter lige lidt data (kommentarer starter med --)

insert into bilmaerke(navn) values('Ford');      -- ref = 1
insert into bilmaerke(navn) values('Fiat');      -- ref = 2
insert into bilmaerke(navn) values('Cisitalia'); -- ref = 3
insert into bilmaerke(navn) values('Delahaye');  -- ref = 4

insert into model (bilmaerke_ref, navn) values(1,'Fiesta');
insert into model (bilmaerke_ref, navn) values(1,'Taunus');
insert into model (bilmaerke_ref, navn) values(1,'Sierra');
insert into model (bilmaerke_ref, navn) values(1,'Granada');
insert into model (bilmaerke_ref, navn) values(1,'Mondeo');
insert into model (bilmaerke_ref, navn) values(2,'Punto');
insert into model (bilmaerke_ref, navn) values(2,'Panda');
insert into model (bilmaerke_ref, navn) values(2,'Multipla');
insert into model (bilmaerke_ref, navn) values(2,'500');
insert into model (bilmaerke_ref, navn) values(3,'D46');
insert into model (bilmaerke_ref, navn) values(3,'202');
insert into model (bilmaerke_ref, navn) values(3,'202MM');
insert into model (bilmaerke_ref, navn) values(3,'202SMM');
insert into model (bilmaerke_ref, navn) values(4,'Type 32, 1909');
insert into model (bilmaerke_ref, navn) values(4,'Type 32, 1910');
insert into model (bilmaerke_ref, navn) values(4,'Tourer, 1925');
insert into model (bilmaerke_ref, navn) values(4,'135M Roadster');
insert into model (bilmaerke_ref, navn) values(4,'135M Coupe');

Hvis du nu vil se modellerne af Delahaye så skal du bare skrive

select navn
from model
where (bilmaerke = 4)

eller via navn

select m.navn
from model m
join bilmaerke bm on (bm.ref = m.bilmaerke_ref)
where (bm.navn = 'Delahaye')
25. december 2009 - 10:42 #5
hrc, jeg er ikke uenig i det du forklarer om database strukturer for typiske "en-til-mange" relationer, trae-strukturer, for eksempel hvor hvert bilmaerke, saa som Ford, har en antal modeller, saa som Fiesta og Taunus og hvor de modeller ikke hoerer til andre bilmaerker, for eksempel at der ikke bestaar en Fiat Fiesta.

Imidlertid laeste jeg hojgaard's sporgsmaal som et mange-til-mange problem hvor produkterne relaterer til hinanden ligesom ting der ligger mellem hinanden i en kasse snarere produkter der sidder i en traestruktur hvor hvert produkt undtagen rod-produktet har underordnede produkter. Hvis for eksempel produkt 5544 relaterer til 12345, 4321, og 334455, saa kan man vel forvente at produkt 12345 relaterer til produkt 5544, 4321, og 334455 o.s.v.  Hvis man kan vaere sikker paa at kasserne ikke overlapper saa er det simpleste at at give hver kasse et navn og saa lave en tabel hvor hver raekke indeholder navnet paa kassen.  Hvis systemet er af begraenset omfang saa behoever man ikke at gaa videre.  Hvis der er en hel masse "kasser" vil det nok vaere en god ide at lave en tabel med kasser og saa i tabellen med produkter lave en foreign key til tabellen med kasser.

Men hvad nu hvis relationerne ikke passer i adkilte kasser?  Hvis for eksempel Astrid godt kan lide Bent, Camille, og Daniel og Bent godt kan lide Camille og Erik men bestemt ikke Astrid, hvordan vil man modellere det i en "bilmaerke-model" tabel struktur?  I saadanne tilfaelde maa vejen frem vaere at lave en tabel med to (eller flere) felter  hvor felt 1 indeholder et produkt og felt 2 et produkt det relaterer til.  Hvis der er fem produkter der relaterer til produkt 4321 saa forekommer 4321 i fem raekker.  Det er der ikke noget "redundant" i fordi noeglen daekker to kolonner.  (Hvis man i en raekke havde produkt 5544 relaterende til produkt 4321 og i naeste raekke produkt 5544 relaterende til produkt 4321 saa ville det vaere redundant.)

Og saa kan du ikke lide noegler der er tekststraenge.  Ok, smag og behag er foreskellig og jeg er godt klar over "performance" grundene til i store systemer at bruge numeriske noegler. Men  beskrivende noegler er perfekt mulige og er immervaek naemmere for mennesker at forstaa, saa naar jeg skal skitsere noget med tabeller foretraekker jeg at bruge beskrivende felter, uanset om man vil implementere det med numeriske noegler eller ej.

Venligst opfat ikke dette som et "angreb" paa dit indlaeg men som en debat hvor jeg siger:  "Det er rigtig nok hvad du siger, men det gaar videre."  Selv om det ikke er hovedformaalet med denne traad ville jeg vaere interesseret i dine reaktioner.
Avatar billede hrc Mester
25. december 2009 - 22:42 #6
Jeg er slet ikke uenig. Jeg baserer min handlinger på det jeg har lært - og der har du måske lært en hel del mere; er altid åben for diskussioner og forbedringer.
Jeg vil læse din kommentar igen når Tom Hanks (alias Langdon) er færdig med da Vinci (ikke noget storværk hidtil).
Avatar billede hrc Mester
26. december 2009 - 00:08 #7
Ikke meget guf i den film - dog kan jeg godt lide temaet om at religion (her kun kristendommen) er et bluf (glædelig jul)...

Havde ikke tænkt det så langt som at opfatte det som sammensatte nøgler. Jeg har tidligere løst tilsvarende sammensætning med en n-n-tabel med to fremmednøgler og et negeringsflag så man kan registre den negative relation (til Astrid) - men igen, ikke en løsning jeg helt holder af (hos mig var det registrering af boligkomplekser man foretrak men også de man ikke søgte til).

Hvad numeriske kontra literale nøgler angår. Der er vist ingen tvivl om at indekser er optimerede til numeriske nøgler, af ens størrelse. Mangler stadig gode argumenter for at buruge tekststrenge som nøgler. Desuden kan jeg godt lidt at presse et "auto inc"-felt ned over alle records jeg laver, dette næsten uanset om det er en tabel der kun består af fremmendøgler (altså reelt er en sammensat nøgle). Jeg gær det for konsekvens og fordi jeg har fundet det lettere at styre.
29. december 2009 - 18:27 #8
hojgaard, forventer du flere indlaeg paa dette spoergsmaal?  Erfaringen viser at nye indlaeg er sjaeldne paa et spoergsmaal der er en juges tid gammelt og ikke har haft aktivitet i adskillige dage.  Er tiden ikke kommet til at lukke?  Det giver god orden, og saa staar det ikke laengere som aabent i min liste af indlaeg.

Jeg opretter dette som et svar idet jeg mener at have leveret en loesning til det problem du forelagde.  Hvis du vil dele points maa du nok efterlyse svar fra anden side.  Hvis du, paa den anden side, ikke har haft nytte af mit (eller andres) indlaeg maa du selv oprette et svar og tage pointsene.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester



IT-JOB

Cognizant Technology Solutions Denmark ApS

Sr. Test Analyst

Danske Andelskassers Bank A/S

IT-konsulent