Question

我有一个问题, 我需要用 MS SQL 服务器运行一些数据。 2008 。基本上, 我有 3 个独特的字段, 还有 3 个字段在时间范围内需要重复。这样我就可以在主选语句中有三个子查询。我仅仅在尝试一个子查询, 然后复制 n 粘贴另外两个字段, 并更改日期范围。

这是我想到的 SQL, 但它给了我一个错误。我试图使用子查询, 因为我没有固定程序来创建程序。

SELECT
mon.description as ScriptName,
up0.pageseqnum as PageSeq,
usd.DisplayName as PageName,

--This subquery is the one week old data for the Response Time, Page Weight, and Number of Objects.
(SELECT
ROUND(AVG(CAST(up1.ResponseTime AS FLOAT)* 0.001),3) AS ResponseTime,
ROUND(AVG(CAST(up1.numbytes AS FLOAT)* 0.001), 3) AS NumberofKilobytes,
CONVERT(INT,AVG(up1.numobj),0) AS NumberOfObjects
FROM
table0 AS up1
WHERE
up1.Monitor_Id = up0.Monitor_id
AND up1.TestTime BETWEEN DATEADD(HOUR,4, 2012-05-14 00:00:00 ) AND DATEADD(HOUR,4, 2012-05-21 00:00:00 )
AND ISNULL(up1.ContentMatchStatus, 0 )= 0
AND up1.UserScriptStatus = 0
AND up1.TimeoutStatus = 0
AND up1.ResponseTime > 0
) AS CurrentWeek

FROM
table0 (nolock) AS up0
JOIN table1 usd ON up0.Monitor_Id=usd.monitor_id
and up0.PageSeqNum=usd.PageSeqNum
JOIN table3 mon on up0.Monitor_Id = mon.Monitor_id

WHERE
up0.Monitor_Id in (1, 2, 3, 4, 5, 6, 7, 8 ,9 ,10)

GROUP BY up0.Monitor_Id, mon.Description, up0.PageSeqNum, usd.DisplayName
ORDER BY mon.Description ASC;

我希望结果集应该有这些栏目, 并且我可以在本周之后再增加前两周的数据! 请 SCROLLL 查看我所说的是什么结果。

                                |           CurrentWeek (Sub Query)                  |           OneWeekOld (Sub Query)                   |
ScriptName | PageSeq | PageName | ResponseTime | NumberofKilobytes | NumberOfObjects | ResponseTime | NumberofKilobytes | NumberOfObjects |
Test1      |   0     |   Home   |      1.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test1      |   1     |   Sale   |      2.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test1      |   2     |   Bake   |      3.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test1      |   3     |   Cake   |      4.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test2      |   0     |   Home   |      1.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test2      |   1     |   Sale   |      2.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test2      |   2     |   Bake   |      3.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |

任何帮助都将不胜感激。

Answer 1

尝试一下这样的东西:

SELECT  mon.description as ScriptName,
        up0.pageseqnum as PageSeq,
        usd.DisplayName as PageName,
        wk.ResponseTime,
        wk.NumberofKilobytes,
        wk.NumberOfObjects

FROM
table0 (nolock) AS up0
JOIN table1 usd ON up0.Monitor_Id=usd.monitor_id
and up0.PageSeqNum=usd.PageSeqNum
JOIN table3 mon on up0.Monitor_Id = mon.Monitor_id
CROSS APPLY (   SELECT  up1.Monitor_Id,
                        ROUND(AVG(CAST(up1.ResponseTime AS FLOAT)* 0.001),3) AS ResponseTime,
                        ROUND(AVG(CAST(up1.numbytes AS FLOAT)* 0.001), 3) AS NumberofKilobytes,
                        CONVERT(INT,AVG(up1.numobj),0) AS NumberOfObjects
                FROM table0 AS up1
                WHERE up1.TestTime BETWEEN DATEADD(HOUR,4, 2012-05-14 00:00:00 ) AND DATEADD(HOUR,4, 2012-05-21 00:00:00 )
                AND ISNULL(up1.ContentMatchStatus, 0 )= 0
                AND up1.UserScriptStatus = 0
                AND up1.TimeoutStatus = 0
                AND up1.ResponseTime > 0 
                GROUP BY up1.Monitor_Id) wk

WHERE up0.Monitor_Id in (1, 2, 3, 4, 5, 6, 7, 8 ,9 ,10)
AND up0.Monitor_Id = up1.Monitor_Id
ORDER BY mon.Description ASC;

如果你在sqilfiddle.com上公布你的计划,那会更容易些。这样我就可以写一个对它的质询来回报你需要的东西。

另外,如果您使用 SQL 服务器, 请使用 APPLY 。请说明您正在和哪个数据库系统合作。

友情链接