For example, we want to summarize someone's consumption project, corresponding to the following table: Theme and ThemeDetailTheme record: themeid (int) themename (varchar [10]) 1 Tasting 2 Travel 3 Route 4 Other THEMEDETAIL records: Detailid (int) themeid (int) Price (Money) 1 12.5 2 1 5 3 1 6 4 2 11 5 2 17 6 3 8 where the MemeID in Theme is a couple of relationships, for the THEMEDETAIL table The understanding is as follows: "Eating" cost is 12.5 5 6 = 23.5 yuan, "business trip" cost is 11 17 = 28 yuan, "ride" cost is 8 = 8 yuan, "other" cost does not exist, 0 Processing, the corresponding SQL statement can be represented in this way: select top 100 percent dbo.Theme.TheMename, ISNULL (SUM (DBO.THEMEDETAIL.PRICE), 0) As TotalPriceFrom dbo.theme Inner join dbo.themedetail on dbo.Theme.ThemeId = dbo.themedetail.themeidgroup by dbo.theme.themename, dbo The results of the DBO.THEMEIDORDER BY dbo.Theme.Themeid: Themename Totalprice Egg 23.5 Travel 28 Bottom 8 For consumption records If this is not displayed, use the inline method to meet the requirements, but We now need every item in Theme, including "other" items, so we should use another method to achieve, this is the left outlink method, the corresponding SQL statement can be represented: SELECT TOP 100 PERCENT dbo.Theme.ThemeName, ISNULL (SUM (dbo.ThemeDetail.Price), 0) AS TotalPriceFROM dbo.Theme LEFT OUTER JOIN dbo.ThemeDetail ON dbo.Theme.ThemeID = dbo.ThemeDetail.ThemeIDGROUP BY dbo.Theme.ThemeName DBO.THEME.THEMEIDORDER BY dbo.theme.Themeid execution results are as follows:
