Sonntag, Dezember 15, 2019

verwenden Sie sql ausgewählten Werte als Zeile Namen für einen anderen auswählen

Auf einen MSSQL-server, der die Tabellen:

TABLE values {
    int id;
    timestamp date;
    int value;
}

TABLE value_type {
    int value_id; // foreign key on values.id
    text name;
}

Kann ich eine Abfrage wie:

SELECT date, name, value
FROM values
LEFT JOIN value_type ON value_id = id;

und bekommen so etwas wie:

+----------+------+-------+
| date     | name | value |
+----------+------+-------+
| 01.01.10 | foo  |  1.0  |
+----------+------+-------+
| 01.01.10 | bar  |  2.0  |
+----------+------+-------+
| 02.01.10 | bar  |  4.0  |
+----------+------+-------+
| 03.01.10 | foo  |  5.0  |
+----------+------+-------+

Was ist der effizienteste Weg, um die Datenbank abzufragen, wenn ich mag würde, um die Daten so?

+----------+------+-------+
| date     | foo  | bar   |
+----------+------+-------+
| 01.01.10 | 1.0  |  2.0  |
+----------+------+-------+
| 02.01.10 | NULL |  4.0  |
+----------+------+-------+
| 03.01.10 | 5.0  |  NULL |
+----------+------+-------+

Die Struktur der Datenbank ist viel komplizierter, und leider kann ich es nicht ändern.

BEARBEITEN: Die Lösung sollte nicht verwendet foo und bar fest codierte Werte in der Abfrage.

  • Welche version von SQL Server verwenden Sie?
  • Es ist auf einem MS SQL Server 2008
InformationsquelleAutor chris | 2011-01-24

