visual-studio-2008 - 不支持 DataSet TableAdapter 抛出 OVER SQL 构造或语句

Question

我有一个用 this sample help 编写的 t-sql 查询.

SELECT  t.gName AS 'Product'
, isnull(SUM(CASE WHEN t.Col = 1 THEN t.Quantity END),0) AS '180ml'
, isnull(SUM(CASE WHEN t.Col = 2 THEN t.Quantity END),0) AS '375ml'
, isnull(SUM(CASE WHEN t.Col = 3 THEN t.Quantity END),0) AS '500ml'
, isnull(SUM(CASE WHEN t.Col = 4 THEN t.Quantity END),0) AS '1000ml'
, isnull(SUM(CASE WHEN t.Col = 5 THEN t.Quantity END),0) AS '2000ml'
FROM (
SELECT p.pName
     , p.pCode
     , p.pGroup
     , p.pSize, i.gName, i.gCode
     , sl.Quantity, sl.BillDate
     , DENSE_RANK() OVER(PARTITION BY p.pGroup ORDER BY p.pSize) AS Col 
    FROM 
        ItemGroup AS i INNER JOIN 
                          Products AS p ON i.gCode = p.pGroup INNER JOIN 
                          SalesLog AS sl ON p.pGroup = sl.pGroup
       AND p.pCode = sl.ProductCode   
       ) AS t where t.BillDate=@BillDate and t.pGroup!=15 and t.pGroup!=16
     GROUP BY t.gName
    order by t.gName

它在 Management Studio 查询编辑器中工作，但在 DataSet 中不工作，抛出错误，如 The OVER SQL construct or statement not supported。

这是报告 (.rdlc) 所需的查询。请帮忙

谢谢

Answer 1

我会将此 SQL 放入存储过程或 View 中。然后您可以从那里填充您的数据集。