Verwenden von SQL zum Abfragen von Daten mithilfe der Dataverse-Web-API

Sie können die Structured Query Language (SQL) verwenden, um Daten aus Microsoft Dataverse mithilfe der Web-API abzufragen. Übergeben Sie SQL-Befehle SELECT über die sql Abfrageoption, indem Sie den Entitätssatznamen der Tabelle verwenden, die Sie abfragen möchten.

So verwenden Sie eine SQL-Abfrage wie folgt:

SELECT name 
FROM account AS a 
WHERE a.name LIKE 'Fourth Coffee'

Legen Sie den URL-codierten Wert der Abfrage für die Abfrageoption sql auf eine Entityset-Ressource fest, die der Basistabelle Ihrer Abfrage entspricht. In diesem Fall lautet accountsder Entitätssatzname .

Anfrage

GET [Organization URI]/api/data/v9.2/accounts?sql=SELECT%20name%20%0D%0AFROM%20account%20AS%20a%20%0D%0AWHERE%20a.name%20LIKE%20'Fourth%20Coffee' HTTP/1.1
Authorization: Bearer [REDACTED]
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.include-annotations="*"
Accept: application/json

Antwort

Die Antwort ähnelt dem, was Sie mit der entsprechenden OData-Abfrage erhalten:

/accounts?$select=name&$filter=contains(name,'Fourth Coffee')

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
Preference-Applied: odata.include-annotations="*"

{
   "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,accountid)",
   "@Microsoft.Dynamics.CRM.totalrecordcount": -1,
   "@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded": false,
   "@Microsoft.Dynamics.CRM.globalmetadataversion": "173309522",
   "value": [
      {
         "@odata.etag": "W/\"173325408\"",
         "accountid": "0bdd4472-981d-f111-8341-0022482aa957",
         "name": "Fourth Coffee"
      }
   ]
}

Spalten auswählen

Listen Sie bestimmte Spaltennamen in der SELECT Klausel auf, getrennt durch Kommas. Verwenden Sie Tabellenaliasen, um Spaltenverweise zu qualifizieren und Spaltenaliasen zum Umbenennen von Ausgabefeldern zu verwenden.

Im folgenden Beispiel werden drei Spalten aus der account Tabelle mit einem Tabellenalias ausgewählt:

SELECT a.name, a.telephone1, a.websiteurl
FROM account AS a

Verwenden Sie Spaltenaliasen, um Ausgabefelder umzubenennen:

SELECT a.name AS account_name, a.telephone1 AS phone
FROM account AS a

Die zurückgegebenen Datensätze sehen wie in diesem Beispiel aus:

{
   "@odata.etag": "W/\"174033617\"",
   "accountid": "667ec6df-4a22-f111-8342-0022482aa3a2",
   "account_name@OData.Community.Display.V1.AttributeName": "name",
   "account_name": "Wide World Importers",
   "phone@OData.Community.Display.V1.AttributeName": "telephone1",
   "phone": "(555) 100-0006"
}

Tabellen verbinden

Verwenden Sie INNER JOIN oder LEFT JOIN, um Zeilen aus zwei oder mehr Tabellen zu kombinieren. Verknüpfung über eine zugehörige Spalte, typischerweise einen Primärschlüssel mit einem Fremdschlüssel.

Im folgenden Beispiel werden Konten und ihre zugehörigen Kontakte mithilfe einer inneren Verknüpfung zurückgegeben:

SELECT a.name, c.fullname, c.emailaddress1
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid

Wird LEFT JOIN verwendet, um Konten einzuschließen, die keine verwandten Kontakte haben:

SELECT a.name, c.fullname
FROM account AS a
LEFT JOIN contact AS c ON a.accountid = c.parentcustomerid

Sie können mehr als zwei Tabellen verknüpfen. Im folgenden Beispiel werden Konten, Kontakte und Verkaufschancen verknüpft:

