Zadanie na 15 minut.

Cześć,

mam 'proste' zadanie niby na 15 minut ale rozwiązanie go zajęło mi 2h i w dodatku nie jestem zadowolony z rozwiązania. Mam wrażenie że dopadła mnie nie moc i że da się to zrobić w prosty sposób :).

Są trzy tabele student, przedmiot, sport, autor. Czyli student ma ulubiony przedmiot, sport i autora książek.

create table
student
(s_id int
,s_nazwa varchar(50));

create table
przedmiot
(p_id int
,s_id int 
,p_nazwa varchar(50));

create table
sport
(sp_id int
,s_id int 
,sp_nazwa varchar(50));

create table
autor
(au_id int
,s_id int 
,au_nazwa varchar(50));

-----------------------

insert into
student
values(1,'Kowalski')
,(2,'Maliniak');

insert into przedmiot
values (10,1,'matematyka')
,(11,1,'fizyka')
,(12,1,'metereologia')
,(13,2,'materiałoznastwo')
,(14,2,'socjologia');

insert into sport
values (20,1,'ping-pong')
,(21,1,'p. nożna')
,(22,1,'surfing')
,(23,1,'narciarstwo');

insert into autor
values (30,2,'Mickiewicz')
,(31,2,'Sienkiewicz')
,(32,2,'Prus')
,(33,2,'Shakespeare');

---

Należy napisać zapytanie które zwróci dane w taki sposób:

Gdyby nie to w jaki sposób są wyświetlane dane, zadanie byłoby proste. Matematyka musi się znaleźć w wierszu razem z ping-pong itd. Normalnie zrobiłbym to już po stronie aplikacji, ale zadanie jest z sql.

Udało mi się to zrobić ale zapytanie jest fatalne:

Select s.s_id, s.s_nazwa, T1.p_nazwa, T2.sp_nazwa, T3.au_nazwa FROM 
 
(
 select 
  s.s_id * 100 + row_number() over (partition by lower(s.s_id) order by s.s_id) as rn,
  s.s_id
  ,p.p_id
  ,p.p_nazwa
 from student as s
  LEFT JOIN PRZEDMIOT AS P
  ON s.s_id = p.s_id
 ) as T1
 
 FULL JOIN (
  select 
  s.s_id * 100 + row_number() over (partition by lower(s.s_id) order by s.s_id) as rn,
  s.s_id, 
  sp.sp_id,
  sp.sp_nazwa
 from student as s
  LEFT JOIN SPORT AS SP
  ON s.s_id = sp.s_id ) as T2
  ON T1.rn = T2.rn

  FULL JOIN 
  (
	  select 
	  s.s_id * 100 + row_number() over (partition by lower(s.s_id) order by s.s_id) as rn,
	  s.s_id, 
	  au.au_id,
	  au.au_nazwa
	 from student as s
	  LEFT JOIN autor AS au
	  ON s.s_id = au.s_id
  ) as T3
  ON T1.rn = T3.rn
  JOIN student s
  ON s.s_id = T1.s_id or s.s_id = T2.s_id or s.s_id = T3.s_id
  order by s_id

Link do sqlfiddle

@Wilktar: A dlaczego pingpong + matematyka w jednym wierszu, a nie ping pong + fizyka?

Wybacz, że nie dam Ci gotowca ale baaardzo dawno tego nie używałem i musiałbym sobie to przypomnieć (to jak z regexp jak się nie używa na co dzień to z głowy samo ucieka). Jednak na ten moment to co widzę, co wydaje mi się, że potrzebujesz to GROUP BY GROUPING SETS aczkolwiek pewnie jakbym zaczął pisać to zrobiłbym to tak jak ty ;)
https://www.sqlservertutorial.net/sql-server-basics/sql-server-grouping-sets/

@Wilktar: E tam fatalne. Fatalny jest pomysł, by coś takiego robić. Implementacja jest już tylko konsekwencją :)

Jak czytałem opis problemu, też pomyślałem o row_number, ale ja bym nieco uprościł (nie potrzebujesz w każdym podzapytaniu dociągać tabeli student). Swoją drogą, tym mnożeniem zamykasz się na możliwość, w której student będzie miał przypisanych więcej, niż 99 obiektów w jednej z tabel.

Swoją drogą, jeśli Maliniakowi dodasz trzy sporty, to wynik Ci się rozjedzie.

Proponuję tak:

SELECT s.s_id
	,s.s_nazwa
	,T1.p_id
	,T1.p_nazwa
	,T2.sp_id
	,T2.sp_nazwa
	,T3.au_id
	,T3.au_nazwa
FROM (
	SELECT s_id * 100 + row_number() OVER (PARTITION BY s_id ORDER BY s_id) AS rn
		,s_id
		,p_id
		,p_nazwa
	FROM PRZEDMIOT AS P
	) AS T1
FULL JOIN (
	SELECT s_id * 100 + row_number() OVER (PARTITION BY s_id ORDER BY s_id) AS rn
		,s_id
		,sp_id
		,sp_nazwa
	FROM SPORT AS SP
	) AS T2 ON T1.rn = T2.rn
