Ispostavilo se da zadatk nije nimalo trivijalan :-)
Sad vidim kako je Excel mocna alatka za neke slucajeve :-)
Evo ovako, za najprostiji slucaj, da se samo upare dva po dva, bez ikakvih ogranicenja. Ovako sam to nekako resio, korak po korak:
-- zadatak: napraviti parove, 2 po dva, da bi se smestili u dvokrevetne sobe.
-- ako ima neko nesparen, da se napise 'SINGLE'
-- Za pocetak, dozvoljavamo da Muski (M) i zene (F) budu u istoj sobi
-- Da im dodelimo nekakav redosled
Code:
SELECT
GuestName
, 1 + (SELECT COUNT(*)
FROM Guests AS B
WHERE A.GuestName > B.GuestName) AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
-- dobijemo nesto ovako:
GuestName Redosled UkupanBroj
Cica 1 19
Djura 2 19
Goran 3 19
Jaca 4 19
Janko 5 19
Jeca 6 19
-- da napisemo levu polovinu spiska:
Code:
SELECT
GuestName
, Redosled
, UkupanBroj
FROM
(
SELECT
GuestName
, 1 + (SELECT COUNT(*)
FROM Guests AS B
WHERE A.GuestName > B.GuestName) AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS A
WHERE Redosled <= UkupanBroj/2
-- dobijemo ovo:
GuestName Redosled UkupanBroj
Cica 1 19
Djura 2 19
Goran 3 19
Jaca 4 19
Janko 5 19
Jeca 6 19
Juca 7 19
Krle 8 19
Laza 9 19
-- da pokusamo da dodamo desnu polovinu:
Code:
SELECT
A.GuestName AS Prvi
, A.Redosled AS RedosledPrvog
, A.UkupanBroj
, A.Redosled + A.UkupanBroj/2 AS RedosledDrugog
FROM
(
SELECT
GuestName
, 1 + (SELECT COUNT(*)
FROM Guests AS B
WHERE A.GuestName > B.GuestName) AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS A
WHERE Redosled <= UkupanBroj/2
-- i dobijemo ovo:
Prvi RedosledPrvog UkupanBroj RedosledDrugog
Cica 1 19 10
Djura 2 19 11
Goran 3 19 12
Jaca 4 19 13
Janko 5 19 14
Jeca 6 19 15
Juca 7 19 16
Krle 8 19 17
Laza 9 19 18
-- Vidimo koji redni broj odgovara kom rednom broju
-- Ali, nama trebau imena :-(
-- mozda ovako:
Code:
SELECT U.Prvi, U.RedosledPrvog, W.GuestName AS Drugi, U.RedosledDrugog
FROM
-- U = ceo izraz koji smo imali kad smo
-- dodali redne brojeve za desnu polovinu
(
SELECT
A.GuestName AS Prvi
, A.Redosled AS RedosledPrvog
, A.UkupanBroj
, A.Redosled + A.UkupanBroj/2 AS RedosledDrugog
FROM
(
SELECT
GuestName
, 1 + (SELECT COUNT(*)
FROM Guests AS B
WHERE A.GuestName > B.GuestName) AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS A
WHERE Redosled <= UkupanBroj/2
) AS U
JOIN
-- W = izraz koji smo upotrebili za oderdjivanje redosleda
(
SELECT
GuestName
, 1 + (SELECT COUNT(*)
FROM Guests AS B
WHERE A.GuestName > B.GuestName) AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS W
ON U.RedosledDrugog = W.Redosled
-- dobili smo ovo:
Prvi RedosledPrvog Drugi RedosledDrugog
Cica 1 Maca 10
Djura 2 Melanija 11
Goran 3 Mica 12
Jaca 4 MIka 13
Janko 5 Milan 14
Jeca 6 Pera 15
Juca 7 Veca 16
Krle 8 Zika 17
Laza 9 Zoca 18
-- Ostaje da dodamo osobu sa redosledom 19
-- Mozda UNION sa ovim:
Code:
SELECT
GuestName
, Redosled AS RedosledPrvog
, 'SINGLE' AS drugi
, NULL AS RedosledDrugog
FROM
(
SELECT
GuestName
, 1 + (SELECT COUNT(*)
FROM Guests AS B
WHERE A.GuestName > B.GuestName) AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS X
WHERE X.Redosled = X.UkupanBroj
-- UNION je OK, ali samo ako je UkupanBroj neparan
-- pa treba da uvedemo taj uslov u igru:
Code:
SELECT
GuestName
, Redosled AS RedosledPrvog
, 'SINGLE' AS drugi
, NULL AS RedosledDrugog
FROM
(
SELECT
GuestName
, 1 + (SELECT COUNT(*)
FROM Guests AS B
WHERE A.GuestName > B.GuestName) AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS X
WHERE X.Redosled = X.UkupanBroj
AND UkupanBroj % 2 = 1
-- matematicki UkupanBroj MOD 2 = 1
--, moj SQL koristi % za MOD
-- sto nam daje konacno resenje:
Code:
SELECT U.Prvi, U.RedosledPrvog, W.GuestName AS Drugi, U.RedosledDrugog
FROM
-- U = ceo izraz koji smo imali kad smo
-- dodali redne brojeve za desnu polovinu
(
SELECT
A.GuestName AS Prvi
, A.Redosled AS RedosledPrvog
, A.UkupanBroj
, A.Redosled + A.UkupanBroj/2 AS RedosledDrugog
FROM
(
SELECT
GuestName
, 1 + (SELECT COUNT(*)
FROM Guests AS B
WHERE A.GuestName > B.GuestName) AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS A
WHERE Redosled <= UkupanBroj/2
) AS U
JOIN
-- W = izraz koji smo upotrebili za oderdjivanje redosleda
(
SELECT
GuestName
, 1 + (SELECT COUNT(*)
FROM Guests AS B
WHERE A.GuestName > B.GuestName) AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS W
ON U.RedosledDrugog = W.Redosled
UNION
SELECT
GuestName
, Redosled AS RedosledPrvog
, 'SINGLE' AS drugi
, NULL AS RedosledDrugog
FROM
(
SELECT
GuestName
, 1 + (SELECT COUNT(*)
FROM Guests AS B
WHERE A.GuestName > B.GuestName) AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS X
WHERE X.Redosled = X.UkupanBroj
AND UkupanBroj % 2 = 1
Ako dodate jos jednu osobu u tabelu, nestace ovaj deo sa SINGLE i NULL.
Da dodamo jos jednog
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Ludwig','F','Muzika')
Jednostavno, zar ne :-)
I ovo je tek najjednostavniji slucaj. Nisam siguran da li umem da resim exstenzije zadatka. Probacu posle vikenda.
A dotle mozda neko i resi ceo problem.
;-)