SELECT a.name, c.fullname, o.name AS opportunity_name
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid
INNER JOIN opportunity AS o ON a.accountid = o.customerid

Verwenden Sie einen Self-Join, um Zeilen innerhalb derselben Tabelle in Beziehung zu setzen. Im folgenden Beispiel werden Konten und ihre übergeordneten Konten gefunden:

SELECT child.name AS account, parent.name AS parent_account
FROM account AS child
INNER JOIN account AS parent ON child.parentaccountid = parent.accountid

Zusätzliche ON-Filter

JOIN ... ON klausel muss einen = Operator zwischen Spalten aus den beiden Tabellen verwenden. Alle zusätzlichen Filter müssen mit dieser Gleichheitsbedingung mithilfe des AND Operators kombiniert werden und auf die verknüpfte Tabelle angewendet werden.

-- Not supported, must join on columns from the two tables
-- SELECT a.name, c.fullname, c.emailaddress1
-- FROM account AS a
-- INNER JOIN contact AS c ON c.emailaddress1 LIKE 'B%'

-- Not supported, must use "="
-- SELECT a.name, c.fullname, c.emailaddress1
-- FROM account AS a
-- INNER JOIN contact AS c ON a.accountid <> c.parentcustomerid

-- Not supported, must combine additional filters using AND
-- SELECT a.name, c.fullname, c.emailaddress1
-- FROM account AS a
-- INNER JOIN contact AS c ON a.accountid = c.parentcustomerid OR c.emailaddress1 LIKE 'B%'

-- Not supported, additional filters must be on the joined table
-- SELECT a.name, c.fullname, c.emailaddress1
-- FROM account AS a
-- INNER JOIN contact AS c ON a.accountid = c.parentcustomerid AND a.name LIKE 'A%'

-- This example works because it has a filter on the joined contact table fullname column:
SELECT a.name, c.fullname, c.emailaddress1
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid AND c.fullname LIKE 'A%'

Sie können zusätzliche Bedingungen mithilfe eines geschachtelten OR-Operators miteinander kombinieren, sofern der gesamte zusätzliche Filter mit der Spaltengleichheit über AND kombiniert wird:

SELECT a.name, c.fullname, c.emailaddress1
FROM account AS a
INNER JOIN contact AS c
   ON a.accountid = c.parentcustomerid
   AND (c.fullname LIKE 'A%' OR c.emailaddress1 LIKE 'B%')

Bestellzeilen

Verwenden Sie ORDER BY, um die Ergebnisse nach einer oder mehreren Spalten zu sortieren. Geben Sie ASC (aufsteigend, standard) oder DESC (absteigend) an.

Im folgenden Beispiel werden Konten zurückgegeben, die nach Namen sortiert sind:

SELECT name, telephone1
FROM account
ORDER BY name ASC

Sortieren nach mehreren Spalten:

SELECT name, createdon
FROM account
ORDER BY name ASC, createdon DESC

Zeilen filtern

Verwenden Sie eine WHERE Klausel, um Zeilen nach einer oder mehreren Bedingungen zu filtern. Die WHERE Klausel muss eine Spalte mit einem Konstantenwert vergleichen.

Vergleichsoperatoren

Die unterstützten Vergleichsoperatoren sind: =, , !=, <>, <, >, , und <=>=.

SELECT name, statecode
FROM account
WHERE statecode = 0

Verwenden Sie != oder <>, um Zeilen auszuschließen.

SELECT name, statecode
FROM account
WHERE statecode <> 1

Verwenden Sie <, >, , <=oder >= für Bereichsvergleiche.

SELECT name
FROM account
WHERE name > 'M'
ORDER BY name

Logische Operatoren

Kombinieren Von Bedingungen mithilfe AND und OR. Verwenden Sie Klammern, um die Auswertungsreihenfolge zu steuern.

SELECT name, telephone1
FROM account
WHERE statecode = 0 AND telephone1 IS NOT NULL

