查询所有项目...最好的方法? [英] querying all items... the best method?

问题描述

我在查询或更好地对指定表中的所有项目进行编号时遇到问题.
这是我的表(第三种普通形式): ，名称
SenderTable:MailTableId，AddressTableId
收件人表:MailTableId，AddressTableId，状态

现在，我想查询一个邮件，以计算所有邮件的发件人和收件人.

我的查询:

从MailTable中选择m.Id，m.Created，m.Subject，sender.Name，receiver.Name
作为m
在s.MailTableId = m.Id上将SenderTable加入s
在sender.Id = s.AddressTableId上作为发件人加入AddressTable.
在s.MailTableId = m.Id
在r.AddressTableId上作为收件人的收件人加入AddressTable. >结果:
1 | 2008-01-01 |测试[e-mail] | [e-mail]
1 | 2008-01-01 |测试[e-mail] | [e-mail]
2 | 2008-01-02 |测试[e-mail] | [e-mail]
等.

我的目标:
1 | 2008-01-01 |测试[e-mail] |收件人[e-mail]，收件人[e-mail]
2 | 2008-01-02 |测试[e-mail] | [e-mail]

在不使用耗时的查询的情况下如何实现呢?
坏方法是查找所有收件人的每封邮件，但这不是最好的方法！
我应该比较所有记录并提取所需的信息吗?

请帮助我.谢谢！

I have problems on quering or better numerating all items from a specified table.
Here are my tables (Third normal form):

MailTable: Id, Created, Subject
AddressTable: Id, Name
SenderTable: MailTableId, AddressTableId
RecipientTable: MailTableId, AddressTableId, State

Now i want to make a query to numerate alle senders and recipients for one mail.

My query:

select m.Id, m.Created, m.Subject, sender.Name, recipient.Name
from MailTable as m
join SenderTable as s on s.MailTableId = m.Id
join AddressTable as sender on sender.Id = s.AddressTableId
join RecipientTable as r on s.MailTableId = m.Id
join AddressTable as recipient on recipient.Id = r.AddressTableId

The result:
1 | 2008-01-01 | Test | [e-mail] | [e-mail]
1 | 2008-01-01 | Test | [e-mail] | [e-mail]
2 | 2008-01-02 | Test | [e-mail] | [e-mail]
etc.

My target:
1 | 2008-01-01 | Test | [e-mail] | [e-mail],[e-mail]
2 | 2008-01-02 | Test | [e-mail] | [e-mail]

How do I achive this, whithout using time-consuming queries?
The bad way is to lookup for every mail all recipients, but this is not the best way!
Should I compare all records and extract my desired information?

Please Help me. Thanks!

于2008年7月22日星期二5:09修改

modified on Tuesday, July 22, 2008 5:09 AM

推荐答案

(使用SQL2005，对吗?:))

尝试一下:

( You are using SQL2005, right? :) )

Try this:

<br />;WITH RecipientsByMail (MailId, RecipientList) AS (<br />    select mx.Id AS MailId , <br />        (   select AddressTable.Name+'',''<br />            from MailTable<br />            join RecipientTable on RecipientTable.MailTableId = MailTable.Id<br />            join AddressTable   on AddressTable.Id = RecipientTable.AddressTableId<br />            WHERE MailTable.Id = mx.Id<br />            FOR XML PATH('''')<br />        ) AS RecipientList <br />    from MailTable as mx<br />)<br />select m.Id, m.Created, m.Subject, sender.Name, r.RecipientList<br />from MailTable as m<br />join SenderTable as s on s.MailTableId = m.Id<br />join AddressTable as sender on sender.Id = s.AddressTableId<br />join RecipientsByMail as r on r.MailId = m.Id ;

这篇关于查询所有项目...最好的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！