4 Kommentare

  1. 2

    Für SQL Server 2005+ können Sie dynamisches SQL verwenden und PIVOT.

    DECLARE @ValuesNames NVARCHAR(4000), @Query NVARCHAR(MAX)
    SET @ValuesNames = ''
    
    SELECT @ValuesNames = @ValuesNames + QUOTENAME([name]) + ','
    FROM [values] A
    INNER JOIN value_type B
    ON B.value_id = A.id
    GROUP BY [name]
    ORDER BY [name]
    
    SET @ValuesNames = LEFT(@ValuesNames,LEN(@ValuesNames)-1)
    
    SET @Query = '
    SELECT [date], '[email protected]ValuesNames+'
    FROM (  SELECT [date], [name], value
            FROM [values]
            LEFT JOIN value_type ON value_id = id) A
    PIVOT(SUM(value) FOR [name] IN ('[email protected]ValuesNames+')) AS PT
    '
    EXEC sp_executesql @Query
    • Schön, ich werde es einmal versuchen.
    • Lassen Sie mich wissen, wie es geht
    • Ich scheine nicht ausgeführt werden, WÄHLEN Sie @foo = @foo + Feld VON …, WÄHLEN Sie @foo = Feld AUS … funktioniert, aber natürlich werde ich nur die Letzte Zeile. Irgendwelche Ideen?
    • Upps, da waren einige NULL-Werte in der Tabelle, behoben. Ich nehme dir das zeigen die Verwendung von PIVOT.
  2. 2

    Könnte man group by Datum:

    SELECT  Date
    ,       max(case when vt.name = 'foo' then v.value end) as Foo
    ,       max(case when vt.name = 'bar' then v.value end) as Bar
    FROM Values v
    LEFT JOIN 
            value_type vt
    ON      vt.value_id = v.id
    group by
            Date

    Durch die Art und Weise, der foreign key, die ungewöhnlich scheint. Ich würde erwarten, dass ein value_type Spalte in der values Tabelle, die nicht eine value_id Spalte in der values_type Tisch! Es gibt viel mehr Werte, als Werte-Typen, richtig?

    EDIT: Es gibt keine Möglichkeit in SQL zu generieren, die eine variable Anzahl von Spalten, so werden Sie zu dem resort, meta-SQL oder dynamic SQL. Hier ist ein Beispiel:

    create table [values] (id int, date datetime, value float)
    insert [values] values (1, '2010-01-01', 1.0)
    insert [values] values (2, '2010-01-01', 2.0)
    insert [values] values (3, '2010-01-02', 4.0)
    insert [values] values (4, '2010-01-03', 5.0)
    create table value_types (value_id int, name varchar(max))
    insert value_types values (1,'foo'), (2,'bar'), (3,'bar'), (4,'foo')
    
    declare @sql varchar(max)
    set @sql = ''
    select  @sql = @sql + ',       max(case when vt.name = ''' + 
                       name + ''' then v.value end) as ' + name + CHAR(13) + CHAR(10)
    from    value_types
    group by
            name
    
    set @sql = 'SELECT  Date
    ' + @sql + 
    'FROM   [values] v
    LEFT JOIN 
            value_types vt
    ON      vt.value_id = v.id
    group by
            Date'
    
    exec (@sql)

    Diese Drucke:

    Date        bar     foo
    ----------- ------- -------
    2010-01-01  2       1
    2010-01-02  4       NULL
    2010-01-03  NULL    5
    • Leider hartzucodieren die Feldnamen in dem Beispiel ist keine option. Ich bearbeitet meine Frage.
    • Das sieht toll aus! Ich werde versuchen, es morgen und werde es akzeptieren, wenn ich kann, damit es funktioniert.
    • Ich scheine nicht ausgeführt werden, WÄHLEN Sie @foo = @foo + Feld VON …, WÄHLEN Sie @foo = Feld AUS … funktioniert, aber natürlich werde ich nur die Letzte Zeile. Irgendwelche Ideen?
    • nevermind, geschrieben, eine separate Frage und habe es funktioniert.
  3. 0

    ungetestet: definieren Sie eine Ansicht, die Tabellen zu verknüpfen, auf die pk-fk-pair-Mädchen, und dann versuchen Sie so etwas wie dieses:

    Select date, 
    
    case name when "foo" then value else null end as foo, 
    
    case name when "base" then value else null end as bar 
    
    from mytable  
    
    order by date

    Hier müssen Sie wissen, den Bereich der möglichen Werte im Voraus.

    Es flexibler ZU machen, Sie bauen konnte ein dynamisches case-Anweisung aus der unterschiedlichen Werte von „name“ in einer gespeicherten Prozedur und den Aufruf von exec() auf dieses string.

    • Bitte siehe meinen letzten edit: harcoding foo und bar ist keine option.
    • Ich würde erstellen eine gespeicherte Prozedur Durchlaufen alle eindeutigen Werte der Spalte „NAMEN“, („create #temptable select distinct name from Messwerte“) erstellen Sie einen langen sql-string in einer cursor-Schleife, schließlich führen Sie die Abfrage mithilfe von exec(). vielleicht eine Suche nach „Kreuztabelle“ und „sql“ wird Ihnen helfen, mehr Antworten auf dieser Seite. das problem hat eine ganze Reihe von Variationen.
    • konnte Sie Bearbeiten Ihre Antwort und zeigen, wie Sie dies tun? Ich bin kein SQL-Experte und verstehe nicht, Ihren Kommentar 🙁
    • Benutzer lamak hat die Frage bereits beantwortet, mit eine sehr ähnliche (und bessere) Strategie, die ich vorgeschlagen habe,. Ein paar Anweisungen in einem batch (Sie können wickeln Sie es in eine create procedure-stmt), bauen Sie eine lange Zeichenfolge @Abfrage, dann sp_executesql( @query )
  4. 0

    Aktualisiert auf Basis der neuen Anforderungen:
    Sie müssen entweder dynamisch generieren der SQL-Anweisung für die Abfrage oder wenn Sie laden Sie es in Ihre Anwendung könnten Sie transformieren die Daten aus Ihrem ursprünglichen Abfrage oder eine ähnliche Abfrage.

    • Sieht aus wie Sie denken, dass jeder Wert eine Referenz auf ein value_type, aber es ist anders herum: jeder value_type hat eine Referenz auf den Wert, den es gehört. So müssen Sie eine Verknüpfung zu dem Wert geben Sie beide Teile des full join.
    • Bitte siehe meinen letzten edit: harcoding foo und bar ist keine option.
    • in diesem Fall sind Sie gehen zu müssen, um dynamisch erstellen Sie den Kontoauszug.

Kostenlose Online-Tests

Letzte Fragen

Tun ItemView löst Blase?

Ich habe eine CompositeView für eine Tabelle. Ich habe Trigger-set in der Kind-ItemView für jede Zeile... var TableRow = Marionette.ItemView.extend({ tagName:...

Wie kann ich untersuchen, WCF was 400 bad request über GET?

Die folgenden WCF-endpoint funktioniert gut mit dem WCF test client: AssetList ListFlaggedAssets(short processCode, string platform, string endpoint = "null", string portalId = "null", int...

Bei der Verwendung von UUIDs, sollte ich auch mit AUTO_INCREMENT?

Wir bauen eine neue web-app, die eine offline-iPad - /Android-app-version auf einer Reihe von lokalen Geräten, die Einsätze mit neuen Daten. Als solche benötigen...

Actionscript-Objekt, das verschiedene Eigenschaften

Wie kann ich die Anzahl der Eigenschaften in einer generischen Actionscript-Objekt? (Wie die Array-Länge) InformationsquelleAutor Fragsworth | 2011-01-15

Wie plot mehrere Graphen und nutzen Sie die Navigations-Taste im [matplotlib]

Die neueste version von matplotlib erstellt automatisch Navigations-buttons unter den graph. Aber die Beispiele, die ich finden alles im Internet zeigen, wie erstellen Sie...