SQL OVER 子句替换或 Visual Studio 设置更改

Question

我有一个使用 OVER(PARTITION BY ParamName) 的 SQL 查询条款。

这是整个查询:

SELECT 
   Products.SerialNumber, Products.StationID, 
   COUNT(Tests.ParamName) OVER (PARTITION BY Tests.ParamName) AS ParamNameCount, 
   Tests.ParamName, Tests.ParamValue, Tests.LSL, Tests.USL, Tests.ParamUnits
FROM          
   Tests 
INNER JOIN 
   Products ON Tests.P_Idx = Products.P_idx
WHERE      
   (Products.ProductID = @ProductID) AND (Products.TestID = 1) 
   AND (Tests.ParamState = 0) AND (Tests.ParamName <> 'UUT Test State')
   AND (Tests.ParamName <> 'Total Test Time')
   AND (Products.TestDate BETWEEN @StartDate AND DATEADD(second, -1, DATEADD(day, 1, @EndDate)))
GROUP BY 
   Products.StationID, Products.SerialNumber, Tests.ParamName, Tests.ParamValue, 
   Tests.LSL, Tests.USL, Tests.ParamUnits
ORDER BY 
   ParamNameCount DESC, Products.StationID, Products.SerialNumber

如果我在 Microsoft SQL Server Management Studio 上使用此查询，它可以正常工作，但 Visual Studio 2010 上的查询设计器 GUI 似乎不支持 OVER条款，因为我收到一个错误说

The OVER construct or statement is not supported

我确实在网上找到了一个创建存储过程的解决方案，但该解决方案对我不起作用，因为我使用的是 Crystal Reports，它使用由 Visual Studio 2010 的查询设计器 GUI 生成的 XSD 文件。

所以我想知道是否可以更改我的查询以不使用 OVER子句或者是否有设置或我可以在 Visual Studio 2010 上更改的内容，因此查询设计器 GUI 允许我使用 OVER条款。

谢谢你。

Answer 1

这可能对某人有所帮助......所以我找到了一种方法来处理这个很长的查询:

SELECT     tmp5.SerialNumber, tmp5.StationID, tmp4.ParamCount, tmp4.ParamName, tmp5.ParamValue, tmp5.LSL, tmp5.USL, tmp5.ParamUnits, 
                  tmp5.TestDate
FROM         (SELECT     COUNT(ParamName) AS ParamCount, ParamName
                   FROM          (SELECT     Tests.T_Idx, Products.SerialNumber, Products.StationID, Tests.ParamName, Tests.ParamValue, Tests.LSL, Tests.USL, 
                                                                  Tests.ParamUnits, Products.TestDate
                                           FROM          Tests INNER JOIN
                                                                  Products ON Tests.P_Idx = Products.P_idx
                                           WHERE      (Products.ProductID = @ProductID) AND (Products.TestID = 1) AND (Tests.ParamState = 0) AND 
                                                                  (Tests.ParamName <> 'UUT Test State') AND (Tests.ParamName <> 'Total Test Time') AND (Products.TestDate BETWEEN 
                                                                  @StartDate AND DATEADD(second, - 1, DATEADD(day, 1, @EndDate))) AND (Tests.T_Idx IN
                                                                      (SELECT     MIN(Tests_3.T_Idx) AS TestIdx
                                                                        FROM          Tests AS Tests_3 INNER JOIN
                                                                                               Products AS Products_3 ON Tests_3.P_Idx = Products_3.P_idx
                                                                        WHERE      (Products_3.ProductID = @ProductID) AND (Products_3.TestID = 1) AND (Tests_3.ParamState = 0) AND 
                                                                                               (Tests_3.ParamName <> 'UUT Test State') AND (Tests_3.ParamName <> 'Total Test Time') AND 
                                                                                               (Products_3.TestDate BETWEEN @StartDate AND DATEADD(second, - 1, DATEADD(day, 1, @EndDate)))
                                                                        GROUP BY Products_3.SerialNumber))) AS tmp3
                   GROUP BY ParamName) AS tmp4 LEFT OUTER JOIN
                      (SELECT     MIN(Tests_2.T_Idx) AS TestIdx, Products_2.SerialNumber, Products_2.StationID, Tests_2.ParamName, Tests_2.ParamValue, 
                                               Tests_2.LSL, Tests_2.USL, Tests_2.ParamUnits, Products_2.TestDate
                        FROM          Tests AS Tests_2 INNER JOIN
                                               Products AS Products_2 ON Tests_2.P_Idx = Products_2.P_idx
                        WHERE      (Products_2.ProductID = @ProductID) AND (Products_2.TestID = 1) AND (Tests_2.ParamState = 0) AND 
                                               (Tests_2.ParamName <> 'UUT Test State') AND (Tests_2.ParamName <> 'Total Test Time') AND (Products_2.TestDate BETWEEN 
                                               @StartDate AND DATEADD(second, - 1, DATEADD(day, 1, @EndDate))) AND (Tests_2.T_Idx IN
                                                   (SELECT     MIN(Tests_1.T_Idx) AS TestIdx
                                                     FROM          Tests AS Tests_1 INNER JOIN
                                                                            Products AS Products_1 ON Tests_1.P_Idx = Products_1.P_idx
                                                     WHERE      (Products_1.ProductID = @ProductID) AND (Products_1.TestID = 1) AND (Tests_1.ParamState = 0) AND 
                                                                            (Tests_1.ParamName <> 'UUT Test State') AND (Tests_1.ParamName <> 'Total Test Time') AND 
                                                                            (Products_1.TestDate BETWEEN @StartDate AND DATEADD(second, - 1, DATEADD(day, 1, @EndDate)))
                                                     GROUP BY Products_1.SerialNumber))
                        GROUP BY Products_2.SerialNumber, Products_2.StationID, Tests_2.ParamName, Tests_2.ParamValue, Tests_2.LSL, Tests_2.USL, 
                                               Tests_2.ParamUnits, Products_2.TestDate) AS tmp5 ON tmp4.ParamName = tmp5.ParamName
ORDER BY tmp4.ParamCount DESC