The important thing in life is to have a great aim , and the determination

常用链接

统计

IT技术链接

保险相关

友情链接

基金知识

生活相关

最新评论

在SQL Server 中合理的使用 LEFT OUTER JOIN 进行开发

比如我们想对某人的消费项目进行汇总,对应以下两个表:Theme 与 ThemeDetail

Theme 的记录为:
ThemeID(int)    ThemeName(varchar[10])
        1                        就餐
        2                        出差
        3                        乘车
        4                        其它

ThemeDetail 的记录为:
DetailID(int)    ThemeID(int)    Price(money)
       1                    1                 12.5
       2                    1                    5
       3                    1                    6
       4                    2                   11
       5                    2                   17
       6                    3                    8

其中 Theme 中的 ThemeID 与 ThemeDetail 中的 ThemeID 是一对多的关系,对 ThemeDetail 表的理解如下:“就餐”费用为 12.5 + 5 + 6 = 23.5 元,“出差”费用为 11 + 17 = 28 元,“乘车”费用为 8 = 8 元,“其它”费用不存在,视为 0 处理,对应的 SQL 语句可以这样表示:

SELECT TOP 100 PERCENT dbo.Theme.ThemeName, ISNULL(SUM(dbo.ThemeDetail.Price), 0)
      AS TotalPrice
FROM dbo.Theme INNERJOIN
      dbo.ThemeDetail ON dbo.Theme.ThemeID = dbo.ThemeDetail.ThemeID
GROUP BY dbo.Theme.ThemeName, dbo.Theme.ThemeID
ORDER BY dbo.Theme.ThemeID

执行结果如下:
ThemeName    TotalPrice
    就餐              23.5
    出差               28
    乘车                8

对于消费记录不存的记录如果就这样不显示它的话,使用内联的方法就可以满足要求了,但是我们现在需要对 Theme 中的每一项均做统计,也包括“其它”项,于是我们应该采用另一种方法来实现,这就是左外联的方法,相应的 SQL 语句可以这样表示:

SELECT TOP 100 PERCENT dbo.Theme.ThemeName, ISNULL(SUM(dbo.ThemeDetail.Price), 0)
      AS TotalPrice
FROM dbo.Theme LEFTOUTER JOIN
      dbo.ThemeDetail ON dbo.Theme.ThemeID = dbo.ThemeDetail.ThemeID
GROUP BY dbo.Theme.ThemeName, dbo.Theme.ThemeID
ORDER BY dbo.Theme.ThemeID

执行结果如下:
ThemeName    TotalPrice
    就餐              23.5
    出差               28
    乘车                8
    其它                0

这样是不是就满足了我们的要求呢!



/*
我测试了 10 万条记录后发现速度并非兄弟所说的那样,测试代码如下
*/
declare @StartTime datetime
set @StartTime = getdate()

SELECT TOP 100 PERCENT ThemeName, ISNULL(SUM(Price), 0) AS TotalPrice
FROM Theme LEFT OUTER JOIN ThemeDetail ON Theme.ThemeID = ThemeDetail.ThemeID
GROUP BY ThemeName, Theme.ThemeID
ORDER BY Theme.ThemeID

select datediff(millisecond,@StartTime,getdate())
set @StartTime = getdate()

SELECT TOP 100 PERCENT ThemeName, ISNULL((SELECT SUM(ThemeDetail.Price)
FROM ThemeDetail
WHERE ThemeDetail.ThemeID = Theme.ThemeID), 0) AS TotalPrice
FROM Theme
GROUP BY ThemeName, ThemeID
ORDER BY ThemeID

select datediff(millisecond,@StartTime,getdate())


/* 测试结果如下,精确到毫秒,最后是平均值 */
次数 时间 时间
----------------------
1 93 110
2 93 93
3 93 110
4 93 93
5 93 110
6 93 110
7 93 93
8 93 93
9 93 93
10 93 126
11 110 110
12 93 126
13 96 123
14 93 126
15 93 110
16 93 123
17 106 96
18 93 93
19 106 96
20 93 93
----------------------
95.3 106.35

posted on 2007-02-05 18:25 鸿雁 阅读(271) 评论(0)  编辑  收藏