承接MOSS各种工作流开发 联系人:王先生.电话:18618405729QQ:252385878 QQ群:41696750 MSN:wanghao-3@hotmail.com

导航

统计

公告

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 对应的需要显示的字段。。。

 

 

稍等继续......

 

 

 

 


 

posted on 2011-03-21 21:58 A A 阅读(...) 评论(...) 编辑 收藏