using (SPSite site = new SPSite(SiteUrl))
{
using (SPWeb web = site.RootWeb)
{
SPQuery query = new SPQuery();
//Joins属性，这里有INNER和LEFT两种方式连接，均可查询，而且支持多表连接；
query.Joins = "<Join Type='INNER' ListAlias='City'>" +
"<Eq>" +
"<FieldRef Name='Location' RefType='Id'/>" +
"<FieldRef List='City' Name='ID'/>" +
"</Eq>" +
"</Join>";
//设置关联的查阅项字段
query.ProjectedFields = "<Field Name='CustomCityID' Type='Lookup' " +
"List='City' ShowField='CityID'/>";
<Field Name='CustomMark' Type='Lookup' List='City' ShowField='Mark'/>";
//设置需要显示的字段
query.ViewFields = "<FieldRef Name='Title'/>" +
"<FieldRef Name='Location'/>" +
"<FieldRef Name='CustomCityID'/>";
"<FieldRef Name='CustomMark'/>";
SPList list = web.GetList(ListName);
SPListItemCollection itemcoll = list.GetItems(query);
foreach (SPListItem item in itemcoll)
{
SPFieldLookupValue Location = new SPFieldLookupValue(item["Location"].ToString());
SPFieldLookupValue CustomCityID = new SPFieldLookupValue(item["CustomCityID"].ToString());
Console.WriteLine("ID:" + item.ID.ToString() + " Title:" + item["Title"].ToString() + " Location:" + Location.LookupValue + " CustomCityID:" + CustomCityID.LookupValue);
}
}
}
9、同时为代码的ProjectedFields多添加Mark的字段属性，如下图：
query.ProjectedFields =
"<Field Name='CustomCityID' Type='Lookup' List='City' ShowField='CityID'/>
<Field Name='CustomMark' Type='Lookup' List='City' ShowField='Mark'/>";
10、同时为显示属性加入CustomMark字段，如下图：
query.ViewFields = "<FieldRef Name='Title'/>" +
"<FieldRef Name='Location'/>" +
"<FieldRef Name='CustomCityID'/>" +
"<FieldRef Name='CustomMark'/>";
输出添加CustomMark字段的值，如下：
SPFieldLookupValue CustomMark = new SPFieldLookupValue(item["CustomMark"].ToString());
Console.WriteLine("CustomMark:" + CustomMark.LookupValue)
附：列表结构
列表一：City
字段：Title（默认字段，单行文本，作为Address关联的查阅项）
CityID（数字类型，作为Address关联的查阅项）
Mark（单行文本，作为多表查询的测试字段）
列表二：Address
字段：Title（默认）
Location（查阅项，关联City列表的Title）
CustomCityID（查阅项，关联City列表的CityID）
附：参考链接
http://social.msdn.microsoft.com/Forums/sharepoint/zh-CN/866a49fd-0656-4a8a-9e11-36eeb4401005/sharepoint-2010-caml-list-joins
http://msdn.microsoft.com/zh-cn/library/microsoft.sharepoint.spquery.joins.aspx
