Aggregat-SQL-Funktion, um nur die erste von jeder Gruppe zu erfassen

Ich habe 2 Tabellen, eine Tabelle und eine Tabelle Users. Jeder account kann mehrere Benutzer. Ich habe ein Szenario, wo ich ausführen möchten, eine einzige query/join gegen diese beiden Tabellen, aber ich möchte, dass alle Kontodaten (Konto.*) und nur die ersten von user-Daten (insbesondere Ihren Namen).

Anstatt das zu tun, "min" oder "max" auf meinem aggregierten Gruppe, die ich tun wollte "ersten". Aber anscheinend gibt es keinen "Ersten" Aggregat-Funktion in TSQL.

Irgendwelche Vorschläge auf, wie man über die erste dieser Abfrage? Natürlich ist es einfach, um das kartesische Produkt von Konto x-Benutzer:

 SELECT User.Name, Account.* FROM Account, User
 WHERE Account.ID = User.Account_ID

Aber wie könnte ich ungefähr immer nur der erste user von dem Produkt, basierend auf der Reihenfolge Ihrer Benutzer.ID ?

InformationsquelleAutor der Frage Matt | 2009-04-21

  1. 23

    Eher als Gruppierung, der es darum gehen wie dieses...

    select
    *

from account a

join (
    select 
        account_id, 
        row_number() over (order by account_id, id) - 
            rank() over (order by account_id) as row_num from user
     ) first on first.account_id = a.id and first.row_num = 0

    InformationsquelleAutor der Antwort Adam Robinson

  2. 9

    Ich weiß, meine Antwort ist ein bisschen spät, aber das könnte anderen helfen. Es gibt einen Weg, um zu erreichen, First() und Last() in SQL Server, und hier ist es :

    Stuff(Min(Convert(Varchar, DATE_FIELD, 126) + Convert(Varchar, DESIRED_FIELD)), 1, 23, '')

    Verwenden Sie Min (), First() und Max() Last(). Die DATE_FIELD sollte das Datum, das bestimmt, ob es den ersten oder letzten Datensatz. Die DESIRED_FIELD ist das Feld, das Sie möchten, dass der erste oder der Letzte Wert. Was es tut ist :

    1. Fügen Sie das Datum im ISO-format am Anfang der Zeichenfolge (23 Zeichen lang)
    2. Fügen Sie den DESIRED_FIELD Zeichenfolge
    3. Holen Sie sich die MIN/MAX-Wert für das Feld (da es beginnt mit dem Datum, erhalten Sie den ersten oder letzten Datensatz)
    4. Sachen, die concatened string zu entfernen, die ersten 23 Zeichen (das Datum Teil)

    Hier gehen Sie!

    EDIT: ich habe Probleme mit der ersten Formel : wenn die DATE_FIELD hat .000 als Millisekunden ist, gibt SQL Server das Datum als string mit KEINE Millisekunde an alle, damit Sie die ersten 4 Zeichen aus dem DESIRED_FIELD. Ich veränderte einfach das format auf "20" (ohne Millisekunden) und es funktioniert alles Super. Der einzige Nachteil ist, wenn Sie zwei Felder, die erstellt wurden, an den gleichen Sekunden, die Sortierung kann eventuell chaotisch... in welche cas Sie zurückgreifen können, um "126" für das format.

    Stuff(Max(Convert(Varchar, DATE_FIELD, 20) + Convert(Varchar, DESIRED_FIELD)), 1, 19, '')

    EDIT 2 : Meine ursprüngliche Absicht war die Rückkehr der letzten (oder ersten) NICHT-NULL-Zeile. Ich habe gefragt, wie die Rückkehr der letzten oder ersten Zeile, egal, ob es null ist oder nicht. Fügen Sie einfach ein ISTNULL, um die DESIRED_FIELD. Beim verketten von zwei strings mit einem " + " - operator, wenn einer von Ihnen NULL ist, ist das Ergebnis NULL. So verwenden Sie das folgende :

    Stuff(Max(Convert(Varchar, DATE_FIELD, 20) + IsNull(Convert(Varchar, DESIRED_FIELD), '')), 1, 19, '')

    InformationsquelleAutor der Antwort Dominic Goulet

  3. 6
    Select *