FULL JOIN (
	SELECT s_id * 100 + row_number() OVER (PARTITION BY s_id ORDER BY s_id) AS rn
		,s_id
		,au_id
		,au_nazwa
	FROM autor AS au
	) AS T3 ON T3.rn = COALESCE(T1.rn,T2.rn)
JOIN student s ON s.s_id = COALESCE(T1.s_id,T2.s_id,T3.s_id)
ORDER BY s_id

Można bez full joina:

with cPrzedmiot as (
	select 
		row_number() over (partition by s.s_id order by p_id) r
		,s.s_id
		,s_nazwa
		,p_id
		,p_nazwa 
	from 
		student s
		inner join przedmiot t on s.s_id = t.s_id
), cSport as (
	select 
		row_number() over (partition by s.s_id order by sp_id) r
		,s.s_id
		,s_nazwa
		,sp_id
		,sp_nazwa 
	from 
		student s
		inner join sport t on s.s_id = t.s_id
), cAutor as (
	select 
		row_number() over (partition by s.s_id order by au_id) r
		,s.s_id
		,s_nazwa
		,au_id
		,au_nazwa 
	from 
		student s
		inner join autor t on s.s_id = t.s_id
), r as (
	select 
		number r 
		,s_id
	from 
		master..spt_values 
		cross join student
	where 
		type = 'P' 
		and number between 1 and 100
)

Select 
	r.s_id
	,COALESCE(p.s_nazwa,s.s_nazwa,au.s_nazwa) s_nazwa
	,p_id
	,p_nazwa
	,sp_id
	,sp_nazwa
	,au_id
	,au_nazwa
from 
	r
	left join cPrzedmiot p on p.r=r.r and p.s_id = r.s_id
	left join cSport s on s.r=r.r and s.s_id = r.s_id
	left join cAutor au on au.r=r.r and au.s_id = r.s_id
where
	len(COALESCE(p.s_nazwa,s.s_nazwa,au.s_nazwa)) > 0
order by
	r.s_id
	,r.r

http://sqlfiddle.com/#!18/a9dd83/99

Jak oceniacie trudność tego zadania? Kumpel dostał je na junior c# developer i że ma 10-15 minut na zrobienie go. Myślałem że to ja mam jakąś dziurę w pamięci i nie wiem jak to prosto zrobić, ale patrząc po rozwiązaniach to zadanie nie jest proste.

Przede wszystkim, trochę wydaje mi się, że źle przekazałeś/kolega przekazał założenia zadania lub rekruter chciał może trochę wkręcić i zobaczyć jak kandydat wybrnie. Ogólnie zadanie nie jest proste, bo jest bez sensu imo. Baza danych polega na relacji, tymczasem masz tutaj odprawiać jakieś czary żeby wyczarować coś bez relacji, co trochę zaprzecza idei bazy danych. Trochę bez sensu wyświetlać jakiś autorów dla studentów, a obok jakieś zajęcia, czy sporty bo to nijak ma się do siebie.

Miałem dokładnie takie same zadanie rekrutacyjne lata temu. Moja pierwsza odpowiedź to było próba przekonania klienta, że nie tędy droga :), rekruter się zgodził i poprosił o potraktowanie tego jak mentalnej gimnastyki. Poniżej moje rozwiązanie, nie jestem specialistą, więc ciężko mi określić jak mocno wypływają na szybkośc wszystkie te tabele ale przez takie rozbijanie łatwiej mi analizować.

--Tutaj wrzucam wszystko w tymczasówki z dodatkową kolumną RW która zbada mi głębokość dla przedmiotu, sportu i autora
select *, ROW_NUMBER() over (partition by s_id order by  p_nazwa) as RW into #przedmiotRW from #przedmiot
select *, ROW_NUMBER() over (partition by s_id order by  sp_nazwa) as RW into #sportRW  from #sport
select *, ROW_NUMBER() over (partition by s_id order by  au_nazwa)  as RW into #autorRW  from #autor

--tutaj chce wyciągnąć maksymalną głebokość ze wszystkich kategorii
select * into #maxRW  from (
select RW from #przedmiotRW
union
select RW from #sportRW
union
select RW from #autorRW) as t1

--tutaj tą głębokość łączę z tabelą studentów. 
select * 
into #studentmaxRW
from #student 
cross join #maxRW


--tutaj już raczej jasne. do każdego rokordu ze studentów łączymy się po jego id oraz RW
select s.*, s1.p_id, s1.p_nazwa,
s2.sp_id, s2.sp_nazwa,
s3.au_nazwa, s3.au_id
from #studentmaxRW s
left join  #przedmiotRW s1 on s.s_id  = s1.s_id and s.RW  = s1.RW
left join  #sportRW s2 on s.s_id  = s2.s_id and s.RW  = s2.RW
left join  #autorRW s3 on s.s_id  = s3.s_id and s.RW  = s3.RW