T-SQL a výber prvej asociovanej položky
Po Sirovom príspevku na T-SQL pridám aj ja niečo. Predstavte si, že máte dve tabuľky Contact a PhoneNumber.
| Contact | | ContactId | | FirstName | | LastName | | ďalšie stĺpce | | | PhoneNumber | | PhoneNumberId | | ContactId | | Number | |
Pričom ContactId v tabuľke PhoneNumber je foreign key na stĺpec ContactId z tabuľky Contact.
A problém je nasledovný. Treba vybrať všetky kontakty a ku každému najviac jedno telefónne číslo. Je úplne jedno ktoré, ale najviac jedno. Určite väčšina z vás už podobný problém riešila. Ja používam už od čias mojich začiatkov s SQL nasledovné riešenie.
WITH PhoneNumberMinId (ContactId, MinPhoneNumberId) AS
(
SELECT ContactId, MIN(PhoneNumberId) AS MinPhoneNumberId
FROM PhoneNumber
GROUP BY ContactId
)
SELECT C.*, PN.Number
FROM Contact C LEFT JOIN
PhoneNumberMinId PNMI ON PNMI.ContactId = C.ContactId LEFT JOIN
PhoneNumber PN ON PN.PhoneNumberId = PNMI.MinPhoneNumberId
Táto syntax sa dá použiť len v SQL Server 2005 a vyššej verzii, ale použil som ju kvôli lepšej čitateľnosti a zrozumiteľnosti. Pred SQL 2005 som používal nasledovný select, ktorý je sémanticky úplne rovnaký.
SELECT C.*, PN.Number
FROM Contact C LEFT JOIN
(
SELECT ContactId, MIN(PhoneNumberId) AS MinPhoneNumberId
FROM PhoneNumber
GROUP BY ContactId
) AS PNMI ON PNMI.ContactId = C.ContactId LEFT JOIN
PhoneNumber PN ON PN.PhoneNumberId = PNMI.MinPhoneNumberId
Podobnú štruktúru mám teraz aj v MS CRM. Lenže MS CRM primárne kľúče nepoužíva Integer ale GUID. A SQL nepodporuje funkciu MIN nad GUID. Takže moje dlhoročné riešenie prestalo fungovať. Našťastie v SQL Server 2005 pribudli novinky, ktoré podobný problém dokážu vyriešiť. A to konkrétne funkcia ROW_NUMBER spolu s PARTITION BY.
WITH IndexedPhoneNumbers (ContactId, Number, RowIndex) AS
(
SELECT ContactId, Number,
ROW_NUMBER() OVER (PARTITION BY ContactId ORDER BY PhoneNumberId) AS RowIndex
FROM PhoneNumber
)
SELECT C.*, IPN.Number
FROM Contact C LEFT JOIN
IndexedPhoneNumbers IPN ON IPN.ContactId = C.ContactId AND IPN.RowIndex = 1
Možno to niekomu pomôže. Možno niekto pozná ešte elegantnejšie riešenie, tak nech ho kľudne pridá do komentárov.