Mam stół, który mierzy wydajność ucznia student w moim dB jak poniżej:

ID  TestDate    PerformanceStatus (PS)
1   15/03/2016  0
1   01/04/2016  2
1   05/05/2016  1
1   07/06/2016  1
2   15/03/2016  0
2   01/04/2016  2
2   05/05/2016  1
2   07/06/2016  3
2   23/08/2016  1

Chcę zaktualizować mój stół, aby mieć nową kolumnę poprzedniegoFormancestatus. To poprzednieFormanSanStus jest obliczany na podstawie monitorowanego programu PerformanceStatus, jak poniżej: UWAGA: Jeśli nie ma programu PerformanceTatus nagrany przed testowaniem, chcę zrobić PreviousPerformanceStatus = PerformanceStatus

ID  TestDate    PS  PreviousPerformanceStatus
1   15/03/2016  0   0
1   01/04/2016  2   0
1   05/05/2016  1   2
1   07/06/2016  1   1
2   15/03/2016  0   0
2   01/04/2016  2   0
2   05/05/2016  1   2
2   07/06/2016  3   1
2   23/08/2016  1   3

Jak zaktualizować swoją tabelę SQL? Powinienem użyć dołączenia? Dzięki.

2
Saber Alex 16 luty 2017, 19:27

2 odpowiedzi

Najlepsza odpowiedź

Zakładając, że kolumna testdate jest DATE DataType (a nie a Varchar)

I zakładając, że krotka (id,testdate) jest wyjątkowa w student

Możemy użyć skorelowanego podzapytania na liście Wybierz. Jako przykład:

 SELECT t.id
      , t.testdate
      , t.performancestatus
      , ( SELECT p.performancestatus
            FROM student p
           WHERE p.id = t.id
             AND p.testdate < t.testdate
           ORDER BY p.testdate DESC
           LIMIT 1
        ) AS previousperformancestatus
 FROM student t
ORDER BY t.id, t.testdate

Po potwierdzeniu, że instrukcja wyboru daje nam wynik, którego szukamy, możemy konwertować to do oświadczenia aktualizacji. Albo jako widok inline, albo bezpośrednio korzysta z skorelowanego podzapytania.

UPDATE student t
   SET t.previousperformancestatus
       = ( SELECT p.performancestatus
            FROM student p
           WHERE p.id = t.id
             AND p.testdate < t.testdate
           ORDER BY p.testdate DESC
           LIMIT 1
        )

Jeśli testdate jest nie DATE DataType, albo jest nie przechowywane w formacie kanonicznym, a następnie "Mniej niż" porównanie nie gwarantuje ograniczenia wiersze do "wcześniejszego" testadatu. A "Zamówienie" nie jest gwarantowane, aby przywrócić najnowszy "wcześniejszy" testDate.

W przypadku "pierwszego" testadatu, gdy nie ma wcześniejszego testowania, podzapytanie powróci NULL. Możemy użyć wyrażenia do konwersji wartości zerowej na 0. MOŻEMY ciąć podzewności w funkcji, IFNULL( <subquery> ,0)

2
spencer7593 16 luty 2017, 16:59

Wybór proponowany przez Spencera7593 będzie działać, ale jest dość niezdrowy (w moim przypadku zajęło jedną minutę, aby zaktualizować trzy kolumny). Aktualizacja nie powiedzie się z błędem informującym, że nie możesz zaktualizować tabeli, której używasz w klauzuli z ustawionego celu.

enter image description here

Innym, o wiele bardziej skuteczny sposób, aby użyć CTE z LGD () i chwytać zaktualizowane wartości z niego. Jednak potrzebny jest unikalny klucz do użycia jako klucz dołączenia między CTE a tabelą, jeśli nie ma innego prostego lub kompozytowego unikalnego klucza.

-- Create the table as per question
drop table if exists student;

create table if not exists student (
pk int auto_increment,
id int not null,
TestDate date not null,
PerformanceStatus int not null,
PreviousPerformanceStatus int null default null,
primary key (pk)
) engine=innodb;

insert into student(id, TestDate, PerformanceStatus, PreviousPerformanceStatus)
values  (1, '2016-03-15', 0, null),
        (1, '2016-04-01', 2, null),
        (1, '2016-05-05', 1, null),
        (1, '2016-06-07', 1, null),
        (2, '2016-03-15', 0, null),
        (2, '2016-04-01', 2, null),
        (2, '2016-05-05', 1, null),
        (2, '2016-06-07', 3, null),
        (2, '2016-08-23', 1, null);


-- Update PreviousPerformanceStatus using lag()
with p as
(
    select pk, id, testdate, performancestatus, 
    LAG(performancestatus, 1, 0) OVER (
        PARTITION BY id
        ORDER BY id, testdate asc
    ) as PreviousPerformanceStatus
    from student
)
update student t
inner join p 
on p.pk = t.pk  
set     
t.PreviousPerformanceStatus = p.PreviousPerformanceStatus;

Możesz zastąpić trzeci parametr opóźnienia () z null zamiast zera. Odkryłem, że to rozwiązanie jest najbardziej wydajne wśród kilku innych rozwiązań roboczych.

0
Misa 23 maj 2019, 09:47