SELECT name
FROM account
WHERE (name = 'Contoso' OR name = 'Fabrikam')

SELECT name, telephone1
FROM account
WHERE (statecode = 0 OR statecode = 1) AND telephone1 IS NOT NULL

LIKE-Muster

Verwenden Sie LIKE, um Muster in Zeichenfolgen abzugleichen. Die unterstützten Wildcards sind:

SELECT name FROM account WHERE name LIKE 'Fourth%'

Verwenden Sie NOT LIKE, um übereinstimmende Zeilen auszuschließen:

SELECT name FROM account WHERE name NOT LIKE '%test%'

IN und NICHT IN

Wird IN verwendet, um einen beliebigen Wert in einer Liste abzugleichen:

SELECT name
FROM account
WHERE name IN ('Contoso', 'Fabrikam', 'Fourth Coffee')

Wird NOT IN verwendet, um Werte auszuschließen:

SELECT name
FROM account
WHERE name NOT IN ('Contoso', 'Fabrikam')

BETWEEN

Dient BETWEEN zum Filtern von Zeilen innerhalb eines inklusiven Bereichs.

SELECT name
FROM account
WHERE name BETWEEN 'A' AND 'B'

IS NULL und IS NOT NULL

Verwenden Sie IS NULL, um Zeilen zu finden, bei denen eine Spalte keinen Wert hat, und verwenden Sie IS NOT NULL, um Zeilen zu finden, bei denen eine Spalte einen Wert hat.

SELECT name
FROM account
WHERE telephone1 IS NULL

SELECT name, telephone1
FROM account
WHERE telephone1 IS NOT NULL

DISTINCT

Wird DISTINCT verwendet, um eindeutige Werte zurückzugeben.

SELECT DISTINCT a.address1_city
FROM account AS a

Verwenden von DATEADD- und GETUTCDATE-Funktionen

Verwenden Sie die DATEADD Funktion, um Zeilen für einen konstanten Datumsbereich zurückzugeben:

-- Do not pass column values to functions
-- SELECT a.name
-- FROM account a
-- WHERE DATEADD(day, 3, a.createdon) >= '2023-01-01 17:00:00' (not supported)

SELECT a.name
FROM account a
WHERE a.createdon >= DATEADD(day, -3, '2023-01-01 17:00:00')

Verwenden Sie die GETUTCDATE Funktion, um den Bereich relativ zur aktuellen Uhrzeit zu erstellen:

-- Do not pass column values to functions
-- SELECT a.name
-- FROM account a
-- WHERE DATEADD(day, 3, a.createdon) >= GETUTCDATE() (not supported)

SELECT a.name
FROM account a
WHERE a.createdon >= DATEADD(day, -3, GETUTCDATE())

Nicht unterstützte WHERE-Klauselfunktionen

Die WHERE Klausel unterstützt nicht die folgenden Features:

• Unterabfragen: WHERE accountid IN (SELECT accountid FROM account).
• EXISTS und NOT EXISTS: Diese Operatoren geben einen Fehler zurück.
• Literal-zu-Literal-Vergleiche: WHERE 1=1 und WHERE 1=0.
• Spalten-zu-Spalten-Vergleiche: WHERE a.modifiedon > a.createdon.
• Ausdrücke: WHERE a.revenue > 500.0 + 125.0.
• Funktionen, die auf Spaltenwerte angewendet werden: WHERE DATEADD(day, 3, a.createdon) >= GETUTCDATE().
• In diesem Dokument nicht aufgeführte Funktionen.

Seitenergebnisse

Verwenden Sie die OData-Paginierung mit dem Anforderungsheader Prefer: odata.maxpagesize und der Annotation @odata.nextLink. Weitere Informationen zum Paging.

Verwenden Sie alternativ einen cursorbasierten Ansatz, indem Sie auf die zuletzt gesehene ID der vorherigen Seite filtern:

SELECT name, accountid
FROM account
WHERE accountid > '00000000-0000-0000-0000-000000000000'
ORDER BY accountid

