SQL-Niepoprawny wynik zapytania

Czesc I czołem mam taki oto problem :

zrobić zestawienie zawierające następujące dane; nazwisko i imię wykładowcy, nazwisko i imię studenta, który u danego wykładowcy otrzymał najwięcej ocen niedostatecznych w roku 2015 i jednocześnie w danym roku nie otrzymał od danego wykładowcy ani jednej oceny dobrej. W zestawieniu uwzględnić tylko tych studentów którzy uzyskali u danego wykładowcy więcej niż 2 oceny niedostateczne.

Struktura bazy danych:

Studenci ( IdStudenta,imie,nazwisko,pesel,idGrupy)
Oceny (IdOceny,IdStudenta,IdWykladowcy,IdPrzedmiotu,IdRodzajOceny,DataOceny,Ocena)
Wykladowcy (IdWykl,Nazwisko,Imie,DataUr,Kobieta)
RodzajeOcen (IdRodzaju,Nazwa,Symbol)
Przedmioty (IdPrzedmiotu,Nazwa,IdKierunkuStudiow)
KierunkiStudiow (IdKierunku,NazwaKierunku)
GrupySzkoleniowe (IdGrupy,Nazwa,IdKierunkuStudiow)

Kod:

;With tmp as
(
Select S.Imie as ImieStudenta,
S.Nazwisko NazwiskoStudenta,
W.Imie as ImieWykladowcy,
W.Nazwisko as NazwiskoWykladowcy,
Ocena  ,
count(O.Ocena) as ile

 
 

from Oceny as O
inner join Studenci as S on O.IdStudenta=S.IdStudenta
inner join Wykladowcy as W on O.IdWykladowcy=W.IdWykladowcy

where O.DataOceny between  '2015-01-01' and '2015-12-31' and Ocena=1.00
group by S.Imie,S.Nazwisko,W.Imie,W.Nazwisko,Ocena,IdGrupy

)

select*  from tmp

where  ile>2

Pytanie , jak wyciągnąc ilość określonych ocen tzn.Ile ten student ma ocen* ndst* , ile ocen bdb itd,Bo gdy to bedzie mozna strzelic sobie dense rank i mamy ranking.I jeszcze jak wyciagnac brak oceny db z danego roku. Dzieki za pomoc.

Nie potrzebujesz dense rank. Zwykłe Count() + CASE WHEN... THEN ... END + GROUP BY + HAVING wystarczy.
Dwa podzapytania również wystarczą (max)

Marcin.Miga napisał(a):

Nie potrzebujesz dense rank. Zwykłe Count() + CASE WHEN... THEN ... END + GROUP BY + HAVING wystarczy.
Dwa podzapytania również wystarczą (max)

To będzie wyglądało jakoś tak? , nie mam dostępu do bazy i nie mam jak tego sprawdzić

;with tmp as
(
Select S.Imie as Imie studenta,S.Nazwisko as Nazwisko Studenta,W.Imie as Imie wykladowcy,W.Nazwisko as nazwisko wykladowcy
,SUM(CASE WHEN ocena = 1 THEN 1 ELSE 0 END) as ileNdst
,SUM(CASE WHEN ocena =5  THEN 1 ELSE 0 END) as ileBdb
count(O.ocena) as ile
from Oceny as O
inner join Studenci as S on O.idStudenta=S.idStudenta
inner join Wykladowcy as W on O.idWykladowcy=W.idWykladowcy
where 
year (dataOceny)=2013
group by S.Imie,S.Nazwisko,W.Imie,W.Nazwisko
)
Select * from tmp
where ileBdb is NULL and ileNdst>1 and ile>2

