主页 / dba / 问题 / 2569
Accepted
kacalapy
Asked: 2011-05-07 10:27:58 +0800 CST

如何有效地查询和聚合规范化的 SQL 数据库？

  • 772

关于在单个存储过程中返回多个聚合的最有效方法？

我有一个电子邮件类型的应用程序，想为用户选择所有消息（收件箱）。问题是我将电子邮件的标题部分规范化到数据库中，以便平面数据进入消息表，而从、到、CC、BCC 存储到另一个表。

选择消息的最佳方法是什么（完整 - 意味着对完整消息进行非规范化），以便每条记录包含所有消息相关字段，包括所有消息表字段以及收件人表中与消息相关的任何相关记录，根据 PK/ FK 关系。

我非常重视的一件事是 SQL 解决方案的效率，因为这将是多次执行的代码，并且可能是整个数据库中运行次数最多的 sql

对于上下文，这里是我的数据库模式的视图。

数据库架构

sql-server-2005 query

2 个回答

  1. Best Answer

    我就是这样做的。我经常使用 Coalesce 将行放入分隔字段中，并且它始终执行和扩展良好（只要您意识到子查询总是会导致一些性能损失）。

    如果您不喜欢将其作为存储过程运行，您也可以轻松地将其重写为表值函数。

    我想另一种方法是 CTE，但我对这种从头开始打字的方法并不熟悉。

    CREATE PROCEDURE GetMessageById
    @pMessageID int
AS
BEGIN

SET NOCOUNT ON;

Declare @pTo varchar(max)
Declare @pCC varchar(max)
Declare @pBC varchar(max)

SELECT @pTo = COALESCE(@pTo + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 1 /** or whatever the id of TO field is */

SELECT @pCC = COALESCE(@pCC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 2 /** or whatever the id of CC field is */

SELECT @pBC = COALESCE(@pBC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 3 /** or whatever the id of BCC field is */

SELECT Message.*, @pTo as [ToField], @pCC as [CCField], @pBC as [BCCField], (SELECT TOP 1 [EmailAddress] FROM MessageRecipient Where RecipientTypeID = 0 /**<sender id>*/ AND MessageID = @pmessageID) AS [FromField] FROM Message Where Message.ID = @pMessageID

END
GO

    您可能会问自己，以这种方式使用 Coalesce 是如何工作的（当我第一次看到它使用时，我就是这样做的）。基本上，它创建一个递归查询，依次返回集合中的每个后续非空值，直到返回集合结束。从另一端出来，您会得到一个以逗号分隔的所有结果列表作为单个字符串。

    • 6

  2. 我将创建一个名为viewInbox的视图，该视图由所有一对一关系表设计。这将是我的主要查询视图。我将使用此视图 (viewInbox) 来显示所有收件箱项目的列表。

    当用户深入了解消息时，我会带回所有信息，包括来自多个收件人、抄送和密件抄送的一对多关系。

    • 5

相关问题