Question

我有一个类似于以下例子的表格,这是从使用ODBC与IIS进行内容管理系统的ODBC记录中提取的。

logtime username operation target parameters

2012-05-24 18:13:23.000           -                          GET           /beta.pptx     title=home
2012-05-24 18:13:14.000           -                          GET            /index.php     -
2012-05-24 18:13:09.000     domainjoeh              GET           /css.php          -

我试图找出的是谁正在下载哪些文件(如 PPTX 和 DOCX 文件 ) 。由于目标所在的行中包含 PPTX 或 DOCX 文件名称的行中没有相应的用户名, 因此我可以从日志时间向后追溯到表格中找到下一个行, 该行除了“ - ” 以外还有用户名条目可以加入 PPTX 或 DOCX 文件列表的行。在我测试中, 这似乎是准确的。因此, 我如何创建可以让我完成此目的的选定语句?

显示日期印章和每个用户每天仅一例确切文件名:

SELECT DISTINCT
v.username
, v.logdate 
, SUBSTRING(v.target, CASE WHEN CHARINDEX( = , v.target) > 0 THEN CHARINDEX( = , v.target)+1 ELSE LEN(v.target) END, LEN(v.target)) as  fileName

FROM ( select (select Top 1 temp2.username from InternetLog as temp2 where temp2.logtime <= temp.logtime and temp2.username != - order by temp2.logtime desc) as username, LEFT(CONVERT(DATETIME, temp.logtime, 101), 11) AS logdate, temp.target from InternetLog as temp where (RIGHT(RTRIM(temp.target),4) = docx or RIGHT(RTRIM(temp.target),4) = pptx ) ) AS v WHERE v.username LIKE %johnd% order by logdate desc

Answer 1

DECLARE @documents TABLE (DocumentName varchar(50))
DECLARE @downloads TABLE (target varchar(50))

INSERT @documents SELECT  test.pptx 
INSERT @documents SELECT  test2.pptx 
INSERT @downloads SELECT  /test.pptx 


SELECT *
FROM @documents doc
  INNER JOIN @downloads dwn ON dwn.target LIKE  %  + doc.DocumentName +  %

Answer 2

declare @temp table (logtime smalldatetime, username varchar(10), target varchar(10))
insert into @temp(logtime, username, target)
 values( 2012-05-24 14:13:23.000 ,  name ,  df ),
        ( 2012-05-24 16:13:23.000 ,  - ,  sdf ),
        ( 2012-05-24 18:13:23.000 ,  - ,  DOCX ),
        ( 2012-05-24 19:13:23.000 ,  sdfsdf ,  sdf ),
        ( 2012-05-24 19:15:23.000 ,  - ,  PPTX )

select 
    (select Top 1 temp2.username
        from @temp as temp2 
        where temp2.logtime<temp.logtime 
              and temp2.username !=  -  
        order by temp2.logtime desc) as username, 
    temp.logtime, 
    temp.target 
from @temp as temp 
where temp.target like  %DOCX%  or temp.target like  %PPTX%  
order by temp.logtime

给出结果 :

(5 row(s) affected)
username   logtime                 target
---------- ----------------------- ----------
name       2012-05-24 18:13:00     DOCX
sdfsdf     2012-05-24 19:15:00     PPTX

(2 row(s) affected)

EDIT 如果您想要通过外部查询来过滤日期范围( 如果您获得与您的时间和初始目标/时间匹配不匹配的 id 的用户名并不重要), 则添加到外部位置。对于用户名来说, 您必须更改它周围的更多内容。我还没有测试它, 但也许有类似的东西 :

select 
        (select Top 1 temp2.username
    from @temp as temp2 
    where temp2.logtime<temp.logtime 
          and temp2.username !=  -  
    order by temp2.logtime desc) as username, 
        temp.logtime, 
        temp.target 
    from @temp as temp 
    join  (select Top 1 temp2.username
            from @temp as temp2 
            where temp2.logtime<temp.logtime 
                  and temp2.username !=  -  
            order by temp2.logtime desc) as users
         on 1=1
    where (temp.target like  %DOCX%  or temp.target like  %PPTX% ) 
          and users.username like  %domainuser% 
          and temp.logtime < @maxtime 
          and temp.logtime > @minTime
    order by temp.logtime

您想要过滤的约会时间最大和分钟时间。 NAME OF TRANSLATORS NAME OF TRANSLATORS

友情链接