;WITH oceny_cte
AS ( SELECT result.IdWykladowcy ,
            result.IdStudenta ,
            [1] Ndst
     FROM   (   SELECT   DISTINCT pt.IdWykladowcy ,
                                  pt.IdStudenta ,
                                  pt.[1] ,
                                  pt.[4] ,
                                   ROW_NUMBER() OVER ( PARTITION BY pt.IdWykladowcy
                                                ORDER BY pt.IdWykladowcy ,
                                                         pt.[1] DESC ) AS Rank
                FROM     (   SELECT   o.IdWykladowcy ,
                                      o.IdStudenta ,
                                      o.Ocena ,
                                      COUNT(o.Ocena) Ile
                             FROM     Oceny o
                             WHERE    YEAR(DataOceny) = 2015
                             GROUP BY o.IdWykladowcy ,
                                      o.IdStudenta ,
                                      o.Ocena ) AS result
                PIVOT (   AVG(Ile)
                          FOR  Ocena IN ( [1], [4] )) AS pt
                WHERE    [4] IS NULL
                GROUP BY pt.IdWykladowcy ,
                         pt.IdStudenta ,
                         pt.[1] ,
                         pt.[4] ) AS result
     WHERE  result.Rank = 1
            AND [1] > 2 )

 
SELECT w.Nazwisko ,
       w.Imie ,
       s.Imie ,
       s.Nazwisko ,
       o.Ndst
FROM   oceny_cte o
       INNER JOIN Studenci s ON o.IdStudenta = s.IdStudenta
       INNER JOIN Wykladowcy w ON o.IdWykladowcy = w.IdWykl;

Kurde ten kod trochę wygląda skomplikowanie , ale czy ten który umieściłem wyżej nadaje się ?

Zrób fiidle z przykładowymi danymi. Nikomu (z drobnymi wyjątkami) nie będzie się chciało generować danych do tylu tabel.
A twoje zapytanie... jest prawie dobre.
Mówiłem, że musisz użyć dwóch podzapytań, a masz jedno...

Skup się na pobraniu danych z tabeli oceny reszta to join-y:

with rpt as (
select
    idStudenta
    ,idWykladowcy
    rank() over (partition by idWykladowcy order by ndst desc) r
from (
    select
	   IdStudenta
	   ,IdWykladowcy
	   ,sum(case when ocena=1 then 1 else 0 end)  ndst
    from
	   oceny
    where
	   --w roku 2015
	   DataOceny between  '2015-01-01' and '2015-12-31'
    group by
	   IdStudenta
	   ,IdWykladowcy
    having
	   -- zestawieniu uwzględnić tylko tych studentów którzy uzyskali u danego wykładowcy więcej niż 2 oceny niedostateczne.
	   sum(case when ocena=1 then 1 else 0 end) > 2
	   and
	   -- jednocześnie w danym roku nie otrzymał od danego wykładowcy ani jednej oceny dobrej
	   -- pytanie czy ocena dobra to literalnie 4, czy większa od 1, trzeba doprecyzwoać
	   sum(case when ocena>1 then 1 else 0 end) = 0) dt
where
    --otrzymał najwięcej ocen niedostatecznych
    r =1)

select * from rpt

Marcin.Miga napisał(a):

Zrób fiidle z przykładowymi danymi. Nikomu (z drobnymi wyjątkami) nie będzie się chciało generować danych do tylu tabel.
A twoje zapytanie... jest prawie dobre.
Mówiłem, że musisz użyć dwóch podzapytań, a masz jedno...

a co ma zawierac to drugie zapytanie bo jakos nie mam pomyslu , oraz gdzie ono powinno byc ?

;with tmp as
(
Select S.Imie as Imiestudenta,S.Nazwisko as NazwiskoStudenta,W.Imie as Imiewykladowcy,W.Nazwisko as nazwiskowykladowcy
,SUM(CASE WHEN O.Ocena = 1.00 THEN 1 ELSE 0 END) as ileNdst,
SUM(CASE WHEN O.Ocena =5.00 thEN 1 ELSE 0 END) as ileBdb,
count(*) as ile
from Oceny as O
inner join Studenci as S on O.idStudenta=S.idStudenta
inner join Wykladowcy as W on O.idWykladowcy=W.idWykladowcy
where 
year (dataOceny)=2015
group by S.Imie,S.Nazwisko,W.Imie,W.Nazwisko
)
Select * from tmp

jak go jeszce mozna okielznac ?

Panczo Miałes na myśli cos takiego ?