From Accounts a
Left Join (
    Select u.*, 
    row_number() over (Partition By u.AccountKey Order By u.UserKey) as Ranking
    From Users u
  ) as UsersRanked
  on UsersRanked.AccountKey = a.AccountKey and UsersRanked.Ranking = 1

    Diese können vereinfacht werden, indem Sie mit der Partition By-Klausel. In der oben genannten, wenn ein Konto drei Benutzer, dann die Unterabfrage zahlen Sie 1,2, und 3, und für eine andere AccountKey wird, setzt es die numnbering. Dies bedeutet, dass für jede eindeutige AccountKey, es wird immer eine 1, und möglicherweise 2,3,4, usw.

    Damit Sie filter auf Ranking=1 zu greifen, die ersten aus jeder Gruppe.

    Dies wird Ihnen eine Zeile pro Konto, und wenn es mindestens einen Benutzer für dieses Konto, dann wird es geben Ihnen die Benutzer mit dem niedrigsten Schlüssel(weil ich einen left join aus, erhalten Sie immer eine Konto-Liste, auch wenn keine Benutzer vorhanden ist). Ersetzen Order By u.UserKey mit einem anderen Feld, wenn Sie bevorzugen, dass der erste Benutzer ausgewählt werden alphabetisch oder einige andere Kriterien.

    InformationsquelleAutor der Antwort AaronLS

  4. 3

    Dem ZEUG Antwort von Dominic Goulet ist glatt. Aber, wenn Ihr DATE_FIELD ist SMALLDATETIME (statt DATETIME), dann die ISO 8601 Länge 19 statt 23 (da SMALLDATETIME hat keine Millisekunden) - so passen Sie die SACHEN, die parameter entsprechend anpassen oder der Rückgabewert aus der Funktion STUFF falsch sein (es fehlen die ersten vier Zeichen).

    InformationsquelleAutor der Antwort mweaver

  5. 2

    Erste und das Letzte sind nicht in Sql Server 2005 oder 2008, aber in Sql Server 2012 gibt es eine First_Value, Last_Value-Funktion. Ich habe versucht zu implementieren, das Aggregat der Ersten und Letzten für Sql Server 2005 und kam zu dem Hindernis, dass der sql-server keine Garantie für die Berechnung der aggregate in einer festgelegten Reihenfolge. (Siehe Attribut SqlUserDefinedAggregateAttribute.IsInvariantToOrder Eigenschaft, die nicht implementiert ist.) Dies könnte daran liegen, dass die query analyser versucht, führen Sie die Berechnung der Summe auf mehrere threads und kombinieren Sie die Ergebnisse, das beschleunigt die Ausführung, aber keine Garantie für eine Reihenfolge, in der Elemente, die aggregiert werden.

    InformationsquelleAutor der Antwort Christoph K

  6. 2

    Können Sie die Verwendung von OUTER APPLY, siehe Dokumentation.

    SELECT User1.Name, Account.* FROM Account
OUTER APPLY 
    (SELECT  TOP 1 Name 
    FROM [User]
    WHERE Account.ID = [User].Account_ID
    ORDER BY Name ASC) User1

    InformationsquelleAutor der Antwort qub1n

  7. 1
    SELECT (SELECT TOP 1 Name 
        FROM User 
        WHERE Account_ID = a.AccountID 
        ORDER BY UserID) [Name],
       a.*
FROM Account a

    InformationsquelleAutor der Antwort Jimmie R. Houts

  8. 1

    Habe ich zudem alle Methoden, die simpelest und Schnellste Methode, dies zu erreichen ist durch die Verwendung von außen - /cross apply

    SELECT u.Name, Account.* FROM Account
OUTER APPLY (SELECT TOP 1 * FROM User WHERE Account.ID = Account_ID ) as u

    CROSS APPLY-funktioniert genau wie die INNER JOIN und liest die Zeilen, in denen beide Tabellen verknüpft sind, während die ÄUßEREN GELTEN Werke wie LEFT OUTER JOIN und holt alle Zeilen aus der linken Tabelle (Konto hier)

    InformationsquelleAutor der Antwort Shaegorath

  9. 0

    Gibt es eine Reihe von Möglichkeiten, dies zu tun, hier eine quick und dirty.

    Select (SELECT TOP 1 U.Name FROM Users U WHERE U.Account_ID = A.ID) AS "Name,
    A.*
FROM Account A

    InformationsquelleAutor der Antwort Mitchel Sellers

  10. 0

    Definieren "Ersten". Was Sie denken, das erste ist ein Zufall, dass normalerweise zu tun hat, mit clustered-index, um, sollte aber nicht herangezogen werden, (Sie können Beispiele ersinnen, die es zu brechen).

    Sind Sie richtig, Sie nicht zu verwenden, MAX() oder MIN(). Während verlockend, betrachten Sie das Szenario, wo Sie den Vornamen und Nachnamen in getrennten Felder. Sie könnten Namen aus verschiedenen Datensätzen.

    Da es klingt wie Sie alle Ihre wirklich Sorge ist, dass Sie genau das bekommen, einen beliebigen Datensatz für jede Gruppe, was Sie tun können, ist nur MIN oder MAX ist ein ID-Feld für den Datensatz, und fügen Sie die Tabelle in die Abfrage auf, dass ID.

    InformationsquelleAutor der Antwort Joel Coehoorn

  11. 0

    (Leicht Off-Topic, aber) ich habe oft ausführen, aggregieren, Abfragen, um die Liste Ausnahme Zusammenfassungen, und dann will ich wissen, WARUM ein Kunde in die Ergebnisse, so verwenden Sie MIN-und MAX-zu geben, 2 semi-zufällige Beispiele, die ich mir ansehen kann in details wie

    SELECT Customer.Id, COUNT(*) AS ProblemCount
      , MIN(Invoice.Id) AS MinInv, MAX(Invoice.Id) AS MaxInv
FROM Customer
INNER JOIN Invoice on Invoice.CustomerId = Customer.Id
WHERE Invoice.SomethingHasGoneWrong=1
GROUP BY Customer.Id

    InformationsquelleAutor der Antwort brewmanz

  12. 0

    Erstellen und kommen mit einer untergeordneten select 'FirstUser", dass gibt den ersten Benutzer für jedes Konto

    SELECT User.Name, Account.* 
FROM Account, User, 
 (select min(user.id) id,account_id from User group by user.account_id) as firstUser
WHERE Account.ID = User.Account_ID 
 and User.id = firstUser.id and Account.ID = firstUser.account_id

    InformationsquelleAutor der Antwort Leon Droog

Schreibe einen Kommentar