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