;with rpt as (
select idStudenta,idWykladowcy,rank() over (partition by idWykladowcy order by ndst desc) r
from (
select S.IdStudenta,W.IdWykladowcy,sum(case when ocena=1 then 1 else 0 end)  ndst
from
Oceny as O
inner join Studenci as S on O.IdStudenta=S.IdStudenta
inner join Wykladowcy as W on O.IdWykladowcy=W.IdWykladowcy
where
--w roku 2015
O.DataOceny between  '2015-01-01' and '2015-12-31'
group by S.IdStudenta,W.IdWykladowcy
having
-- zestawieniu uwzględnić tylko tych studentów którzy uzyskali u danego wykładowcy więcej niż 2 oceny niedostateczne.
sum(case when ocena=1 then 1 else 0 end) > 2and sum(case when ocena>1 then 1 else 0 end) = 0) dt
)

select * 
from rpt 
where r=1

To co napisałem zwraca dokładnie pary wykładowca i student, tobie zostaje tylko join że studentami i wykładowcami

Panczo napisał(a):

To co napisałem zwraca dokładnie pary wykładowca i student, tobie zostaje tylko join że studentami i wykładowcami

Witam wszystkich w nowym dniu,

;with rpt as (
select idStudenta,idWykladowcy,rank() over (partition by idWykladowcy order by ndst desc) r
from (
select S.IdStudenta,W.IdWykladowcy,sum(case when ocena=1 then 1 else 0 end)  ndst
from
Oceny as O
inner join Studenci as S on O.IdStudenta=S.IdStudenta
inner join Wykladowcy as W on O.IdWykladowcy=W.IdWykladowcy
where
--w roku 2015
O.DataOceny between  '2015-01-01' and '2015-12-31'
group by S.IdStudenta,W.IdWykladowcy
having
-- zestawieniu uwzględnić tylko tych studentów którzy uzyskali u danego wykładowcy więcej niż 2 oceny niedostateczne.
sum(case when ocena=1 then 1 else 0 end) > 2and sum(case when ocena>1 then 1 else 0 end) = 0) dt
)

select * 
from rpt  r
INNER JOIN Studenci s ON r.IdStudenta = s.IdStudenta
INNER JOIN Wykladowcy w ON r.IdWykladowcy = w.IdWykladowcy;
where r=1

Panczo o takie joiny ci chodziło ?

Tak, brakuje tylko odstępu: 2and

Panczo napisał(a):

Tak, brakuje tylko odstępu: 2and

;with rpt as (
select idStudenta,idWykladowcy,rank() over (partition by idWykladowcy order by ndst desc) r
from (
select S.IdStudenta,W.IdWykladowcy,sum(case when ocena=1 then 1 else 0 end)  ndst
from
Oceny as O
inner join Studenci as S on O.IdStudenta=S.IdStudenta
inner join Wykladowcy as W on O.IdWykladowcy=W.IdWykladowcy
where
--w roku 2015
O.DataOceny between  '2015-01-01' and '2015-12-31'
group by S.IdStudenta,W.IdWykladowcy
having
-- zestawieniu uwzględnić tylko tych studentów którzy uzyskali u danego wykładowcy więcej niż 2 oceny niedostateczne.
sum(case when ocena=1 then 1 else 0 end) > 2 and sum(case when ocena>1 then 1 else 0 end) = 0) dt
)

select * 
from rpt  r
INNER JOIN Studenci s ON r.IdStudenta = s.IdStudenta
INNER JOIN Wykladowcy w ON r.IdWykladowcy = w.IdWykladowcy;
where r=1

Poprawione jakieś uwagi ?

Wykaż się inwencją i zrób na pure-SQL. Czyli bez okien :)

Marcin.Miga napisał(a):

Wykaż się inwencją i zrób na pure-SQL. Czyli bez okien :)

Dzieki za radę :D a czy masz jakieś zastrzeżenia co do kodu wyżej ?

