SOQL機能 | 例 |
|---|---|
IDをアカウントから選択する | SELECT Id from Account |
IDと名前をアカウントから選択する | SELECT Id, Name from Account |
完全修飾名 | SELECT Account.Id, Account.Name from Account |
フィルタ | SELECT Account.Name ,Account.NumberOfEmployees, Account.SLAExpirationDate__c ,Account.utf_filter__c, Account.Datetimefld__c_FROM Account Where ((utf_filter__c LIKE '%UTFDSS%')) |
複雑なフィルタ | SELECT Account.Name, Account.NumberOfEmployees, Account.SLAExpirationDate__c, Account.utf_filter__c, Account.Datetimefld__cFROM Account Where (((utf_filter__c LIKE '%UTFDSS%') AND Name IS NOT Null) OR AnnualRevenue > 567) |
GROUP BY | SELECT LeadSource FROM Lead GROUP BY LeadSource |
集計関数とGROUP BY | SELECT Name, Max(CreatedDate) FROM Account GROUP BY NameLIMIT 5 SELECTCampaignId,AVG(Amount)FROMOpportunityGROUP BY CampaignId SELECT COUNT(Id) FROM Account WHERE Name LIKE 'a%' |
HAVING | SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource HAVING COUNT(Name) > 100 |
IN | SELECT Name FROM Account WHERE BillingState IN ('California', 'New York') |
NOT IN | SELECT Name FROM Account WHERE BillingState NOT IN ('California', 'NewYork') |
ORDER BY | SELECT Name FROM Account ORDER BY Name DESC NULLS LAST SELECT Id, CaseNumber,Account.Id,Account.NameFROMCaseORDER BY Account.Name |
OFFSET | SELECT Name FROM Merchandise__c WHERE Price__c > 5.0 ORDER BY Name LIMIT 100 OFFSET 10 |
ROLLUP | SELECT LeadSource, COUNT(Name) cnt FROMLeadGROUP BY ROLLUP(LeadSource) |
TYPEOF | SELECT TYPEOF What WHEN Account THEN Phone ELSE Name END FROM EventWHERECreatedById IN (SELECTCreatedByIdFROMCase) |
エイリアス | SELECT Name n, MAX(Amount) max FROM Opportunity GROUP BY Name |
日付関数 | SELECT CALENDAR_YEAR(CreatedDate), SUM(Amount) FROM Opportunity GROUP BY CALENDAR_YEAR(CreatedDate) |
日付リテラル | SELECT Id FROM Account WHERE CreatedDate = YESTERDAY SELECT Id, CaseNumber,Account.Id,Account.NameFROMCaseORDER BY Account.Name |
マルチ通貨 | SELECT Id, convertCurrency(AnnualRevenue) FROM Account SELECT Id, NameFROMOpportunityWHEREAmount > USD5000 |
演算子 | SELECT Id FROMAccountWHERECreatedDate > 2005-10-08T01:02:03Z |
準結合 | SELECT Id, NameFROMAccountWHERE Id IN (SELECTAccountIdFROM OpportunityWHEREStageName = 'ClosedLost') |
準結合の参照フィールド | SELECT Id FROM Task WHERE WhoId IN (SELECT Id FROM Contact WHERE MailingCity = 'Twin Falls') |
準結合のリレーションクエリ | SELECT Id, (SELECT Id fromOpportunityLineItems)FROM Opportunity WHERE Id IN (SELECT OpportunityId FROM OpportunityLineItem WHERE totalPrice > 10000) |
親-子リレーションクエリ | SELECT Name, (SELECT LastName FROM Contacts) FROM Account SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts) FROM Account SELECT Name, (SELECT Name FROM Line_Items__r) FROM Merchandise__c WHERE Name LIKE 'Acme%' SELECT Id, Name,(SELECT Id, NameFROMAlldatatypeDetail__r) FROM AllDatatypes__c |
子-親リレーションクエリ | SELECT Id, FirstName__c,Mother_of_Child__r.FirstName__cFROM Daughter__c WHERE Mother_of_Child__r.LastName__c LIKE 'C%' Select Id, IsDeleted, Name, LastViewedDate, LastReferencedDate, AllDatatypes_md__c, AllDatatypes_md__r.Name from AlldatatypeDetail__c |
集計関数を含むリレーションクエリ | SELECT Name, (SELECT CreatedBy.Name FROM Notes) FROM Account SELECT Amount, Id, Name, (SELECT Quantity, ListPrice, PricebookEntry.UnitPrice, PricebookEntry.Name FROM OpportunityLineItems) FROM Opportunity |