CAML 多表查询 SPQuery.Joins and ProjectedFields

      在实际的SharePoint项目中，业务都比较复杂，其实我们的List一直都支持多表查询的~~

 

嘿嘿！

Demo：

字典表：

CityState表：  标题(Text)   State(Text)  AreaCode(num)

Locations表：  标题(Text)   City (Text)  zipCode (num)

Customer表：  CustomerName(Text) Address(Text) City(Lookup) State(Lookup)

 

下面通过SPQuery 进行多表查询（不过Joins 和ProjectedFields 对于字段有很多限制，join的表不支持多行文本，不支持用户或用户组 ）

        using (SPSite site = new SPSite(@"http://localhost/"))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    SPList list1 = web.Lists["Customers"];
                    SPQuery query = new SPQuery();
                    query.Query = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>AA</Value></Eq></Where>";
                    query.Joins = @"<Join Type='LEFT' ListAlias='Locations'><Eq><FieldRef Name='City' RefType='ID'/><FieldRef List='Locations' Name='ID'/></Eq></Join><Join Type='LEFT' ListAlias='CityState'><Eq><FieldRef Name='State' RefType='ID'/><FieldRef List='CityState' Name='ID'/></Eq></Join>";//
                    query.ProjectedFields = @"<Field Name='zipCode1' Type='Lookup' List='Locations' ShowField='zipCode'/><Field Name='AreaCode' Type='Lookup' List='CityState' ShowField='AreaCode'/><Field Name='CreateU' Type='Lookup' List='CityState' ShowField='Created'/>";
                    query.ViewFields = @"<FieldRef Name='Title'/><FieldRef Name='City'/><FieldRef Name='zipCode1'/><FieldRef Name='State'/><FieldRef Name='AreaCode'/><FieldRef Name='CreateU'/>";//
                    SPListItemCollection items = list1.GetItems(query);
                }
            }

Joins对应的join的表，ProjectedFields 对应的Join的表的字段

ViewFields 对应的需要显示的字段。。。

 

 

稍等继续......