Aggregieren von Daten

Verwenden Sie mit GROUP BY Aggregatfunktionen, um Daten zusammenzufassen. Die unterstützten Aggregatfunktionen sind COUNT, , SUM, AVGund MINMAX.

Im folgenden Beispiel werden Kontakte nach ihrem übergeordneten Konto gruppiert und gerechnet:

SELECT a.name, COUNT(*) AS contact_count
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid
GROUP BY a.name
ORDER BY a.name

Verwenden mehrerer Aggregatfunktionen in einer einzelnen Abfrage:

SELECT COUNT(*) AS total_accounts,
       SUM(revenue) AS total_revenue,
       AVG(revenue) AS avg_revenue,
       MIN(revenue) AS min_revenue,
       MAX(revenue) AS max_revenue
FROM account

Aggregatabfragedatensatzbeschränkungen

Abfragen, doe aggregierte Werte zurückgeben, sind auf 50.000 Datensätze beschränkt. Diese Beschränkung hilft dabei, die Systemleistung und Zuverlässigkeit zu erhalten. Wenn die Filterkriterien in Ihrer Abfrage mehr als 50.000 Datensätze enthalten, wird die folgende Fehlermeldung angezeigt:

Zahl: -2147164125
Code: 8004E023
Meldung: AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Client-Fehlermeldung: Die maximale Datensatzgrenze wird überschritten. Reduzieren Sie die Anzahl der Datensätze.

Um diesen Fehler zu vermeiden, fügen Sie entsprechende Filter zu Ihrer Abfrage hinzu, um sicherzustellen, dass nicht mehr als 50.000 Datensätzen ausgewertet werden. Führen Sie Ihre Abfrage dann mehrmals aus und kombinieren Sie die Ergebnisse. Geeignete Filter hängen von der Art Ihrer Daten ab, es kann sich jedoch auch um einen Datumsbereich oder eine Teilmenge von Werten in einer Auswahlspalte handeln.

Zeilenanzahl

Wird COUNT(*) verwendet, um die Anzahl der Zeilen zu zählen, die Ihrer Abfrage entsprechen:

SELECT COUNT(*) AS account_count
FROM account

Kombinieren Sie COUNT mit einer WHERE-Klausel, um Zeilen zu zählen, die eine Bedingung erfüllen:

SELECT COUNT(*) AS active_contacts
FROM contact
WHERE statecode = 0

Verwenden Sie COUNT mit GROUP BY, um Zeilen pro Gruppe zu zählen:

SELECT a.name, COUNT(*) AS contact_count
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid
GROUP BY a.name

Optimieren der Leistung

Befolgen Sie diese Richtlinien, um effiziente SQL-Abfragen für Dataverse zu schreiben.

Vermeiden von Abfrage-Antimustern

Hinweise zu allgemeinen Dingen, die beim Erstellen von Dataverse-Abfragen zu vermeiden sind, finden Sie unter Abfrageantimuster.

Wählen Sie nur die benötigten Spalten aus.

Wenn Sie weniger Spalten auswählen, wird die übertragene Datenmenge reduziert. Vermeiden Sie das Anfordern von Spalten, die Sie nicht verwenden:

-- Avoid selecting all columns
-- SELECT * FROM account (not supported)

-- Select only needed columns
SELECT name, telephone1
FROM account

Nach indizierten Spalten filtern

Das Filtern nach Primärschlüsseln und anderen indizierten Spalten ist schneller als das Filtern nach nicht indizierten Feldern.

SELECT name, telephone1
FROM account
WHERE accountid = '00000000-0000-0000-0000-000000000000'

JOIN-Tiefe begrenzen

Sie können Joins über mehrere Tabellen verwenden, aber jede zusätzliche Verknüpfung erhöht die Abfragekosten. Beschränken Sie Verknüpfungen auf das, was Sie für Ihre Abfrage benötigen.