;with rpt as (
select
    idStudenta
    ,idWykladowcy,
    rank() over (partition by idWykladowcy order by ndst desc) r
from (
    select
       IdStudenta
       ,IdWykladowcy
       ,sum(case when ocena=1 then 1 else 0 end)  ndst
    from
       oceny
    where
       --w roku 2015
       DataOceny between  '2015-01-01' and '2015-12-31' 


    group by
       IdStudenta
       ,IdWykladowcy
    having
       -- zestawieniu uwzględnić tylko tych studentów którzy uzyskali u danego wykładowcy więcej niż 2 oceny niedostateczne.
       sum(case when ocena=1 then 1 else 0 end) > 2
       and
       -- jednocześnie w danym roku nie otrzymał od danego wykładowcy ani jednej oceny dobrej
       -- pytanie czy ocena dobra to literalnie 4, czy większa od 1, trzeba doprecyzwoać
       sum(case when ocena=4 then 1 else 0 end) = 0) dt
	
	  where
    --otrzymał najwięcej ocen niedostatecznych
    r =1)  
	

select * from rpt  

INNER JOIN Studenci s ON rpt.IdStudenta = s.IdStudenta
INNER JOIN Wykladowcy w ON rpt.IdWykladowcy = w.IdWykladowcy;

chłopaki wsadzilem ten kod do bazy . nie wyswietla mi zadnych rekordow , oraz zamiast id studenta , id wykladowcy wyswietla wszystkie pola tzn:nip,pesel,data urodzenia , i jeszcze komunikat invalid column name r . Co z tym ?

Po Złączeniu tabeli Oceny , są rekordy ale z wieloma cechami tzn.Nip,Pesel,Data_Ur , i te r=1 ciagle wyrzuca bląd

;with rpt as (
select
    idStudenta
    ,idWykladowcy,
    dense_rank() over (partition by idWykladowcy order by ndst desc)as r
from (
    select
       IdStudenta
       ,IdWykladowcy
       ,sum(case when ocena=1 then 1 else 0 end) as ndst
    from
       Oceny 

    where
       --w roku 2015
       DataOceny between  '2013-01-01' and '2013-12-31' 

    group by
       IdStudenta
       ,IdWykladowcy
    having
       -- zestawieniu uwzględnić tylko tych studentów którzy uzyskali u danego wykładowcy więcej niż 2 oceny niedostateczne.
       sum(case when ocena=1 then 1 else 0 end) > 2
       and
       -- jednocześnie w danym roku nie otrzymał od danego wykładowcy ani jednej oceny dobrej
       -- pytanie czy ocena dobra to literalnie 4, czy większa od 1, trzeba doprecyzwoać
       sum(case when ocena=5 then 1 else 0 end) = 0) dt

      where
    --otrzymał najwięcej ocen niedostatecznych
    r=1)  

select * from Oceny  as O

INNER JOIN Studenci s ON O.IdStudenta = s.IdStudenta
INNER JOIN Wykladowcy w ON O.IdWykladowcy = w.IdWykladowcy;

Dobra Wysiwtla sie IdStudenta,IdWykladowcy i r w kazdym rekordzie równy 1, Problemem było to że w bazie najmniejsza ocena to 2.00 a nie 1.00

;with rpt as (
select
    idStudenta
    ,idWykladowcy
    ,dense_rank() over (partition by idWykladowcy order by ndst desc)as r
from (
    select
       S.IdStudenta
       ,W.IdWykladowcy
       ,sum(case when ocena=2.00 then 1 else 0 end) as ndst
    
	from Oceny  as O

INNER JOIN Studenci s ON O.IdStudenta = s.IdStudenta
INNER JOIN Wykladowcy w ON O.IdWykladowcy = w.IdWykladowcy

    where
       --w roku 2015
       DataOceny between  '2015-01-01' and '2015-12-31' 

    group by
       S.IdStudenta
       ,W.IdWykladowcy
    having
       -- zestawieniu uwzględnić tylko tych studentów którzy uzyskali u danego wykładowcy więcej niż 2 oceny niedostateczne.
       sum(case when ocena=2.00 then 1 else 0 end) >2
       and
       -- jednocześnie w danym roku nie otrzymał od danego wykładowcy ani jednej oceny dobrej
       -- pytanie czy ocena dobra to literalnie 4, czy większa od 1, trzeba doprecyzwoać
       sum(case when ocena=4.00 then 1 else 0 end) = 0) dt
 
	) 
    

select * from rpt where r=1