tables - update parte del campo sql




Ottieni la prima riga di ogni gruppo (11)

È stato verificato in SQLite che è possibile utilizzare la seguente query semplice con GROUP BY

SELECT MAX(DateCreated), *
FROM DocumentStatusLogs
GROUP BY DocumentID

Qui MAX aiuta a ottenere il massimo DateCreated DA ogni gruppo.

Ma sembra che MySQL non associ * -colonne con il valore di max DateCreated :(

Ho una tabella che voglio ottenere l'ultima voce per ogni gruppo. Ecco la tabella:

Tabella DocumentStatusLogs

|ID| DocumentID | Status | DateCreated |
| 2| 1          | S1     | 7/29/2011   |
| 3| 1          | S2     | 7/30/2011   |
| 6| 1          | S1     | 8/02/2011   |
| 1| 2          | S1     | 7/28/2011   |
| 4| 2          | S2     | 7/30/2011   |
| 5| 2          | S3     | 8/01/2011   |
| 6| 3          | S1     | 8/02/2011   |

La tabella verrà raggruppata per DateCreated DocumentID e ordinata per DateCreated in ordine decrescente. Per ogni DocumentID , voglio ottenere lo stato più recente.

La mia uscita preferita:

| DocumentID | Status | DateCreated |
| 1          | S1     | 8/02/2011   |
| 2          | S3     | 8/01/2011   |
| 3          | S1     | 8/02/2011   |
  • Esiste una funzione aggregata per ottenere solo la parte superiore di ogni gruppo? Vedi pseudo-code GetOnlyTheTop seguito:

    SELECT
      DocumentID,
      GetOnlyTheTop(Status),
      GetOnlyTheTop(DateCreated)
    FROM DocumentStatusLogs
    GROUP BY DocumentID
    ORDER BY DateCreated DESC
    
  • Se tale funzione non esiste, c'è un modo per raggiungere l'output che voglio?

  • O in primo luogo, ciò potrebbe essere causato da un database non normalizzato? Sto pensando, dal momento che quello che sto cercando è solo una riga, questo status dovrebbe trovarsi anche nella tabella principale?

Si prega di consultare la tabella genitore per ulteriori informazioni:

Tabella dei Documents correnti

| DocumentID | Title  | Content  | DateCreated |
| 1          | TitleA | ...      | ...         |
| 2          | TitleB | ...      | ...         |
| 3          | TitleC | ...      | ...         |

La tabella genitore dovrebbe essere così in modo da poter accedere facilmente al suo stato?

| DocumentID | Title  | Content  | DateCreated | CurrentStatus |
| 1          | TitleA | ...      | ...         | s1            |
| 2          | TitleB | ...      | ...         | s3            |
| 3          | TitleC | ...      | ...         | s1            |

AGGIORNAMENTO Ho appena imparato come usare "apply" che rende più facile affrontare tali problemi.


Ho appena imparato a usare cross apply . Ecco come usarlo in questo scenario:

 select d.DocumentID, ds.Status, ds.DateCreated 
 from Documents as d 
 cross apply 
     (select top 1 Status, DateCreated
      from DocumentStatusLogs 
      where DocumentID = d.DocumentId
      order by DateCreated desc) as ds

Il mio codice per selezionare la prima parte di ogni gruppo

select a.* from #DocumentStatusLogs a where 
 datecreated in( select top 1 datecreated from #DocumentStatusLogs b
where 
a.documentid = b.documentid
order by datecreated desc
)

Negli scenari in cui si desidera evitare l'uso di row_count (), è anche possibile utilizzare un join di sinistra:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
WHERE ds.ID NOT IN (
    SELECT filter.ID 
    FROM DocumentStatusLogs filter
    WHERE ds.DocumentID = filter.DocumentID
        AND ds.DateCreated < filter.DateCreated)

Per lo schema di esempio, è possibile utilizzare anche "not in subquery", che generalmente viene compilato con lo stesso output del join sinistro:

        SELECT [DocumentID], 
        [tmpRez].value('/x[2]','varchar(20)') as [Status],
 [tmpRez].value('/x[3]','datetime') as [DateCreated] 
FROM (
        SELECT [DocumentID],
    cast('<x>'+max(cast([ID] as varchar(10))+'</x><x>'+[Status]+'</x><x>'
    +cast([DateCreated] as varchar(20)))+'</x>' as XML) as [tmpRez]
        FROM DocumentStatusLogs
        GROUP by DocumentID) as [tmpQry]

Nota, il pattern di subquery non funzionerebbe se la tabella non avesse almeno una chiave / vincolo / indice univoco a singola colonna, in questo caso la chiave primaria "Id".

Entrambe queste query tendono ad essere più "costose" della query row_count () (misurata da Query Analyzer). Tuttavia, potresti incontrare scenari in cui restituiscono risultati più velocemente o abilitano altre ottimizzazioni.


Questa è una delle domande più facilmente reperibili sull'argomento, quindi volevo dare una risposta moderna (sia per il mio riferimento che per aiutare gli altri). Usando over e first value puoi fare un lavoro breve della query sopra riportata:

select distinct DocumentID
  , first_value(status) over (partition by DocumentID order by DateCreated Desc) as Status
  , first_value(DateCreated) over (partition by DocumentID order by DateCreated Desc) as DateCreated
From DocumentStatusLogs

Questo dovrebbe funzionare in SQL Server 2008 e versioni successive. Il primo valore può essere pensato come un modo per ottenere il primo top selezionato quando si usa una clausola over. Over consente di raggruppare l'elenco di selezione in modo che invece di scrivere sottoquery annidate (come fanno molte delle risposte esistenti), lo fa in un modo più leggibile. Spero che questo ti aiuti.


Questo è il TSQL più vaniglia che riesco a trovare

    SELECT * FROM DocumentStatusLogs D1 JOIN
    (
      SELECT
        DocumentID,MAX(DateCreated) AS MaxDate
      FROM
        DocumentStatusLogs
      GROUP BY
        DocumentID
    ) D2
    ON
      D2.DocumentID=D1.DocumentID
    AND
      D2.MaxDate=D1.DateCreated

Se sei preoccupato per le prestazioni, puoi farlo anche con MAX ():

SELECT *
FROM DocumentStatusLogs D
WHERE DateCreated = (SELECT MAX(DateCreated) FROM DocumentStatusLogs WHERE ID = D.ID)

ROW_NUMBER () richiede una sorta di tutte le righe nell'istruzione SELECT, mentre MAX no. Dovrebbe velocizzare drasticamente la tua richiesta.


So che questo è un thread vecchio, ma le soluzioni TOP 1 WITH TIES sono piuttosto carine e potrebbero essere utili per leggere le soluzioni.

select top 1 with ties
   DocumentID
  ,Status
  ,DateCreated
from DocumentStatusLogs
order by row_number() over (partition by DocumentID order by DateCreated desc)

Maggiori informazioni sulla clausola TOP possono essere trovate here .


;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

Se si prevedono 2 voci al giorno, questo verrà scelto arbitrariamente. Per ottenere entrambe le voci per un giorno, utilizzare invece DENSE_RANK

Per quanto normalizzato o no, dipende se si desidera:

  • mantenere lo stato in 2 posizioni
  • preservare la cronologia dello stato
  • ...

Così com'è, si conserva la cronologia dello stato. Se vuoi anche l'ultimo stato nella tabella genitore (che è denormalizzazione) avresti bisogno di un trigger per mantenere lo "stato" nel genitore. o rilasciare questa tabella della storia dello stato.


SELECT * FROM
DocumentStatusLogs JOIN (
  SELECT DocumentID, MAX(DateCreated) DateCreated
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ) max_date USING (DocumentID, DateCreated)

Quale server di database? Questo codice non funziona su tutti loro.

Per quanto riguarda la seconda metà della tua domanda, mi sembra ragionevole includere lo stato come colonna. È possibile lasciare DocumentStatusLogs come un registro, ma memorizzare ancora le ultime informazioni nella tabella principale.

BTW, se hai già la colonna DateCreated nella tabella Documenti puoi semplicemente unire DocumentStatusLogs usando quello (fintanto che DateCreated è univoco in DocumentStatusLogs ).

Modifica: MsSQL non supporta USING, quindi modificalo in:

ON DocumentStatusLogs.DocumentID = max_date.DocumentID AND DocumentStatusLogs.DateCreated = max_date.DateCreated

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
left join DocumentStatusLogs filter 
    ON ds.DocumentID = filter.DocumentID
    -- Match any row that has another row that was created after it.
    AND ds.DateCreated < filter.DateCreated
-- then filter out any rows that matched 
where filter.DocumentID is null 

Se si desidera restituire solo l'ordine di un documento recente da DateCreated, verrà restituito solo il primo documento di DocumentID





greatest-n-per-group