SQL查询以为MOSS 2007中的共享点列表生成报告 [英] SQL Query to generate reports for Sharepoint Lists in MOSS 2007

问题描述

我有一个称为候选者的共享点列表.

每个候选人都有一个状态，该状态在9个不同的值之间变化.

对于每个候选人，可能会有多个状态"字段.

例如

Hi,

I have a Sharepoint List called Candidates.

Each Candidate has a Status which varies between 9 different values.

For each Candidate there may be multiple Status fields.

For Example

Candidate Name      Status         Date when Status was created/modified    ID
John Doe            New                   20/07/2011                         1
John Doe            Shortlisted           21/07/2011                         1
John Doe            Shortlisted           22/07/2011                         1
John Doe            Reject                23/07/2011                         1 
Jane Doe            New                   20/07/2011                         2
Jane Doe            Shortlisted           22/07/2011                         2
Jane Doe            Shortlisted           23/07/2011                         2
Jane Doe            Shortlisted           23/07/2011                         2
Jane Doe            Joined                23/07/2011                         2 

因此，现在我想要一个查询(生成报告)，该查询获取候选名称以及每个状态值的最新状态...我的意思是该查询应生成下表.

So now I want a query(to generate a report) which gets the Candidate Name, and his latest Status for each status value... I mean that the query should generate the table below.

Candidate Name      Status         Date when Status was created/modified
John Doe            New                   20/07/2011
John Doe            Shortlisted           22/07/2011
John Doe            Reject                23/07/2011
Jane Doe            New                   20/07/2011
Jane Doe            Shortlisted           23/07/2011 
Jane Doe            Joined                23/07/2011

如您所见，我只需要获取最新的入围值或最新的加入值(如果存在状态为已加入的多个值)

到目前为止，我所能获得的是每个名字我都能获得最新状态的信息

即

So as you can see I need to get only the latest Shortlisted value or the latest Joined value (if there are any multiple values with status Joined)

What I am able to get so far is for each name I am able to get the latest Status

ie

Candidate Name      Status         Date when Status was created/modified
John Doe            Reject                23/07/2011
Jane Doe            Joined                23/07/2011

通过以下查询:

thru the following query:

SELECT  [tp_ID],
        [nvarchar14],
        [tp_Modified]
FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY [tp_ID] ORDER BY [tp_Modified] DESC) AS rn,
                    [tp_ID],
                    [nvarchar14],
                    [tp_Modified]
          FROM      [WSS_Intvw666Mgmt].[dbo].[AllUserData]
          WHERE     [tp_ListId] = 'b126efb6-1239-4b8a-978b-897960078552'
        ) a
WHERE   rn = 1

在此查询中
tp_ID是候选人ID ...这是唯一的
nvarchar14是状态
而tp_Modified是创建/修改状态的日期

有人可以帮我解决这个问题吗?

In this query
tp_ID is the Candidate ID... which is unique
nvarchar14 is the Status
and tp_Modified is the date when the Status was created/modified

Can anyone please help me out with this?

推荐答案

您正尝试直接使用SQL查询SharePoint数据库吗?不支持此操作，不应尝试这样做.将CAML查询与SPQuery或SPSiteDataQuery对象一起使用

You are trying to query the SharePoint database directly with SQL? This is not supported and should not be attempted. Use a CAML query with the SPQuery or SPSiteDataQuery object

这篇关于SQL查询以为MOSS 2007中的共享点列表生成报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！